Inserting Records into SQL - Should be easy

[Guest]

I found the following code on a website that works fine

Dim strSQL As Strin

strSQL = "Insert into Employees(lastname,firstname) values('Al','Coholic')

Dim objcommand As New SqlClient.SqlCommand(strSQL, New
SqlClient.SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=;")

objcommand.Connection.Open(
objcommand.ExecuteNonQuery(
objcommand.Connection.Close(

objcommand.Dispose(
objcommand = Nothin

i want to do something similar but want to do multiple inserts within a loop - here is my code

Dim strSQL As Strin

strSQL = "Insert into Employees(lastname,firstname) values('Al','Coholic')

Dim objcommand As New SqlClient.SqlCommand(strSQL, New
SqlClient.SqlConnection("server=Sql-2;database=Northwind;uid=sa;pwd=;")

For x = 1 To
strSQL = "Insert into Employees(lastname,firstname) values('" & x & "','" & x & "')
objcommand.Connection.Open(
objcommand.ExecuteNonQuery(
objcommand.Connection.Close(
Nex
objcommand.Dispose(
objcommand = Nothin

i want to insert the values 1-5 for the first name and last name -- i am having a hard time finding and understanding what i am doing wrong - any help would be appreciated

 

[Jason Roozee]

I don't see where you're re-setting the strsQL in to the comand object. Try
this:



Dim strSQL As String



For x = 1 To 5
strSQL = "Insert into Employees(lastname,firstname) values('" &
x & "','" & x & "')"
Dim objcommand As New SqlClient.SqlCommand(strSQL, New _

SqlClient.SqlConnection("server=Sql-2;database=Northwind;uid=sa;pwd=;"))
objcommand.Connection.Open()
objcommand.ExecuteNonQuery()
objcommand.Connection.Close()
Next
objcommand.Dispose()
objcommand = Nothing

I found the following code on a website that works fine:

Dim strSQL As String

strSQL = "Insert into Employees(lastname,firstname) values('Al','Coholic')"

Dim objcommand As New SqlClient.SqlCommand(strSQL, New _
SqlClient.SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=;"))

objcommand.Connection.Open()
objcommand.ExecuteNonQuery()
objcommand.Connection.Close()

objcommand.Dispose()
objcommand = Nothing

i want to do something similar but want to do multiple inserts within a loop - here is my code:

Dim strSQL As String

strSQL = "Insert into Employees(lastname,firstname) values('Al','Coholic')"

Dim objcommand As New SqlClient.SqlCommand(strSQL, New _
SqlClient.SqlConnection("server=Sql-2;database=Northwind;uid=sa;pwd=;"))

For x = 1 To 5
strSQL = "Insert into Employees(lastname,firstname) values('" & x & "','" & x & "')"
objcommand.Connection.Open()
objcommand.ExecuteNonQuery()
objcommand.Connection.Close()
Next
objcommand.Dispose()
objcommand = Nothing


i want to insert the values 1-5 for the first name and last name -- i am

having a hard time finding and understanding what i am doing wrong - any
help would be appreciated.

 

[Jason Roozee]

Actually, an even more proper way:

Dim strSQL As String

'Create a DB connection once!
Dim DatabaseConnection as SqlClient.SqlConnection = New
SqlClient.SqlConnection("server=Sql-2;database=Northwind;uid=sa;pwd=;"))
DatabaseConnection.open

For x = 1 To 5
strSQL = "Insert into Employees(lastname,firstname) values('" &
x & "','" & x & "')"
Dim objcommand As New SqlClient.SqlCommand(strSQL,
DatabaseConnection )


objcommand.ExecuteNonQuery()
Next
DatabaseConnection.close
objcommand.Dispose()
objcommand = Nothing

 

[Guest]

thank u for your quick response -

What would i declare DatabaseConnection as?

 

[Jason Roozee]

I showed you in the example:

Dim DatabaseConnection as SqlClient.SqlConnection = New
SqlClient.SqlConnection("server=Sql-2;database=Northwind;uid=sa;pwd=;"))
DatabaseConnection.open

 

[Z D]

you're not setting strSQL to anything inside the loop!

also, you dont need to keep opening/closing the connection in the loop.
Open it once outside the loop and then close it at the end of the loop.

Also, you should try/catch the update and in the finally you should put a
..close call also otherwise the db connection will remain open if there's an
unhandled exception.

I found the following code on a website that works fine:

Dim strSQL As String

strSQL = "Insert into Employees(lastname,firstname) values('Al','Coholic')"

Dim objcommand As New SqlClient.SqlCommand(strSQL, New _
SqlClient.SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=;"))

objcommand.Connection.Open()
objcommand.ExecuteNonQuery()
objcommand.Connection.Close()

objcommand.Dispose()
objcommand = Nothing

i want to do something similar but want to do multiple inserts within a loop - here is my code:

Dim strSQL As String

strSQL = "Insert into Employees(lastname,firstname) values('Al','Coholic')"

Dim objcommand As New SqlClient.SqlCommand(strSQL, New _
SqlClient.SqlConnection("server=Sql-2;database=Northwind;uid=sa;pwd=;"))

For x = 1 To 5
strSQL = "Insert into Employees(lastname,firstname) values('" & x & "','" & x & "')"
objcommand.Connection.Open()
objcommand.ExecuteNonQuery()
objcommand.Connection.Close()
Next
objcommand.Dispose()
objcommand = Nothing


i want to insert the values 1-5 for the first name and last name -- i am

having a hard time finding and understanding what i am doing wrong - any
help would be appreciated.

 

[Cor]

Hi PCL

In addion to Jazon, as advise, remove those useless setting to nothing the
only cost time and have no sense.

Cor

 

[Cor]

Hi PCL,

I did not see it, also in addition to the good advise from ZD



Cor

 

[Guest]

Thank you very much for all your help. I thought by haviing that Dim statement in the loop it would error out. It is frustrating thast a job that i could have done in 2 days in vb6 has taken me over 2 weeks already.....I am struggling at the jump to .NET. Im sure i will have plenty more questions in the future - Thanx Again for your help everyone!