Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

SQLDataReader

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
odonovanpm
Asked:
odonovanpm
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Arthur_WoodCommented:
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
 
mdouganCommented:
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
 
Arthur_WoodCommented:
good catch, mdougan.  I totally missed that the asker had not 'read' the Datareader.

AW
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
odonovanpmAuthor Commented:
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
 
mdouganCommented:
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
 
odonovanpmAuthor Commented:
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
 
mdouganCommented:
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
 
mdouganCommented:
Opps, in that second example, your GetProduct function doesn't have to say GetProduct = oDataReader
0
 
Hans LangerCommented:
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

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!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now