?
Solved

SqlDataReader Problem

Posted on 2006-07-21
6
Medium Priority
?
494 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:Maritimer
  • 3
  • 2
6 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 200 total points
ID: 17154901
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
 

Author Comment

by:Maritimer
ID: 17155805
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
 
LVL 35

Expert Comment

by:YZlat
ID: 17155839
actually, if you are only retrieving one value, it would make sense to use ExecuteScalar instead of ExecuteReader
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

Expert Comment

by:YZlat
ID: 17156274
post the code where you actually fill your datareader. Above you are missing that code
0
 
LVL 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 200 total points
ID: 17158626
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
 

Author Comment

by:Maritimer
ID: 17168362
Thanks for the help. I will close this request soon
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

621 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