execute multiple SLQ statments off a signle connection

Posted on 2005-04-22
Last Modified: 2010-04-23
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 equiv
Question by:unreal400
    LVL 13

    Expert Comment

    You would want to do it like this:

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

    LVL 2

    Author Comment

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

    Author Comment


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

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

    Accepted Solution

    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 & "'"
                            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.
    LVL 2

    Author Comment

    how is myCommand declared?
    LVL 4

    Expert Comment


      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.

                myCommand.CommandText = "UPDATE myTable SET myField = '" & txtMyEditBox.Text & "' WHERE IDNUM = '" & myID & "'"
                myCommand.CommandText="UPDATE TmyTable2 SET myField2='asdf' WHERE FieldA='123456'"
    LVL 13

    Expert Comment

    Dim myConnection as new SqlClient.SQLConnection
    Dim myCommand as new SqlClient.SqlCommand

    myConnection = "your connection string"
    myCommand.Connection = myConnection
    myCommand.CommandText = "your sql query"
    LVL 16

    Expert Comment

    Usually you would use a dataadapter for this kind of operation.

    See here for some tips:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video discusses moving either the default database or any database to a new volume.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now