Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SqlDataReader Problem

Posted on 2006-07-21
6
Medium Priority
?
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

604 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