Failure to insert multiple rows into SQL database

I have an app that imports data from a csv file into a dataset. The user views the dataset and then decides to import the data into the database by clicking the code below.

I am getting the error message below when the app gets to the line 'objCommand.ExecuteNonQuery()'

"Message="The variable name '@.PartNumber' has already been declared. Variable names must be unique within a query batch or stored procedure.
Incorrect syntax near '?'."

My questions are:

1) Why is the code failing at this point ? ( i have hard coded the part number and part name to test my code)

2) How do i pass the actual value of PartNumber and PartName from each datarow into the parameter ?

Private Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click

Dim objCommand As SqlCommand = New SqlCommand

Dim datatable As DataTable

DataTable = dsimport.Tables(0)

Dim dataRow As DataRow

'Setup SQLcommand

objCommand.Connection = objConnection

objCommand.CommandText = "INSERT into Part (PartNumber,PartName) VALUES (?,?)"

objCommand.CommandType = CommandType.Text

For Each dataRow In dsimport.Tables(0).Rows

'Parameter for the PartNumber field...

objCommand.Parameters.AddWithValue("PartNumber", "2R8T-14A005-AA")

'Parameter for the PartName field...

objCommand.Parameters.AddWithValue("PartName", "Test3")

Next

'Open the connection...

objConnection.Open()

'Execute the SqlCommand object to update the data...

objCommand.ExecuteNonQuery()

'Close the connection...

objConnection.Close()

objCommand = Nothing

objConnection = Nothing

End Sub

This article talks about where you can use (?) placeholder.

http://authors.aspalliance.com/aspxtreme/adonet/usingstoredprocedureswithcommand.aspx

In your case you can use OleDbCommand to accomplish the same thing. I have an example below in which I read couple of rows from an excel spreadsheet and insert them into SQL Database.

-

Dim cn As New OleDbConnection, cn1 As New OleDbConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim cmd As New OleDbCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\Test.xls;" + "Extended Properties=""Excel 8.0;HDR=Yes;"""
cn1.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;"

cn.Open()
cn1.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from TestTable"
adapter.SelectCommand = cmd
adapter.Fill(dtset)

cmd.Connection = cn1
cmd.CommandText = "Insert into Parts (PartNumber,PartName) Values(?,?)"
cmd.CommandType = CommandType.Text

cmd.Parameters.Add("PartNumber", OleDbType.VarChar, 20)
cmd.Parameters.Add("PartName", OleDbType.VarChar, 20)
For Each dr In dtset.Tables(0).Rows
cmd.Parameters("PartNumber").Value = dr(0).ToString()
cmd.Parameters("PartName").Value = dr(1).ToString()
cmd.ExecuteNonQuery()
Next

cn.Close()
cn1.Close()

-

Hope this helps

|||

Using SqlCommand you can use this example:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\Test.xls;" + "Extended Properties=""Excel 8.0;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from TestTable"
adapter.SelectCommand = cmd
adapter.Fill(dtset)

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into Parts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dtset.Tables(0).Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps