update function of a datgrid not updating

[spgreen]

All,

Very very new to all this, so here is my problem I have and update function that uses parameters in the SQL to up a record in a access database but when I run the function it completes ok but the record in the database does not get updated. Can anybody see what I’m doing wrong here.

Sub PhoneBook_Update(sender As Object, e As DataGridCommandEventArgs)

   Dim strStaffId as integer = e.Item.Cells(2).Text
   Dim strExt as string = CType(e.Item.Cells(9).Controls(0), TextBox).Text 'e.Item.Cells(9).Text
   Dim strOutsideline as string =  CType(e.Item.Cells(10).Controls(0), TextBox).Text 'e.Item.Cells(10).Text
   Dim strMobile as String =  CType(e.Item.Cells(11).Controls(0), TextBox).Text 'e.Item.Cells(11).Text
   Dim strLocation as String =  CType(e.Item.Cells(6).Controls(0), TextBox).Text 'e.Item.Cells(6).Text
   
   
   'Construct the SQL statement using Parameters
    Dim strSQL as String = "UPDATE [tblContacts] SET [Location] = @Loc, " & _
      "[WorkPhone] = @workphone, [WorkExtension] = @extNo, [MobilePhone] = @Mob WHERE [ContactID]= @StaffID"

    Const strConnString as String = "Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & _
"D:\Simon\Web Development\Westcare_Phone_Directory\Westcare_Phone_Directory.mdb"

    Dim objConn as New OleDbConnection(strConnString)
    objConn.Open()

    Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
    myCommand.CommandType = CommandType.Text

    ' Add Parameters to the SQL query
    Dim para_StaffId as OleDbParameter = new OleDbParameter("@StaffID", OleDbType.Integer)
    para_StaffId.Value = strStaffId
    myCommand.Parameters.Add(para_StaffId)


    Dim para_Ext as OleDbParameter = new OleDbParameter("@extNo", OleDbType.VarWChar,50)
    para_Ext.Value = strExt
    myCommand.Parameters.Add(para_Ext)

    Dim para_Outside as OleDbParameter = new OleDbParameter("@workphone", OleDbType.VarWChar,50)
    para_Outside.Value = strOutsideline
    myCommand.Parameters.Add(para_Outside)

    Dim para_Loc as OleDbParameter = new OleDbParameter("@Loc", OleDbType.VarWChar,50)
    para_Loc.Value = strLocation
    myCommand.Parameters.Add(para_Loc)

    Dim para_Mobile as OleDbParameter = new OleDbParameter("@Mob", OleDbType.VarWChar,50)
    para_Mobile.Value = strMobile
    myCommand.Parameters.Add(para_Mobile)

    msgbox(para_staffId.value  & " " &  para_Ext.Value & " " & para_Outside.Value & " " & para_Loc.Value & " " & para_Mobile.Value)


    myCommand.ExecuteNonQuery()   'Execute the UPDATE query
    objConn.Close()   'Close the connection
    
    'reset the intem index and rebind the data to reflect changes
    PhoneBook.EditItemIndex = -1
    BindData("")

End Sub

Thanks In Advance

Simon

 

[adamroof]

This is known prob for mdb updates...
Remove the parameter for the StaffID, and do this...

Dim strStaffId as integer = CType(e.Item.Cells(2).Text, Integer)

"UPDATE tblContacts SET Location=@Loc, WorkPhone=@workphone, WorkExtension=@extNo, MobilePhone=@Mob WHERE ContactID=" & strStaffId

forget the "&_" and put all on one line.