Solved

SQLDataReader

Posted on 2004-10-14
9
808 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
Industry Leaders: 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

630 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