[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1457
  • Last Modified:

DataReader EOF Help in VB.NET

I'm trying to cycle through a database table (kind of like EOF in VB6) and then do something for each entry I find. What am I doing wrong?
myConStringReader = "Data Source=ussrm-netsql\netdata;" & _
                    "Initial Catalog=PhysicalInventory;" & _
                    "Integrated Security=SSPI"
        Dim mySelectQueryReader As String = "SELECT * FROM tblDataEntry where WMSQty IS NULL"
        Dim myConnectionReader As New Data.SqlClient.SqlConnection(myConStringReader)
        myCommandReader = New Data.SqlClient.SqlCommand(mySelectQueryReader, myConnectionReader)
        myReader = myCommandReader.ExecuteReader
        While myReader.Read()
            strPN = CType(Session.Item("reader"), Data.SqlClient.SqlDataReader)("PartNumber").ToString
            Dim myConnectionBBB4 As New SqlClient.SqlConnection
            Dim myCommandBBB4 As New SqlClient.SqlCommand
            myConnectionBBB4.ConnectionString = "Data Source=ussrm-WMSSQL;" & _
                             "Initial Catalog=ASCTrac;" & _
                             "Integrated Security=SSPI"
            myCommandBBB4.Connection = myConnectionBBB4
            myCommandBBB4.CommandText = "SELECT SUM(QTYTOTAL) FROM LOCITEMS where ITEMID like '" & strPN & "'"
            wmsqty = myCommandBBB4.ExecuteScalar()
        End While

Open in new window

Mike Miller
Mike Miller
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
which problem do you have?
First. For the inner data construct I would move this out of this procedure entirly, passing in the value you want to search for to a function.
I would also create a DataSet of this second data request at the top, or ouside of the procedure so that you can loop through as many times as you want without recreating the connection string everythime.

Within the DataReader Loop I would do something like ....
While myReader.Read()
    DIM RetVal As String = FindItemFunction(myReader.Item("PartNumber").ToString)
    ......<Do what ever else with it now>
End While

Create Function FindItemFunction(Byval PartNumb As String) As String
FOR EACH DR AS DataRow in MyDataSet(0)
   If PartNumb = DR.items("ITEMID").ToString Then
          Return DR.items("ITEMID").ToString
   Return ""
End Function
There are many ways of doing this, it all depends on what you want to do and need to do.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now