VB.NET

VB.NET RSS

input data not being saved in dataset or database

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: Feb 2009

Posts: 33

Reputation: danielagaba is an unknown quantity at this point

Solved Threads: 0

danielagaba danielagaba is offline

Offline

Light Poster

input data not being saved in dataset or database

Jul 20th, 2009

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.

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
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!")
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!")

•

Join Date: Aug 2008

Posts: 710

Reputation: Teme64 will become famous soon enough

Solved Threads: 114

Teme64

Offline

Master Poster

Re: input data not being saved in dataset or database

Jul 20th, 2009

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

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
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!")
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

Teme64 @ Windows Developer Blog

•

Join Date: Feb 2009

Posts: 33

Reputation: danielagaba is an unknown quantity at this point

Solved Threads: 0

danielagaba danielagaba is offline

Offline

Light Poster

Re: input data not being saved in dataset or database

Jul 21st, 2009

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

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
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")
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?

Last edited by danielagaba; Jul 21st, 2009 at 3:48 am. Reason: spelling errors

•

Join Date: Aug 2008

Posts: 710

Reputation: Teme64 will become famous soon enough

Solved Threads: 114

Teme64

Offline

Master Poster

Re: input data not being saved in dataset or database

Jul 21st, 2009

•

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

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
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!")
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.

Teme64 @ Windows Developer Blog

•

Join Date: Feb 2009

Posts: 33

Reputation: danielagaba is an unknown quantity at this point

Solved Threads: 0

danielagaba danielagaba is offline

Offline

Light Poster

Re: input data not being saved in dataset or database

Jul 21st, 2009

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.

•

Join Date: Oct 2008

Posts: 2,600

Reputation: adatapost has much to be proud of

Solved Threads: 460

adatapost

Offline

Posting Maven

Re: input data not being saved in dataset or database

Jul 21st, 2009

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).

Failure is not fatal, but failure to change might be. - John Wooden

•

Join Date: Aug 2008

Posts: 710

Reputation: Teme64 will become famous soon enough

Solved Threads: 114

Teme64

Offline

Master Poster

Re: input data not being saved in dataset or database

Jul 22nd, 2009

•

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.

Teme64 @ Windows Developer Blog

•

Join Date: Oct 2008

Posts: 2,600

Reputation: adatapost has much to be proud of

Solved Threads: 460

adatapost

Offline

Posting Maven

Re: input data not being saved in dataset or database

Jul 22nd, 2009

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.

Failure is not fatal, but failure to change might be. - John Wooden

•

Join Date: Feb 2009

Posts: 33

Reputation: danielagaba is an unknown quantity at this point

Solved Threads: 0

danielagaba danielagaba is offline

Offline

Light Poster

Re: input data not being saved in dataset or database

Jul 23rd, 2009

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

Last edited by danielagaba; Jul 23rd, 2009 at 3:25 am.

•

Join Date: Feb 2009

Posts: 33

Reputation: danielagaba is an unknown quantity at this point

Solved Threads: 0

danielagaba danielagaba is offline

Offline

Light Poster

Re: input data not being saved in dataset or database

#10

Jul 23rd, 2009

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.