add a datagridview values to sql table

Question

0.00/5 (No votes)

See more:

Hello,
I have a datagrid connected to an SQL table, how to programmatically with a button can insert specific cell values to another SQL database table?
here is vb net code gives an error "column0 has been declared"

VB

Dim ConnString As String = "Data Source=SUPERVISOR-PC;Initial Catalog=library;Integrated Security=True"
      Dim i As New Integer

      For i = 0 To DataGridView1.RowCount - 1

          Dim sql As String = "" & "INSERT INTO [atzenta].dbo.phones (" & "name, mobile, telephone" & ") VALUES (" & "@Column" & (i) & ", @Column" & (i) & ", @Column" & (i) & " )"

          Using cn As New SqlConnection(ConnString), cmd As New SqlCommand(sql, cn)

              cmd.Parameters.Add("@column" & (i), SqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(1).Value
              cmd.Parameters.Add("@column" & (i), SqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(4).Value
              cmd.Parameters.Add("@column" & (i), SqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(5).Value

              cn.Open()
              cmd.ExecuteNonQuery()
              cn.Close()
          End Using


      Next

  End Sub

changed SQL string to this and get cmd.executenonquery "incorect syntax Near Trans"

VB

Dim sql1 As String = "" & "INSERT INTO [atzenta].dbo.phones (" & "name, mobile, telephone" & ") VALUES (" & DataGridView1.Rows(i).Cells(1).Value & "," & DataGridView1.Rows(i).Cells(4).Value & ", " & DataGridView1.Rows(i).Cells(5).Value & ")"

and deleted the add parameter section

VB

Dim ConnString As String = "Data Source=SUPERVISOR-PC;Initial Catalog=library;Integrated Security=True"
       Dim i As New Integer

       For i = 0 To DataGridView1.RowCount - 1

           Dim sql1 As String = "" & "INSERT INTO [atzenta].dbo.phones (" & "name, mobile, telephone" & ") VALUES (" & DataGridView1.Rows(i).Cells(1).Value & "," & DataGridView1.Rows(i).Cells(4).Value & ", " & DataGridView1.Rows(i).Cells(5).Value & ")"

           Using cn As New SqlConnection(ConnString), cmd As New SqlCommand(sql1, cn)
               cn.Open()
               cmd.ExecuteNonQuery()
               cn.Close()
           End Using

Any correct SQL string syntax for this?

Posted 14-Jun-15 23:39pm

Member 3892343

Updated 14-Jun-15 23:59pm

v2

Add a Solution

1 solution

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)

Andy Lanng · Accepted Answer · 2015-06-14T23:59:00

All of these parameter declarations are within the same loop.

Let me show you the values in the first loop:

VB

'First loop so i = 0
 
'This is just a string at this stage
Dim sql As String = "" & "INSERT INTO [atzenta].dbo.phones (" & "name, mobile, telephone" & ") VALUES (" & "@Column0, @Column0, @Column0 )"
 
Using cn As New SqlConnection(ConnString), cmd As New SqlCommand(sql, cn)
 
'Paremeter @column0  added
cmd.Parameters.Add("@column0", SqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(1).Value
'Paremeter @column0 added - oh wait!  it has already been added - ERROR!
cmd.Parameters.Add("@column0", SqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(4).Value
 cmd.Parameters.Add("@column0", SqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(5).Value
 
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End Using

I hope that helps clarify the issue.

Let me know if you have any further problems ^_^