DataReader EOF Help in VB.NET

Posted on 2009-04-24
Last Modified: 2013-11-08
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

Question by:mwmiller78
    LVL 69

    Expert Comment

    by:Éric Moreau
    which problem do you have?
    LVL 4

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
    A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now