Stuck in saving Records in DB using ADO.Net (RESOLVED)

[moinkhan]

I am editing a specific column of a DataRow using Item collection.. then i am using DataAdapter's Update method to save the changes but it is giving me

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

Now how can i know the update command for this row@ run time? I am lost
Well.. I am new to Ado.Net and VB.Net......

***Believe me.. I used to be a VB GURU***

[Pirate]

Here you need to define the variable MyConnection as
OleDbConnetion supplied with connection string . btw , you don't
need OleDbAdapter in this method . Here you are actually
executing an OleDbCommand .

Public Sub EditMyData(ByVal SQL_UPDATE As String)
OpenDB()
 
Dim MyCommand As New OleDbCommand(SQL_UPDATE, MyConnection)
Try
If MyCommand.ExecuteNonQuery() Then
 MsgBox("Successfully Updated")
Else
MsgBox("WRONG")
End If
Catch x As Exception
MsgBox(x.Message)
End Try
 
MyConnection.Close()
MyCommand.Dispose()
 End Sub

Usage

Private Sub Button7_Click(ByVal sender As System.Object, ByVal 
e As System.EventArgs) Handles Button7.Click
 
 Dim MyTable As String = "MyTab"
 
        Dim str1 As String = TextBox1.Text
        Dim str2 As String = ComboBox1.Text
        Dim str3 As String = ComboBox2.Text
        Dim str4 As String = TextBox2.Text
        Dim str5 As String = TextBox3.Text
        Dim str6 As String = TextBox4.Text
        Dim str7 As String = TextBox5.Text
        Dim str8 As String = ComboBox3.Text
        Dim str9 As String = TextBox6.Text
 
Dim Updater As String = "UPDATE " & MyTable & " SET 
YourField1='" & str1 & "'," & " YourField2='" & str2 & "'," & " 
YourField3='" & str3 & "'," & " YourField4='" & str4 & "'," & " 
YourField5='" & str5 & "'," & " YourField6='" & str6 & "'," & " 
YourField6='" & str7 & "'," & "YourField7='" & str8 & "'," & " 
YourField8='" & str9 & "'" & " WHERE YourField1='" & str1 & "'"
 
 
'Call the sub to save edited data
EditMyData(Updater)
End Sub

[DevGrp]

Originally posted by moinkhan
I am editing a specific column of a DataRow using Item collection.. then i am using DataAdapter's Update method to save the changes but it is giving me

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

Now how can i know the update command for this row@ run time? I am lost
Well.. I am new to Ado.Net and VB.Net......

***Believe me.. I used to be a VB GURU***

Post some code so we can see what you are doing wrong. Its more than likely that you did not specify an update command.

[Wallabie]

As DevGrp has stated, it sounds like you haven't put in the command builders. there is one for each action: Insert, Update & Delete

You'll need to use .GetUpdateCommand like so....

      Dim cmdBuild As OleDbCommandBuilder = New OleDbCommandBuilder(daCty)

        'Generate the command builders
        daCty.UpdateCommand = cmdBuild.GetUpdateCommand

You might have to post your code for us to help you to see what you have done....or not done!

[moinkhan]

Thanx.. It's resovled..
By the hints from u ppl i wrote the following Code....
and it worked...
Thanx everybody

Wallabie!! your GetUpdateCommand seemed to do the trick.. Thanx....

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = F:\Moin\.Net Products\EditingDataSet\temp.mdb"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "Select * from Crime"
        da.SelectCommand = cmd
        da.Fill(ds, "Crime")
        Dim cmdBuilder As New OleDbCommandBuilder(da)
        ds.Tables(0).Rows(0).Item("Doer") = "Salman Talpur Achakzai"
        da.UpdateCommand = cmdBuilder.GetUpdateCommand
        da.Update(ds, "Crime")
    End Sub
End Class