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 DataTableDataTable = dsimport.Tables(0)
Dim dataRow As DataRow 'Setup SQLcommandobjCommand.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 =
NothingobjConnection =
Nothing End Sub
Failure to insert multiple rows into SQL database
Beto
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
The_Landlord
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