[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Reset SQLDataReader

Posted on 2006-04-30
4
Medium Priority
?
3,811 Views
Last Modified: 2008-01-09
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
Comment
Question by:dba123
  • 2
4 Comments
 
LVL 6

Expert Comment

by:the_paab
ID: 16573994
reader.close
0
 
LVL 1

Author Comment

by:dba123
ID: 16574231
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
 
LVL 1

Author Comment

by:dba123
ID: 16574289
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
 
LVL 23

Accepted Solution

by:
naveenkohli earned 2000 total points
ID: 16574801

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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
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…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month19 days, 6 hours left to enroll

834 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