How to insert data from datagridview into database

Question

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 19-Jan-16 3:04am

Richmond Boateng

Updated 19-Jan-16 3:52am

CHill60

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2016-01-19T04:03:00

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[^]

Peter Leow · Accepted Answer · 2016-01-19T04:06:00

Solution 2

pairing of single quotes here:

rw.Cells (0).Value & '",'"

should be

rw.Cells (0).Value & "','"

Posted 19-Jan-16 4:06am

Peter Leow

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.