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

   For each rw as DatagridviewRow in Datagridview1. Rows

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

          conn()
          cmd = New OleDbCommand (sql,conn)
          cmd.ExecuteNonQuery()

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

End Sub.

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

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

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
SQL
insert into Student (ID, name)Values ('theValue

Set your sql to be something like
SQL
sql = "insert into Student (ID, name) Values (@value1, @value2)"
and create two parameters for your command
VB
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 & "','" 
   
Comments
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