Inserting Records into SQL - Should be easy

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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



PCL said:
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
Click to expand...
having a hard time finding and understanding what i am doing wrong - any
help would be appreciated.
 
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
 
I showed you in the example:

Dim DatabaseConnection as SqlClient.SqlConnection = New
SqlClient.SqlConnection("server=Sql-2;database=Northwind;uid=sa;pwd=;"))
DatabaseConnection.open
 
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.
PCL said:
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
Click to expand...
having a hard time finding and understanding what i am doing wrong - any
help would be appreciated.
 
Hi PCL

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

Cor
 
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!
 
Back
Top