[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SqlDataReader pass row to subroutine

Posted on 2012-08-29
Medium Priority
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
  • 2
LVL 16

Expert Comment

ID: 38348712
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

zipnotic earned 0 total points
ID: 38356955
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

ID: 38371445
Used a different method.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

868 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