input data not being saved in dataset or database

[danielagaba]

Hi

I've run this code and i get no errors but my form data is not saved on the dataset or database. This is the click event code.

Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDictionary|\TMSDB.mdf;Integrated Security=True Instance=True")

Dim ds As New DataSet
conn.Open()
Dim Adp As New SqlDataAdapter("select * from Housing")
Adp.Fill(ds, "Housing")

Dim dr As DataRow = ds.Tables("Housing").NewRow()
dr("housingType") = Me.TextBox1.Text
dr("description") = Me.TextBox2.Text
dr("location") = Me.TextBox3.Text

ds.Tables("Housing").Rows.Add(dr)
Dim cmd As New SqlCommandBuilder(Adp)
Adp.Update(ds, "Housing")

MessageBox.Show("Information Saved!")

[Teme64]

Try to dump out insert statement. I also added connection object to data adapter creation

Dim conn As New SqlConnection("Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDictionary|\TMSDB.mdf;Integrated Security=True Instance=True")

Dim ds As New DataSet
conn.Open()
' AFAIK YOU NEED CONNECTION OBJECT IN HERE
Dim Adp As New SqlDataAdapter("select * from Housing", conn)
Adp.Fill(ds, "Housing")

Dim dr As DataRow = ds.Tables("Housing").NewRow()
dr("housingType") = Me.TextBox1.Text
dr("description") = Me.TextBox2.Text
dr("location") = Me.TextBox3.Text

ds.Tables("Housing").Rows.Add(dr)
Dim cmd As New SqlCommandBuilder(Adp)
' DUMP INSERT STATEMENT
MsgBox(cmd.GetInsertCommand())
' OR CREATE AN INSERT STATEMENT
'Adp.InsertCommand = New SqlCommand("INSERT INTO Housing (housingType, description, location) VALUES ('" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.TextBox3.Text & "')")
Adp.Update(ds, "Housing")

MessageBox.Show("Information Saved!")

If the insert statement doesn't seem to be ok, comment out line 19 and try with that insert statement.

HTH

[danielagaba]

hey,
thanx for the help. finally got a response though it was an error about my INSERT_IDENTITY being set to off. I changed my code to the following deal with that error but my data still is not being stored and I'm getting no errors. But the peculiar thing is i manually entered a primary key value, run the code and it didnt give any errors but no data stored. I run the code again with the same value and i got an error about duplicate values in the primary key column but i dont see any stored data for it to have seen the duplicate value

Dim comm As  New SqlCommand("SET IDENTITY_INSERT Housing ON;INSERT INTO Housing (housingID, housingType, description, location) VALUES ('', '" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.TextBox3.Text & "')", conn)

Adp.InsertCommand = comm

Adp.Update(ds, "Housing")

or could it be a bug with VS 2008?

[Teme64]

or could it be a bug with VS 2008?

I doubt it. Inserting data to a DB is an elementary thing to do and any bugs would have been fixed by now.

I run the code again with the same value and i got an error about duplicate values in the primary key column

In your code Dim comm As New SqlCommand("SET IDENTITY_INSERT Housing ON;INSERT INTO Housing (housingID, housingType, description, location) VALUES ('', '" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.TextBox3.Text & "')", conn) you have identity insert on but data for the identity field is empty. Assuming it's of type Int, value 0 is inserted. Next time ID value 0 is tried to be inserted again and you get the PK violation error. You should either remove identity insert or provide a valid and unique value for identity column (which is also PK in your case).

i dont see any stored data for it to have seen the duplicate value

In SQL Server Management Studio you don't see any rows added unless you refresh the opened table view.

Here's the code again

Dim conn As New SqlConnection(<connection string>)

Dim ds As New DataSet
conn.Open()
Dim Adp As New SqlDataAdapter("select * from Housing", conn)
Adp.Fill(ds, "Housing")

' DEBUG
Me.TextBox1.Text = "TestType"
Me.TextBox2.Text = "TestDesc"
Me.TextBox3.Text = "TestLoc"
' /DEBUG

' IF YOU HAVE IDENTITY_INSERT ON, YOU MUST PROVIDE EXPLICITLY A VALID ID VALUE
Dim comm As New SqlCommand("SET IDENTITY_INSERT Housing ON;INSERT INTO Housing (housingID, housingType, description, location) VALUES (100, '" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.TextBox3.Text & "')", conn)

' OR WITHOUT IDENTITY_INSERT
'Dim comm As New SqlCommand("INSERT INTO Housing (housingType, description, location) VALUES ('" & Me.TextBox1.Text & "', '" & Me.TextBox2.Text & "', '" & Me.TextBox3.Text & "')", conn)

Adp.InsertCommand = comm

' DEBUG: DUMP INSERT STATEMENT
MsgBox(Adp.InsertCommand().CommandText)
' /DEBUG

'Adp.Update(ds, "Housing") <- This doesn't work
Adp.InsertCommand.ExecuteNonQuery() ' Use this instead

MessageBox.Show("Information Saved!")

The glitch I didn't notice yesterday was the Update method of data adapter. If you want to use Update method instead of ExecuteXXX methods, take a look at MSDN's documentation of SqlCommandBuilder Class.

[danielagaba]

hi

i'm using a .mdf database that i'm accessing from the server explorer in visual studio and it still shows my database is empty. Just to make sure i'm not losing it, i used breakpoints with the changes you've suggested and everything is working. The data is just not being displayed or stored. And about the primary key bit, i was entering diffferent values manually, i didnt put any in the code i gave.

[kvprajapati]

Check your database located at Debug\Bin folder. A database in your project folder get copied at Debug\bin each time a changes occurs in your database(mdf).

[Teme64]

A database in your project folder get copied at Debug\bin each time a changes occurs in your database(mdf).

That didn't happen to me :-/ Nothing was copied to Bin\Debug folder, at least the mdf file wasn't copied at any point of execution of the code. Besides, at the run-time (not in IDE) where would that mdf file be copied to?

Anyway, both SQL Server Management Studio and Server Explorer will reflect any changes in the (original) database table. If neither one shows any changes (after refreshing the view), the code failed for some reason.

[kvprajapati]

Teme64,
Ms Sql server local databases (SQLEXPRESS edition) are created in project folder (local databases). If such a case, that database is consider as a resource and it is copied at Debug\Bin folder by the VS.

[danielagaba]

Hi

I still cant see any data but when on a different form i data bound a combo box to the dataset, i get the values i've been enterin. But in the server explorer the database is empty

[danielagaba]

hi

i think i've identified the problem. When i databound the combo box to the dataset, it ws bringing the values that were stored in the dataset specified in the code NOT the one i created in the wizard. This i proved when i restarted VS2008 and run my project, the combo box was empty. Data is not reaching the dataset or database i created in the wizard.

[Teme64]

Hi danielagaba,
do you still have this problem unsolved? If it is, could you please post your current code? I've installed (finally) VB.NET 2008 so I could try to reproduce your scenario.

[sknake]

You should create a DataSet with the IDE and use it OR you should just execute your queries dynamically without the use of a DataSet. I personally don't consider creating a DataSet in code functionally identical to a designer generated DataSet. Since an INSERT can have triggers that execute post-update after you INSERT a record the DataSet will try to also SELECT that record back to see what the values are. DataSet's are a mess and you're going to have countless bugs trying to edit your data this way. I would choose another way to go about this.

[jigar.makhija]

Database is Continously Updating after addition in database so the previous data getting replaced by the new one ......