Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Re-using command objects

Posted on 2004-09-17
7
Medium Priority
?
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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