Solved

Re-using command objects

Posted on 2004-09-17
7
231 Views
Last Modified: 2012-06-27
What do I have to do to re-use a command object? I want to be able to execute several commands without creating new objects, but I get an error that the command object is still open/fetching. Here's my code:

            Dim Conn As New OleDbConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim Cmd As New OleDbCommand

            Cmd.CommandText = "SELECT * FROM Table1"
            Cmd.Connection = Conn
            Conn.Open()

            Try
                Dim rs As OleDbDataReader = Cmd.ExecuteReader

                While rs.Read
                        .... read the data
                End While
                rs.Close()

And I'd like to follow this with:

            Cmd.CommandText = "SELECT * FROM Table2"
            Cmd.Connection = Conn
            Conn.Open()

            Try
                Dim rs As OleDbDataReader = Cmd.ExecuteReader

                While rs.Read
                        .... read the data
                End While
                rs.Close()

What do I need to do in terms of closing or disposing of the objects?
0
Comment
Question by:crescendo
  • 3
  • 2
  • 2
7 Comments
 
LVL 20

Expert Comment

by:ihenry
ID: 12087158
Why don't use multiple select statements and use OleDbDataReader.NextResult method for each retrieval.

Cmd.CommandText = "SELECT * FROM Table1;SELECT * FROM Table2"
.....
Dim rs As OleDbDataReader = Cmd.ExecuteReader
.....
While rs.Read
.... read the data
End While
'continue with the next recordset
rs.NextResult()
While rs.Read
.... read the data
End While
0
 
LVL 9

Author Comment

by:crescendo
ID: 12087196
That's a neat answer but my second query depends on results from the first, so I can't build a multiple select statement. I like the idea, though, and I'm sure it will come in handy on other projects.

Thanks
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12087249
you need to close and dispose your connection and command after each query,nonquery etc

cnn.Close()
            cmd.Dispose()
            cnn.Dispose()

Regards,

Aeros
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 17

Expert Comment

by:AerosSaga
ID: 12087254
Example:

Private Sub LoadTier3Data()
        Dim cnn As New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("SiteDB"))
        Dim cmd As New OleDb.OleDbCommand

        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT Tier3ID, Tier2.Name + ' » ' + Tier3.Name AS " & _
            "Name, Tier3.ImageSrc FROM Tier3 INNER JOIN Tier2 ON " & _
            "Tier3.Tier2ID = Tier2.Tier2ID ORDER BY Tier2.Name, Tier3.Name"
        cmd.Connection = cnn

        Tier3.DataKeyField = "Tier3ID"
        cnn.Open()
        Tier3.DataSource = cmd.ExecuteReader()
        Tier3.DataBind()
        cnn.Close()

        cmd.Dispose()
        cnn.Dispose()

        If Tier3.Items.Count = 0 Then
            Tier3.Visible = False
        Else
            Tier3.Visible = True
        End If
    End Sub
0
 
LVL 9

Author Comment

by:crescendo
ID: 12087363
Hi again

<<you need to close and dispose your connection and command after each query,nonquery etc>>

How do I re-use a connection/command after disposing it? Do I need to Dim it again, or what? I was trying to save a few lines of typing, plus some execution time, but it looks like I'll end up creating a second and third connection and command after all.

0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12087419
I guess you could set one connection per page as long as its global, or you could store it in a key in your web.config
0
 
LVL 20

Accepted Solution

by:
ihenry earned 250 total points
ID: 12087503
I can execute the following code without any problem.

Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Projects\WebVB\northwind.mdb;")
Dim cmd As New OleDbCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "select * from Employees"
cmd.Connection = cnn
cnn.Open()

Dim rd1 As OleDbDataReader = cmd.ExecuteReader()
While rd1.Read
    Dim ln As String = rd1("LastName")
End While
rd1.Close()

cmd.CommandText = "select * from Customers"
Dim rd2 As OleDbDataReader = cmd.ExecuteReader()
While rd2.Read
    Dim ln As String = rd2("Address")
End While
rd2.Close()
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ck editor is taking too much time to be loaded in asp 1 21
Save ms data to server side. 19 55
Function after success of Jquery/AJAX action 9 28
asp.net mvc 2 25
Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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