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

Reset SQLDataReader

How can I reset my reader after it's done being used in order to use it for the next sqlcommand

        Dim reader As SqlDataReader = cmdSql.ExecuteReader()

        Dim hp_rowcount As Integer
        Dim wp_rowcount As Integer

        hp_rowcount = 0
        wp_rowcount = 0

        If reader.HasRows() Then  ' test to see if there is any data in the reader
            reader.Read()
            hp_rowcount = Convert.ToInt16(reader("hpcount"))
        End If

'RESET READER HERE
0
dba123
Asked:
dba123
  • 2
1 Solution
 
the_paabCommented:
reader.close
0
 
dba123Author Commented:
here's the problem, I need to use the reader again but it errors out saying it's closed on the second attempt to check the reader's rows when trying to run my 2nd stored proc:

        Dim cmdSql As New SqlClient.SqlCommand("mismatched_homephone", objConn)
        cmdSql.CommandType = CommandType.StoredProcedure
        cmdSql.CommandTimeout = 1000

        Dim reader As SqlDataReader = cmdSql.ExecuteReader()

        Dim hp_rowcount As Integer
        Dim wp_rowcount As Integer

        hp_rowcount = 0
        wp_rowcount = 0

        If reader.HasRows() Then  ' test to see if there is any data in the reader
            reader.Read()
            hp_rowcount = Convert.ToInt16(reader("hpcount"))
        End If
        reader.Close()

        Dim cmdSql2 As New SqlClient.SqlCommand("mismatched_workphone", objConn)
        cmdSql2.CommandType = CommandType.StoredProcedure
        cmdSql2.CommandTimeout = 1000

        If reader.HasRows() Then  ' test to see if there is any data in the reader
            reader.Read()
            wp_rowcount = Convert.ToInt16(reader("wpcount"))
        End If
        reader.Close()
0
 
dba123Author Commented:
I'll just create a reader2 or something.  I'm not sure if this it the only and best way to do it but that's the only solution I see at this point.  Not sure what best practices say about doing it the way I'm doing it..any thoughts?
0
 
naveenkohliCommented:

DataReaders are readnly, forward only providers. Once you have read the records and closed it, they are done forever. You can't go bak or reset it. You have to create a new one. If you need mechamism to go back and forth then you should be using DataAdapter and DataSet model.
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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