SqlDataReader pass row to subroutine

Posted on 2012-08-29
Last Modified: 2012-09-06
Hello experts,

I'm using the following to grab a SINGLE record from a remote database (SQL SERVER 2000) based on a primary key.  I am trying to pass that datarow to a subroutine that will combine some columns from that row and some locally generated information into a local database.  The INSERT command is in the subroutine 'InsertToCue'   "r" is a datarow from a local datatable that will be combined with the row from the remote DB.

How do I pass the entire row obtained from the sqldatareader to a sub and then reference the wanted columns by their names like "theRow("LastName")" instead of by index/column number?

Here is partial code:

                                'Use remote DB
                                myCommand = New SqlCommand(sql, Connection)
                                Dim sqlReader As SqlDataReader = myCommand.ExecuteReader()
                                Dim theRow() As DataRow = Nothing
                                While sqlReader.Read()
                                    'For j = 0 To 20    'This was testing to make sure right row matched r
                                    '    Debug.Print(sqlReader.Item(j))
                                    InsertToCue(r, theRow(0))
                                End While

Thanks for your thoughts
Question by:zipnotic
    LVL 16

    Expert Comment

    Make a collection of the field names and do something like

    For j = 0 To 20  
        Debug.Print(sqlReader.Item(  strNameCollection(j)  ))

    (it's a few years since I visited VB.NET, but I think that's how you could do it!



    Accepted Solution

    I ended up using a datatable and refering to row(0).  It probably ended up being only a couple extra lines of code anyways.  It seems difficult to refer to the whole row when using sqldatareader.  The columns can only be referenced by index integer and I couldn't figure out how to pass the entire row to a subroutine.

    Author Closing Comment

    Used a different method.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    734 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

    23 Experts available now in Live!

    Get 1:1 Help Now