How do i insert several records into SQLserver (2005)?
Hi,
Im new to VB.NET 2005 and i want to insert several records into a SQLServer database. The first record is ok, but the next will crash. Heres my code:
Dim iCnt As Integer
Dim cmd As New SqlClient.SqlCommand
Dim con As New SqlClient.SqlConnection
cmd.Connection = con
con.ConnectionString = "[MY CONN STRING]"
Try
con.Open()
For iCnt = 0 To 9
cmd.CommandText = "INSERT INTO Prod (Field1, Field2, Field3) " _
& "VALUES (@Field1,@Field2,@Field3)"
cmd.Parameters.Add("@Field1", SqlDbType.NVarChar, 10).Value = "Test " & iCnt
cmd.Parameters.Add("@Field2", SqlDbType.NVarChar, 20).Value = "Another test " & iCnt
cmd.Parameters.Add("@Field3", SqlDbType.NVarChar, 20).Value = "Last test " & iCnt
cmd.ExecuteNonQuery()
cmd.Dispose()
Next iCnt
Catch ex As Exception
MsgBox(ex.Message)
Finally
con.Close()
con.Dispose()
cmd.Dispose()
End Try
The error message i get:
"The variable name'@Field1' has already been declared. Variable names must be unique within a query batch or stored procedure."
What am I doing wrong?
Is there a more effective way of inserting data to SQLServer?
Thanks,
[1422 byte] By [
Rune73] at [2007-12-24]
Personally, I would say that using a DataSet and TableAdapter is a more effective way.
But as to this problem - the parameters only need to be added to the command once. After that, you just need to change each parameter's value.
But since you're setting teh command text on each itteration of the loop, you could just provide the values directly.
So here's an example of the first fix:
'The command text only needs to be set once when using parameters
cmd.CommandText = "INSERT INTO Prod (Field1, Field2, Field3) " _
& "VALUES (@Field1,@Field2,@Field3)"
For iCnt = 0 To 9
If Not cmd.Parameters.Contains("@Field1") Then
cmd.Parameters.Add("@Field1", SqlDbType.NVarChar, 10).Value = "Test " & iCnt
Else
cmd.Parameters.Item("@Field1").Value = "Test" & iCnt
End If
'Wrap other parameters in similar IF statement
cmd.Parameters.Add("@Field2", SqlDbType.NVarChar, 20).Value = "Another test " & iCnt
cmd.Parameters.Add("@Field3", SqlDbType.NVarChar, 20).Value = "Last test " & iCnt
cmd.ExecuteNonQuery()
cmd.Dispose()
Next iCnt
The second fix would be to forget the parameters and set the values directly (psuedo-code):
For iCnt = 0 To 9
cmd.CommandText = "INSERT INTO Prod (Field1, Field2, Field3) " _
& "VALUES ('Test " & iCnt & "', 'Another test " & iCnt & "','Last test " & iCnt &"')"
cmd.ExecuteNonQuery()
cmd.Dispose()
Next iCnt
Hope that helps!
Or simply modify your original loop to have a "cmd.Parameters.Clear" statement prior to your first parameter add statement.
Clearing the parameter collection each time would work, however it would add unnecessary overhead to the procedure. Testing for the existence of the parameters would be more efficient.
I guess I don't understand how it works then. How is clearing the collection and adding the parameters more overhead than using an if statement to loop through the parameters checking for the value then adding or modifing if found? At least that's how I understand the '.contains' to be implemented.
While that may be true of the Contains() method, consider the Add() method on the parameters collection... Each time it's called, a new Parameter object has to be instantiated, its properties set, and then it's added to the collection.
And truely, it would be even better to add the parameters outside the loop and then change each value without needing to test whether the parameter exists or not. I used the testing scenario to help the OP understand why their code was failing - that is, you can step through the loop in debug and watch the test being performed to see that the parameter already exists and so its value only needs to be changed.
Since everything's an object I guess I just figured that adding an object to the collection was bound to produce the most efficient code 
And you are correct, adding the parameters outside the loop would be more streamlined, but optmizing the procedure wasn't part of the OP's original question so I didn't even go near that can of worms.
actually clearing out the parameters is better than to have to check each parameter, if it exists set the value and if it doesnt then declare it and set the value - in fact this is what alot of our projects do :-) There is not much overhead as you may be emphisiasing :-)
You could however declare the parameters before hand, (before opening SQL connection), and set values, then within your interation loop just reset the values and execute the non query. This would be a good way of doing things.
Just to add - its better to used a Stored Procedure on the SQL End as its faster and securer plus you don't need to care about the syntax when it comes to using it in the client (your app) - just simply call the SPROC and pass it the parameters it requires ;-)
Thanks, to all of you!
I have now tried all of your suggestions and they all seem to work :)
But, I notice if I add a lot of records (ex 10 000 rows) to the SQLserver database (dbname = TestVBNet), the memory usage for Sqlservr.exe is increasing with about 10 MB and the memory usage won't go down again...
Do you know how to avoid that?
Will this be solved with a stored procedure inside the database?
1. But, since I’m new to this, I don't know how to create an INSERT INTO stored procedure in SQLEXPRESS/SQL Server Management Studio Express 2005. Could anyone help me?
2. Also, how would the code for using DataSet and TableAdapter be?
Many "stupid" questions... but I really appreciate your help on this. :-)
Here’s the "test code":
Dim iCnt As Integer
Dim cmd As New SqlClient.SqlCommand
Dim con As New SqlClient.SqlConnection
cmd.Connection = con
con.ConnectionString = "[MY CONN STRING]"
con.Open()
cmd.CommandText = "INSERT INTO Prod (Field1, Field2, Field3) " _
& "VALUES (@Field1,@Field2,@Field3)"
For iCnt = 0 To 9
cmd.Parameters.Clear()
cmd.Parameters.Add("@Field1", SqlDbType.NVarChar, 10).Value = "Test " & iCnt
cmd.Parameters.Add("@Field2", SqlDbType.NVarChar, 20).Value = "Another test " & iCnt
cmd.Parameters.Add("@Field3", SqlDbType.NVarChar, 20).Value = "Last test " & iCnt
cmd.ExecuteNonQuery()
cmd.Dispose()
Next iCnt
con.close
//Rune
Many thanks! You saved my bacon with your suggestion of using
cmd.Parameters.Clear();
