Saving data in a datatable

29 Nov 2007 7:29 PM

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?

29 Nov 2007 9:00 PM

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

Show quoteHide quote

"Kevin" <kmahoney@nospam_fireacademy.org> schreef in bericht
news:kb4uk3tn7j0fr6r8q0ul1cn9oa91m20ae8@4ax.com...
> 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?

29 Nov 2007 9:34 PM

Kevin

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

Show quoteHide quote

On Thu, 29 Nov 2007 22:00:19 +0100, "Cor Ligthert[MVP]" <notmyfirstn***@planet.nl> wrote:

>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" <kmahoney@nospam_fireacademy.org> schreef in bericht
>news:kb4uk3tn7j0fr6r8q0ul1cn9oa91m20ae8@4ax.com...
>> 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?