Solved

SQLDataReader

Posted on 2004-10-14
9
783 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.NET 2008 Populate DataModel with DataTable 44 28
Crystal reports vb.net 2 41
write xml in vb.net 2 24
location of a form 2 14
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

810 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