Solved

SQLDataReader

Posted on 2004-10-14
9
775 Views
Last Modified: 2008-05-05
When I return a datareader from one of my classes I want to assign column values to individual text boxes. What code should I use? See code below:

 Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        Dim drProduct As New admin
        drProduct.ProductID = ddlProduct.SelectedItem.Value

        If drProduct.GetProduct.Read() Then
            Dim dr As SqlDataReader = drProduct.GetProduct()
            ddlCategoryID.SelectedValue = ddlCategory.SelectedItem.Value

             // I want to put in values from individual columns into text boxes but code below does not work?

            txtProductCode.text = dr("ProductCode")
        End If
    End Sub
0
Comment
Question by:odonovanpm
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12308485
When retrieving the values from a DataReader, you need to specify what type of value is being read:

so you code should look like this:

If drProduct.GetProduct.Read() Then
            Dim dr As SqlDataReader = drProduct.GetProduct()
            ddlCategoryID.SelectedValue = ddlCategory.SelectedItem.Value

             // I want to put in values from individual columns into text boxes but code below does not work?

            txtProductCode.text = dr.GetString(0) ' the ORDINAL position of the field
        End If

or:

If drProduct.GetProduct.Read() Then
            Dim dr As SqlDataReader = drProduct.GetProduct()
            ddlCategoryID.SelectedValue = ddlCategory.SelectedItem.Value

             // I want to put in values from individual columns into text boxes but code below does not work?

            txtProductCode.text = dr.GetString("ProductCode") ' the name of the field
        End If


AW
0
 
LVL 18

Expert Comment

by:mdougan
ID: 12308513
Hi odonovanpm,
Well, you first have to "read" a row from the reader... try adding this line before the assignment:

If dr.Read then
   txtProductCode.text = dr("ProductCode")
   dr.Close
End if

If that doesn't do it, I'd have to see what your GetProduct procedure looks like.
Cheers!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12308583
good catch, mdougan.  I totally missed that the asker had not 'read' the Datareader.

AW
0
 

Author Comment

by:odonovanpm
ID: 12308732
drProduct.GetProduct() returns an sqldatareader, I have used it as the datasource for a datagrid and it displays fine so I don't think there is a problem with the class... drProduct.GetProduct.Read() is the same as what you suggested dr.Read()???

For some strange reason it isn't displaying the field, I get an error saying there is no data present but when I look in the table there is??? Very strange.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:mdougan
ID: 12309063
Wait a minute... this code is really confusing to me:

If drProduct.GetProduct.Read() Then
            Dim dr As SqlDataReader = drProduct.GetProduct()


First, what is drProduct?  I was assuming that this was some local class you'd developed, and that the GetProduct was a function that was part of that class...

Since dr is your local data reader and since you're theoretically setting it to a newly created datareader through that assignment, it makes no sense for you to be doing the .Read statement before that.

Here is how you'd normally open a datareader and read the contents:

        Dim oConn As SqlConnection
        Dim oCmd As SqlCommand

            'Get connection and set up for recordset (just a function I use to set conn)
            If Not NETSetConnection(oConn) Then
                Exit Function
            End If

            'Create a Command object.
            oCmd = oConn.CreateCommand
            oCmd.CommandText = "Select ProductID from Products Where Cust = 1"
            oCmd.CommandType = CommandType.Text
            oCmd.CommandTimeout = 600
            oDatareader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

            if oDatareader.Read Then
               txtProduct.Text = oDatareader("ProductID")
            end if
            oDatareader.Close
0
 

Author Comment

by:odonovanpm
ID: 12309171
OK, sorry about that... It is a bit confusing the way I have presented it... drProduct is an instance of a local class I created, GetProduct is a method of the class, this method returns an sqldatareader by using the executereader method as you have in your code... There is no need for the 2nd line I put in "Dim dr as SqlDataReader = drProduct.GetProduct()"...

I just read something about this being an SQL problem, I don't know if this is true or not?
0
 
LVL 18

Accepted Solution

by:
mdougan earned 125 total points
ID: 12309484
No, I think that there is just a problem with how you are working with the returned data reader....  I'll give you two ways that you can do this:

In your class
Public Function GetProduct() As SQLDataReader
Dim oDatareader as SQLDatareader
....code

oDatareader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

GetProduct = oDataReader

End Function

In your regular code
Dim x as New MyClass
Dim dr as SQLDatareader

dr = x.GetProduct
if dr.Read Then
   txtProduct.Text = dr("ProductId")
End If

OR, you can do this:

In your class
Public Function GetProduct(ByRef oDataReader as SQLDataReader)

....code

oDatareader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

GetProduct = oDataReader

End Function

IN your regular code

Dim x as New MyClass
Dim dr as SQLDatareader

x.GetProduct(dr)
if dr.Read Then
   txtProduct.Text = dr("ProductId")
End If
0
 
LVL 18

Expert Comment

by:mdougan
ID: 12309502
Opps, in that second example, your GetProduct function doesn't have to say GetProduct = oDataReader
0
 
LVL 10

Expert Comment

by:Hans Langer
ID: 12310877
Dim dr As SqlDataReader = drProduct.GetProduct()
        If dr.Read() Then
             ddlCategoryID.SelectedValue = ddlCategory.SelectedItem.Value

             // I want to put in values from individual columns into text boxes but code below does not work?

            txtProductCode.text = dr("ProductCode")
        End If


'In your code "dr" has not use its .read property
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

14 Experts available now in Live!

Get 1:1 Help Now