SqlDataReader Problem

Not sure if I am doing this correct but I am trying to use SQLDataReader to read some records however I am getting a Null answer. I know the stored Procedure should return a string (it does when I run it on the sql analyze). Could someone give me a hand as I am new at .Net

Sub loadCurrPeriods()
        'Dim db As Database = DatabaseFactory.CreateDatabase()

        'Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand("ProcWaterFallQuerybmscalendarFYFYQTR")
       

        Dim objConn As SqlConnection
        Dim strSQL As String

        Dim dr As SqlDataReader = Nothing

       
           
                ' Create a connection object
                objConn = New SqlConnection(ConfigurationManager.AppSettings.Get("ConnectionStringTEST"))
            Catch ex As Exception
                Response.Write(ex)
                Exit Sub
           
            ' The stored procedure
            strSQL = "ProcWaterFallQuerybmscalendarFYFYQTR"
           
            ' Call ExecuteReader static method of SqlHelper class that returns a SqlDataReader
            ' We pass in database connection string, stored procedure name and value of categoryID parameterand, and a "1" for CategoryID value
           

            dr.Read()
            Me.lbl_FromDate.Text = dr.GetValue(dr.GetOrdinal("fiscal_year"))


    End Sub
MaritimerAsked:
Who is Participating?
 
YZlatConnect With a Mentor Commented:
Which version of .NET Framework are you using? 2005?

Try this:

Sub loadCurrPeriods()
        'Dim db As Database = DatabaseFactory.CreateDatabase()

        'Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand("ProcWaterFallQuerybmscalendarFYFYQTR")
       

        Dim objConn As SqlConnection
        Dim strSQL As String

        Dim dr As SqlDataReader = Nothing

       
           
                ' Create a connection object
                objConn = New SqlConnection(ConfigurationManager.AppSettings.Get("ConnectionStringTEST"))
            Catch ex As Exception
                Response.Write(ex)
                Exit Sub
           
            ' The stored procedure
            strSQL = "ProcWaterFallQuerybmscalendarFYFYQTR"
           
            ' Call ExecuteReader static method of SqlHelper class that returns a SqlDataReader
            ' We pass in database connection string, stored procedure name and value of categoryID parameterand, and a "1" for CategoryID value
           

            While dr.Read()
                  Me.lbl_FromDate.Text = dr("fiscal_year")
          End While


    End Sub
0
 
MaritimerAuthor Commented:
Sorry I am using visual Studio 2005 and the enterprise Library Jan 2006 release.

I tried the code below an got the following error "Object reference not set to an instance of an object."


Dim objConn As SqlConnection
        Dim strSQL As String

        Dim dr As SqlDataReader = Nothing



        ' Create a connection object
        objConn = New SqlConnection(ConfigurationManager.AppSettings.Get("ConnectionString"))
           

        ' The stored procedure
        strSQL = "ProcWaterFallQuerybmscalendarFYFYQTR"

        ' Call ExecuteReader static method of SqlHelper class that returns a SqlDataReader
        ' We pass in database connection string, stored procedure name and value of categoryID parameterand, and a "1" for CategoryID value


        While dr.Read() ------------------------------------------------Object reference not set to an instance of an object.
            Me.lbl_FromDate.Text = dr("fiscal_year")
        End While

0
 
YZlatCommented:
actually, if you are only retrieving one value, it would make sense to use ExecuteScalar instead of ExecuteReader
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
YZlatCommented:
post the code where you actually fill your datareader. Above you are missing that code
0
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
Sub loadCurrPeriods()
        Dim db As Database = DatabaseFactory.CreateDatabase()
        Dim dbCommand As Common.DbCommand = db.GetStoredProcCommand("ProcWaterFallQuerybmscalendarFYFYQTR")
 
        ' Retrieve products from the category 7.
        db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, 1)

        SqlDataReader dr = db.ExecuteReader()
        If(dr.Read())  Then
            Me.lbl_FromDate.Text = dr.GetValue(dr.GetOrdinal("fiscal_year"))
        End If

        '  whatever else you need to do.

    End Sub

Note: You may have to do a IDbDataReader instead of SqlDataReader, but I think that this version is OK if you use the SqlDataReader.

Ben.
0
 
MaritimerAuthor Commented:
Thanks for the help. I will close this request soon
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.

All Courses

From novice to tech pro — start learning today.