Solved

SQLDataReader

Posted on 2004-10-14
9
790 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
[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
  • 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
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!

 

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
 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

762 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