Saving data in a datatable

[Kevin]

I'm new to ADO.NET--trying to make the switch from ADO, which I've been using in my VB2005 apps up until now. Here's what I have:


Sub Save_Record()
Dim OldRecord as DataTable
Dim NewRecord as DataTable

Using testConnection As SqlConnection = New SqlConnection(SQLConnectionString)
Dim testCommand As SqlCommand = testConnection.CreateCommand()
testCommand.CommandText = "SELECT * FROM MasterRoster WHERE MR_StudentNumber = '" & txtStudentNumber.Text & "'"
Dim dataAdapter As New SqlDataAdapter(testCommand)
dataAdapter.Fill(OldRecord)
dataAdapter.Fill(NewRecord)

If NewRecord.Rows.Count = 0 Then
NewRecord.Rows.Add()
NewRecord.Rows(0).Item(53) = sysUser & " " & Now 'CreatedBy
End If


NewRecord.Rows(0).Item(0) = txtLName.Text
NewRecord.Rows(0).Item(1) = txtFName.Text
NewRecord.Rows(0).Item(2) = txtMI.Text

NewRecord.AcceptChanges()
dataAdapter.Update(NewRecord)


'Code to compare OldRecord to NewRecord cut from here

End Using
End Sub


Comparing the two datatables shows the changes I made, but the changes don't persist. How can I update the database with my changes?

 

[Cor Ligthert[MVP]]

Kevin,

The point is here the dataadapter, that has to have commands, that includes
the working of the update, delete and insert as is used by SQL. If you have
made the dataadapter in any generic way, then the problem is probably alone
in your acceptchanges.

It means something as: "set the rows in the dataadapter updated in the
database and accept the changes as done correct". Therefore those rows will
never been updated in your code. (The dataadapter does this automaticly for
you so there is not really need for that).

Cor

 

[Kevin]

So, what does that mean in terms of code? Am I missing something or coding something incorrectly?

 

[Kerry Moorman]

Kevin,

Don't call the data table's AcceptChanges method before calling the data
adapter's Update method.

Kerry Moorman

 

[Kevin]

Then it stops on the line 'dataAdapter.Update(NewRecord)' and I get an error:

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

 

[Kerry Moorman]

Kevin,

The data adapter needs a valid UpdateCommand, InsertCommand and
DeleteCommand, in addition to the SelectCommand that you provided.

You can either provide the commands yourself or use a SQLCommandBuilder,
which will provide the commands for you, assuming a very simple situation.

If you look up SQLDataAdapter in the help system and then look up the data
adapter's UpdateCommand property you will see examples of how to do this.

Kerry Moorman

 

[Cor Ligthert[MVP]]

Kevin,

Beside the commandbuilder you can use the generic tools in VS, a pity is,
that every version has his own so first tell us what version you are using.

To make the update, insert and delete with hand is not easy, for the most
part it is SQL script. (Now you see maybe the need for the LINk to sQl)
there shall be made an other meaning for the characters, however in practise
it is this.

LINQ is not using datasets by the way, it uses DBML which are an other kind
of classes, however as most things, based on the same principles done in
another way.

Cor

 

[Kevin]

I solved the problem myself by adding the following line:

Dim CmdBuilder As New SqlCommandBuilder = New SqlCommandBuilder (dataAdapter)