Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

execute multiple SLQ statments off a signle connection

Posted on 2005-04-22
8
Medium Priority
?
299 Views
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 vb.net equiv
next
0
Comment
Question by:unreal400
8 Comments
 
LVL 13

Expert Comment

by:Torrwin
ID: 13846641
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
 
LVL 2

Author Comment

by:unreal400
ID: 13846669
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
 
LVL 2

Author Comment

by:unreal400
ID: 13846683

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Accepted Solution

by:
Torrwin earned 2000 total points
ID: 13846841
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
 
LVL 2

Author Comment

by:unreal400
ID: 13846868
how is myCommand declared?
0
 
LVL 4

Expert Comment

by:carl-
ID: 13846931

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

Expert Comment

by:Torrwin
ID: 13846984
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
 
LVL 16

Expert Comment

by:RobertRFreeman
ID: 13847196
Usually you would use a dataadapter for this kind of operation.

See here for some tips:
http://vsnetdatabinding.blogspot.com/
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

571 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