Updating a record in SQL via a datagridview

[Andrew_MCP]

Hi

I'm trying to update a selected row on a datagridview, I somehow had it kind of working, at least it was doing something but not quite right, now it just fails no matter what I am doing.

Firstly I have a sub that inserts a new record to SQLand this works just fine.

I then started building an update statement for it and the first issue I had was:

1) To reference the SQL UniquIdentifier value within the code, so that I could run update statement where ID = the currently selected value's Unique ID.

I did manage to get the code running but using a new ID and this put in a new row with the updated statement. So I proved it half worked, it just wasn't updating the row, but instead putting the updated data into a new row as it was a new ID.

Since that I don't know what I done but it kep failing with incorrect syntax on '.' and I output SQL to messagebox and I could not see any issues.

I'm not 100% sure now if the layout of the code is exactly right or why it keeps failing but have a look and see if anyone can see any issues:

Private Sub UpdateDataToQuery()


Dim MyCmd As New SqlCommand()
Dim da As New SqlDataAdapter()
Dim myTable As New DataTable()
Dim sSQL As String = String.Empty



'Open conection and load update query

myConn.Open()
MyCmd.Connection = myConn
MyCmd.CommandType = CommandType.Text

sSQL = "UPDATE MyTable1 set InputDate = @InputDate, StaffMember = @staffMember, Ward = @Ward, Defecit = @Defecit, Reason = @Reason, Band = @Band, AuthoriserName5 = @AuthoriserName5 , AuthoriserName6 = @AuthoriserName6 WHERE OT_ID = @guidValue"


MyCmd.CommandText = sSQL
MsgBox("SQL Code: " & sSQL)
da.SelectCommand = MyCmd
da.Fill(myTable) '<<<< Failing here with incorrect syntax at '.', now since cleaning up query it is failing with must declare variable @InputDate


'Parameters for update query



MyCmd.Parameters.AddWithValue("@guidValue", Guid.NewGuid)
MyCmd.Parameters.AddWithValue("@InputDate", DateTimePicker1.Value)
MyCmd.Parameters.AddWithValue("@staffMember", ComboBox1.GetItemText(ComboBox1.SelectedItem))
MyCmd.Parameters.AddWithValue("@Ward", ComboBox2.GetItemText(ComboBox2.SelectedItem))
MyCmd.Parameters.AddWithValue("@Defecit", ComboBox3.GetItemText(ComboBox3.SelectedItem))
MyCmd.Parameters.AddWithValue("@Reason", ComboBox4.GetItemText(ComboBox4.SelectedItem))
MyCmd.Parameters.AddWithValue("@Band", ComboBox5.GetItemText(ComboBox5.SelectedItem))
MyCmd.Parameters.AddWithValue("@AuthoriserName5", ComboBox6.GetItemText(ComboBox6.SelectedItem))
MyCmd.Parameters.AddWithValue("@AuthoriserName6", ComboBox7.GetItemText(ComboBox7.SelectedItem))


MyCmd.ExecuteNonQuery()

myConn.Close()

End Sub


I'm a bit mixed up now with what the code is actually doing so hopefully can get some pointers what I might have done wrong and how to set it out correctly.


Thanks

Andrew

Continue reading...