Solved

SqlDataReader Problem

Posted on 2006-07-21
6
460 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 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now