SqlDataReader pass row to subroutine

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
Who is Participating?
zipnoticConnect With a Mentor Author Commented:
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.
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!


zipnoticAuthor Commented:
Used a different method.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.