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

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***:( :( :( :( :( :( :( :( :(

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 .

VB Code:

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

VB Code:

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

Quote:

---

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.

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

Code:

---

      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! :)

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

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

VB Code:

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

:D