Click here to Skip to main content
14,699,541 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have been trying to commit these changes to the Access DB, but i get "Syntax error in the UPDATE statement" error each time. I could use an adapter but the datagridview(dgv) is connected to more than one tables and the code has to decide whether to update or insert. Please help me out.

Dim updateExamSql As String = "UPDATE Exams SET " & _
                       "Obj = " & CInt(dgv.Rows(e.RowIndex).Cells(2).Value) & ", " & _
                       "Comp = " & CInt(dgv.Rows(e.RowIndex).Cells(3).Value) & ", " & _
                       "Eng = " & CInt(dgv.Rows(e.RowIndex).Cells(4).Value) & ", " & _
                       "Sarufi = " & CInt(dgv.Rows(e.RowIndex).Cells(5).Value) & ", " & _
                       "Insha = " & CInt(dgv.Rows(e.RowIndex).Cells(6).Value) & ", " & _
                       "Kisw = " & CInt(dgv.Rows(e.RowIndex).Cells(7).Value) & ", " & _
                       "Maths = " & CInt(dgv.Rows(e.RowIndex).Cells(8).Value) & ", " & _
                       "Sci = " & CInt(dgv.Rows(e.RowIndex).Cells(9).Value) & ", " & _
                       "SS = " & CInt(dgv.Rows(e.RowIndex).Cells(10).Value) & ", " & _
                       "CRE = " & CInt(dgv.Rows(e.RowIndex).Cells(11).Value) & ", " & _
                       "SSRE = " & CInt(dgv.Rows(e.RowIndex).Cells(12).Value) & ", " & _
                       "Score = " & CInt(dgv.Rows(e.RowIndex).Cells(13).Value) & ", " & _
                       "Grade = '" & CStr(dgv.Rows(e.RowIndex).Cells(14).Value) & "' " & _
                       "WHERE ExamCode = " & CInt(dgv.Rows(e.RowIndex).Cells(15).Value) & " "

                   Dim cmd = New OleDb.OleDbCommand(updateExamSql, cn)
                   cmd.ExecuteNonQuery()
                   updates += 1
                   MsgBox(updates.ToString + " Rows Updated", vbInformation)
                   cmd.Dispose()
Posted
Comments
Michael_Davies 7-Sep-15 2:40am
   
First of all user parameters to store your data rather than building an SQL string.

Why cint() the values? You are building a string not ints, you need to ToString the cell values.
Martin Gitehi 7-Sep-15 16:04pm
   
Thanks Michael, can you demonstrate how to make the parameters please?
Richard chiu 8-Sep-15 5:37am
   
what is the error

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900