Update requires a valid UpdateCommand when passed DataRow collection with modified rows. What Can Be Done For That?

Question

Dim conn As OleDbConnection

Dim com As OleDbCommand

conn = New 

OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= D:\Mohamed Ayman\Donic.mdb")

com = New OleDbCommand("Update SET [Firstname] =@fn , [Surname] =@sn  ,  [Gender] =@g  , [PhoneNumber] =@p1  , [PhoneNumber2] =@p2  , [PhoneNumber3] =@p3  , [Country] =@co  , [City] =@ci  , [Club] =@cl  , [Notes] =@n  , [Email] =@e  , [CurrentDate] =@cd  , [Date] =@d where [Firstname] =@store  And [SurName] =@store1 And [PhoneNumber] =@store2  And  [Club] =@store3 ", conn)


conn.Open()
com.Parameters.AddWithValue("@store", tsearch.Text)
com.Parameters.AddWithValue("@store1", ComboBox.Text)
com.Parameters.AddWithValue("@store2", ComboBox3.Text)
com.Parameters.AddWithValue("@store3", ComboBox2.Text)
com.Parameters.AddWithValue("@fn", txt.Text)
com.Parameters.AddWithValue("@sn", txt1.Text)
If m.Checked = True Then
    com.Parameters.AddWithValue("@g", "Male")
End If
If f.Checked = True Then
    com.Parameters.AddWithValue("@g", "Female")
End If
com.Parameters.AddWithValue("@p1", p1.Text)
If p2.Enabled = True Then
    com.Parameters.AddWithValue("@p2", p2.Text)
End If
If p3.Enabled = True Then
    com.Parameters.AddWithValue("@p3", p3.Text)
End If

com.Parameters.AddWithValue("@co", co.Text)
If ci.Enabled = True Then
    com.Parameters.AddWithValue("@ci", ci.Text)
End If
com.Parameters.AddWithValue("@cl", cl.Text)
If Not n.Text = "" Then
    com.Parameters.AddWithValue("@n", n.Text)
End If
If Not eee.Text = "" Then
    com.Parameters.AddWithValue("@e", eee.Text)
End If
com.Parameters.AddWithValue("@cd", Date.Today)
com.Parameters.AddWithValue("@d", start)

com.ExecuteNonQuery()
MsgBox("Record Updated")
conn.Close()

i did ur advice and i get a new error a weird one that // ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.// how is that its obvious that conn.open and then closed !!

Answer 1

You don't need an OleDbDataAdapter for this. This is not the intended purpose of an OleDbDataAdapter. You should simply use an OleDbCommand.

The example below is not complete, I don't know exactly the name of your fields, but in essence, you should prepare an UPDATE sql text where you set the values of the fields that should be updated and put a placeholder (?) instead of the actual value. Then you build a new OleDbCommand, set its connection and its command text, and fill the Parameters collection with the parameter's names and values.

Being this an OleDb situation, the names of the parameters are not significant but the order in which you add them to the collection is essential.
You should respect the exact order in which the placeholders appears in the command text (So the value for the ClientsID should be the last one)

Using conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= D:\Mohamed Ayman\Donic.mdb"
Using cmd = new OleDbCommand()
     conn.Open()
     Dim clientID = Convert.ToInt32(dsnew.Tables("Clients").Rows(0)("ClientsID"))
     sqlnew = "UPDATE Clients SET [FirstName] = ?, [SurName] = ?, " & 
              "Gender = ?, Phone = ? ........ WHERE ClientsID = ?"
     cmd.Connection = con
     cmd.CommandText = sqlNew
     cmd.Parameters.AddWithValue("@fname", txt.Text)
     cmd.Parameters.AddWithValue("@sname", txt1.Text)
     cmd.Parameters.AddWithValue("@gend", If m.Checked, "Male", If f.Checked, "Female", "");
     cmd.Parameters.AddWithValue("@phone", p1.Text);
     .....
     cmd.Parameters.AddWithValue("@id", clientID);
     cmd.ExecuteNonQuery()
End Using
End Using

Notice that I have also changed the way in which you open and close the connection. The best practice is to use the USING STATEMENT. This will close and dispose automatically the connection and the command when you exit from the using block. And this is true also in case of exceptions

And as a side note. When you need to do such validations it is better to isolate all these test in a separate function that returns true or false. If the function returns true you proceed with the update

 Public Function IsDataValid() As Boolean
    If txt.Text = "" Then
        MsgBox("Please Enter First Name")
        txt.Focus()
        return False
    Else if txt1.Text = "" Then
        MsgBox("Please Enter Second Name ")
        txt1.Focus()
        return False
    Else If m.Checked = False And f.Checked = False Then
        ....
    Else 
        return true
    End If

Thus your code could be simplified to

If IsDataValid() Then
    .... execute the update
End if