Solved

SqlDataReader Problem

Posted on 2006-07-21
6
475 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 50 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
Independent Software Vendors: 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!

 
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 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 50 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

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.

Question has a verified solution.

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

Suggested Solutions

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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