Solved

Re-using command objects

Posted on 2004-09-17
7
225 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JavaScript error 1 52
asp.net mvc return json 2 52
Anyway to make "All" the default in the dropdown? 6 34
ASP.NET Core index.html  LOCALHOST page cannot be found 2 22
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

932 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