SQL Database new row won't save

[ghall426]

I have the following code. When I add a new row, it adds it to the dataset in memory but never updates the database on the hard drive. What am I doing wrong?

myCon = New SqlConnection("Data Source=.\SQLEXPRESS..........)
myDA = New SqlDataAdapter("SELECT * FROM Rescue", myCon)
myDA.TableMappings.Add("Table", "Rescue")
myDS = New DataSet
myDA.Fill(myDS)
 
Dim myRow As DataRow = myDS.Tables("Rescue").NewRow
myRow.Item("CallNum") = Me.TxtCallNum.Text
myRow.Item("TimeCall") = Me.TxtTimeCall.Text
myDS.Tables("Rescue").Rows.Add(myRow)
myDA.Update(myDS)

[jmcilhinney]

What value does Update return? If it is non-zero then the record is being saved.

Dim recordsAffected As Integer = myDA.Update(myDS)
 
MessageBox.Show(recordsAffected & " records were affected.")

[ghall426]

What value does Update return? If it is non-zero then the record is being saved

It is returning 0.

[jmcilhinney]

I'm guessing that it is your use of a table mapping that is messing things up. Try this and see if it works:

myCon = New SqlConnection("Data Source=.\SQLEXPRESS..........)
myDA = New SqlDataAdapter("SELECT * FROM Rescue", myCon)
myDS = New DataSet
myDA.Fill(myDS, "Rescue")
 
Dim myRow As DataRow = myDS.Tables("Rescue").NewRow
myRow.Item("CallNum") = Me.TxtCallNum.Text
myRow.Item("TimeCall") = Me.TxtTimeCall.Text
myDS.Tables("Rescue").Rows.Add(myRow)
myDA.Update(myDS, "Rescue")

[ghall426]

Using your code, the update command returns the following error:

"Update requires a valid InsertCommand when passed DataRow collection with new rows."

This should be a straightforward answer. Simply how to add rows to a sql express database. Should be used by many people. Am I doing something different that is making this a harder question than it should be?

Greg

[jmcilhinney]

That indicates that it was definitely the table mapping that was messing you up before. The DataAdapter was looking for a table with the wrong name. Now it's looking for, and finding, the correct table, but you haven't provided any SQL code to insert new rows into the database. You couldn't retrieve any rows without a SELECT statement, so likewise you cannot add any rows without an INSERT statement. You could use an SqlCommandBuilder to generate the SQL code automatically but they have so many limitations that I suggest that you avoid them altogether. Follow the ADO.NET tutorial link in my signature and read up on the basics of data access. If you're still unable to proceed after that then post back again.

[ghall426]

Two things. First of all, you're right. I shouldn't have had that table mapping in there. It was accidentally left when I was trying to add multiple tables to the dataset.

But even with it removed, I got this error:

Update requires a valid InsertCommand when passed DataRow collection with new rows.

I saw on a few sites to add this line:

Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(myDA)

Not sure how it works when I don't ever really use this line besides declaring it here. But it works.

Greg

[jmcilhinney]

As I said in my previous post, you can use an SqlCommandBuilder to generate the required SQL code automatically. That's how it works. When your SqlDataAdapter requires the SQL code to insert a record it is automatically generated by the SqlCommandBuilder. I also said that I recommend against their use because they have important limitations. I recommend that you code your own SQL code to insert your records, as I said in my previous post. If you read about the SqlDataAdapter in the MSDN documentation you'll get pretty much all the information you need. I'd concentrate on the InsertCommand property.

[ghall426]

OK. I understand better now. Sorry I missed that in your previous post. But thanks for telling me again.

Greg