• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Re-using command objects

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
crescendo
Asked:
crescendo
  • 3
  • 2
  • 2
1 Solution
 
ihenryCommented:
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
 
crescendoAuthor Commented:
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
 
AerosSagaCommented:
you need to close and dispose your connection and command after each query,nonquery etc

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

Regards,

Aeros
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
AerosSagaCommented:
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
 
crescendoAuthor Commented:
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
 
AerosSagaCommented:
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
 
ihenryCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now