execute multiple SLQ statments off a signle connection

Dim connect As New SqlClient.SqlConnection(connectstring)


I want to execute  several statments on the single execute statement in a row or in a loop
I get an error on my second execution. I am new to .net so I'm not sure how to reuse a connection for an execute statement

for i = 1 to 100
 connect.execute    'but the vb.net equiv
next
LVL 2
unreal400Asked:
Who is Participating?
 
TorrwinCommented:
Ok, here is how I do it.  Say I have a dataset that I have filled with data called dsTemp, and I want to do an update statement.

RecordIndex = 0

RecordCount = dsTemp.Tables(0).Rows.Count

        While (RecordCount > RecordIndex)
            If Not (dsTemp.Tables(0).Rows(RecordIndex).Item("ID") Is System.DBNull.Value) Then
                myID = dsTemp.Tables(0).Rows(RecordIndex).Item("ID")


                    myCommand.CommandText = "UPDATE MY_TABLE SET USERNAME = '" & txtUserName.Text & "' WHERE ID = '" & myID & "'"
                    Try
                        myCommand.ExecuteNonQuery
                    Catch
                        ErrorMessage(Err.Description)
                        MessageBox.Show(Err.Description, "Main Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End Try
                End If
            End If
            RecordIndex += 1
      End While

This will update the usernames of all the records in my dataset.  You don't have to re-declare it every time, you can just change its commandtext if you want multiple commands.  Perhaps if you could describe what you are trying to do that might help.
0
 
TorrwinCommented:
You would want to do it like this:

MyConnection.open

 While (I < 100)
      myCommand = "Select..." Whatever your query is
      Execute your command
      I+=1
 End While

MyConnection.Close
0
 
unreal400Author Commented:
well thats obvious but the  actual  execution of the command inside the loop is the problem I am having


how do I execute it in the loop

do I do a sqlcommand()  then
do a executenonquery
 do I redim the object every time I want to put a new sqlcommend and execute it or how does it work
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
unreal400Author Commented:

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(SQLstatement,con)
 cmd.executenonquery()

inside a loop how do I change what sql statment the variable cmd is using?
do I dim it again or what goes one or is there a better way to execute a direct statement that does not return any values
0
 
unreal400Author Commented:
how is myCommand declared?
0
 
carl-Commented:

Hi,
  I do not re-Dim the command Text inbetween each usage, I just Dim it once, then execute nonquery as many times as I need until I close the connection.

        myConnection.Open
            myCommand.CommandText = "UPDATE myTable SET myField = '" & txtMyEditBox.Text & "' WHERE IDNUM = '" & myID & "'"
            myCommand.ExecuteNonQuery
            myCommand.CommandText="UPDATE TmyTable2 SET myField2='asdf' WHERE FieldA='123456'"
            myCommand.ExecuteNonQuery
      myConnection.Close
 
0
 
TorrwinCommented:
Dim myConnection as new SqlClient.SQLConnection
Dim myCommand as new SqlClient.SqlCommand

myConnection = "your connection string"
myCommand.Connection = myConnection
myCommand.CommandText = "your sql query"
0
 
RobertRFreemanCommented:
Usually you would use a dataadapter for this kind of operation.

See here for some tips:
http://vsnetdatabinding.blogspot.com/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.