?
Solved

Re-using command objects

Posted on 2004-09-17
7
Medium Priority
?
241 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

762 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