Click here to Skip to main content
14,922,630 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Private Sub insert_Click()

   For each rw as DatagridviewRow in Datagridview1. Rows

          sql = "insert into Student (ID,name)" &_
          "Values ('" & rw.Cells (0).Value & '",'" & rw.Cells(1).Value &"')"

          cmd = New OleDbCommand (sql,conn)

      Catch ex As Exception 
          MsgBox (ex.Message)
      End Try

End Sub.

That is my code, but when I try to insert, it displays "syntax error in the INSERT INTO statement ".

Please help me.
Updated 19-Jan-16 3:52am

Firstly, you should use Parameterized queries[^]
Not only does it help to protect you against SQL Injection attacks it also removes the need to worry about those single quotes around values, nor do you need to convert the .Value with .ToString()

If you note your formatted code you appear to have a problem with quotes and double quotes. If you debug your code and look at the contents of the variable sql you will see it is not valid sql
insert into Student (ID, name)Values ('theValue

Set your sql to be something like
sql = "insert into Student (ID, name) Values (@value1, @value2)"
and create two parameters for your command
cmd.Parameters.Add(new OleDbParameter("@value1", rw.Cells(0).Value))
cmd.Parameters.Add(new OleDbParameter("@value2", rw.Cells(1).Value))

If you are not sure how to debug your code have a read of this article - Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
pairing of single quotes here:
rw.Cells (0).Value & '",'" 

should be
rw.Cells (0).Value & "','" 
Richmond Boateng 19-Jan-16 11:12am
Thank you all for your solution, it has really helped me
Peter Leow 19-Jan-16 22:33pm
You are welcome. Do note the advice from Solution 1 by CHill60.

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