[RESOLVED] Use DataReader twice in one sub

I'm about two days in to using .Net and I'm trying to convert an existing application over. I'm the first to admit I'm fumbling around using old concepts in a new environment but anyway:

I'm connecting to a SQL 2005 database, extracing some data, and want to do that again in the same sub. In the code below the first select works. In the second the objDataReader.HasRows is true but the loop doesn't kick in. I'm assuming I'm at the end of file from the first loop. I tried by old RecordSet processing (closing and opening) and a few other things.

What do I need to do to clean up the last query and do a second? I'm using the same select because I know it brings back data from the first step.

Code:

---

Private Sub TestIt()

        Dim sConnection As String = "Server=devsql.corp.compmgt.com\image1;Database=eDocs;Trusted_Connection=Yes"
        Dim objCommand As New SqlCommand

        objCommand.CommandText = "Select ParmString from refParm where ParmDesc = 'Citrix Image Import " & _
                                Me.cboDocClass.Text & "'"

        objCommand.Connection = New SqlConnection(sConnection)
        objCommand.Connection.Open()

        Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()

        If objDataReader.HasRows Then
            Do While objDataReader.Read()
                gvSourceFolder = objDataReader("ParmString").ToString()
            Loop
        Else
            MsgBox("Failed")
        End If

        '<===== I'm stuck here

        objDataReader.Close() '<=== Doesn't work

        objCommand.CommandText = "Select ParmString from refParm where ParmDesc = 'Citrix Image Import " & _
                                Me.cboDocClass.Text & "'"

        If objDataReader.HasRows Then
            Do While objDataReader.Read()
                gvSourceFolder = objDataReader("ParmString").ToString()
            Loop
        Else
            MsgBox("Failed")
        End If

    End Sub

---

I got so burned with this when I first started with .NET

You can't just close and reuse a datareader within the same procedure like you can with a VB6 recordset.

In order to use the same datareader you need to use a different connection object.

What are you trying to do? The 2 queries are identical, why would you want to use the reader the 2nd time?
Anyway, to answer your question, you have to call command.executereader again since your reader has been closed.

It really looks like you are getting only a single value from a single line, despite the loop. If that is the case, look at ExecuteScalar and don't bother with a datareader at all. It will be faster, and just returns the item in the first field of the first row of whatever query.

As for the main problem, when you closed the datareader you disposed of it (more or less). Therefore, you need to repeat the command.ExecuteReader again to get a new datareader that will be set to the first row.

Quote:

---

Originally Posted by stanav

What are you trying to do? The 2 queries are identical, why would you want to use the reader the 2nd time?
Anyway, to answer your question, you have to call command.executereader again since your reader has been closed.

---

As I mentioned in my post I'm using the same select because I know it returns data in case that was the issue.

How do you close the reader and open it again. When I use this it errors saying "Invalid attempt ti use HasRows when reader is closed".

Code:

---

objDataReader.Close()
        objCommand.ExecuteReader()


        If objDataReader.HasRows Then
            Do While objDataReader.Read()
                gvSourceFolder = objDataReader("ParmString").ToString()
            Loop
        Else
            MsgBox("Failed")
        End If

---

Could you add it to my original code post?

You just do this:

Code:

---

'Dispose the 1st reader
objDataReader.Close()

'Then get a new reader
objDataReader = objCommand.ExecuteReader()
If objDataReader.HasRows Then
            Do While objDataReader.Read()
                gvSourceFolder = objDataReader("ParmString").ToString()
            Loop
        Else
            MsgBox("Failed")
        End If

---

Are you trying to get the last ParmString field of your data in gvSourceFolder??
Then why use a DataReader. You coluld be better off with a ExecuteScalar for this with slight modification in your query.

Quote:

---

Originally Posted by stanav

You just do this:

Code:

---

'Dispose the 1st reader
objDataReader.Close()

'Then get a new reader
objDataReader = objCommand.ExecuteReader()
If objDataReader.HasRows Then
            Do While objDataReader.Read()
                gvSourceFolder = objDataReader("ParmString").ToString()
            Loop
        Else
            MsgBox("Failed")
        End If

---

---

Thank you very much! Geez I'm overwhelmed. This is like a whole new animal to me.

Quote:

---

Originally Posted by Pradeep1210

Are you trying to get the last ParmString field of your data in gvSourceFolder??
Then why use a DataReader. You coluld be better off with a ExecuteScalar for this with slight modification in your query.

---

Could you change my code to what you are mentioning? Then I'll go read up on it to learn more about it.

Quote:

---

Originally Posted by TysonLPrice

Could you change my code to what you are mentioning? Then I'll go read up on it to learn more about it.

---

This is how you do it.

The code below will get the first record instead of the last one unless you change your query. You should modify your query so that it selects a single row (the one you want), or sort it on some field (sql ORDER BY) so that the record you want becomes the first row to be returned in the resultset.

vb.net Code:

Private Sub TestIt()
 
    Dim sConnection As String = "Server=devsql.corp.compmgt.com\image1;Database=eDocs;Trusted_Connection=Yes"
    Dim objCommand As New SqlCommand
 
    objCommand.CommandText = "Select ParmString from refParm where ParmDesc = 'Citrix Image Import " & _
                            Me.cboDocClass.Text & "'"
 
    objCommand.Connection = New SqlConnection(sConnection)
    objCommand.Connection.Open()
 
    gvSourceFolder = objCommand.ExecuteScalar()
    If String.IsNullOrEmpty(gvSourceFolder) Then MsgBox("Failed")
 
 
    objCommand.CommandText = "Select ParmString from refParm where ParmDesc = 'Citrix Image Import " & _
                            Me.cboDocClass.Text & "'"
 
    gvSourceFolder = objCommand.ExecuteScalar()
    If String.IsNullOrEmpty(gvSourceFolder) Then MsgBox("Failed")
 
End Sub

Pradeep :)

@Pradeep: Seems like you have option Strict turned off ;)

Quote:

---

Originally Posted by Pradeep1210

This is how you do it.

The code below will get the first record instead of the last one unless you change your query. You should modify your query so that it selects a single row (the one you want), or sort it on some field (sql ORDER BY) so that the record you want becomes the first row to be returned in the resultset.

vb.net Code:

Private Sub TestIt()
 
    Dim sConnection As String = "Server=devsql.corp.compmgt.com\image1;Database=eDocs;Trusted_Connection=Yes"
    Dim objCommand As New SqlCommand
 
    objCommand.CommandText = "Select ParmString from refParm where ParmDesc = 'Citrix Image Import " & _
                            Me.cboDocClass.Text & "'"
 
    objCommand.Connection = New SqlConnection(sConnection)
    objCommand.Connection.Open()
 
    gvSourceFolder = objCommand.ExecuteScalar()
    If String.IsNullOrEmpty(gvSourceFolder) Then MsgBox("Failed")
 
 
    objCommand.CommandText = "Select ParmString from refParm where ParmDesc = 'Citrix Image Import " & _
                            Me.cboDocClass.Text & "'"
 
    gvSourceFolder = objCommand.ExecuteScalar()
    If String.IsNullOrEmpty(gvSourceFolder) Then MsgBox("Failed")
 
End Sub

Pradeep :)

---

Thanks for taking the time to post that. I'll try it out.