Read each row in a single column in a DataReader

Question

I use while(dr.Read()){...}. But I have a problem with reading data. I have a table like this:

Column1

value1
value2
value3

I want to get Value1, Value2 and Value3

I did this like so:

Using myDataReader As OracleDataReader = myCommand.ExecuteReader
    While (myDataReader.Read())

        Value1= myDataReader.GetValue(0).ToString().Trim()
        Value2 = myDataReader.GetValue(1).ToString().Trim()
        Value3 = myDataReader.GetValue(2).ToString().Trim()

    End While
End Using

I can retrieve Value1, however I get the following error when trying to retrieve Value2 and Value3:

Invalid column index specified

There is one column (at index 0). You could use a `List(Of String)` to store the data and Add the value returned in each iteration of the while loop. — Andrew Morton, Mar 01 '17 at 11:03
There are many examples which you could find, e.g. [how to add database query results to an array in vb.net](http://stackoverflow.com/a/14293455/1115360). — Andrew Morton, Mar 01 '17 at 11:23
.Read() returns one row at a time. GetValue(0) gets the value from the first _column_ in that row. GetValue(1) gets the value from the second column, which in your case doesn't exist. You need to create a list or array, and on each run of .Read() (which will be 3 times, if you get 3 results, but it doesn't matter - it can read millions of rows without any change to the code) you add the result of GetValue(0) to the end of your list. — ADyson, Mar 01 '17 at 11:38
@ADyson the problem is that I have many properties, and each property I need initialize with the value from the database — Emma W., Mar 01 '17 at 12:14
`Dim dt As New DataTable`....`dt.Load(myCommand.ExecuteReader)`. I haven't tested but this should load the data into a `DataTable` You can then reference each row in the `DataTable` like so, `Value1 = dt.Rows(0).Item(0).ToString().Trim()`...`Value2 = dt.Rows(1).Item(0).ToString().Trim()`. There are probably more efficient ways of doing this but it may get you back on track. — Bugs, Mar 01 '17 at 13:06
@EmmaW. you could add a counter to your loop, so you know what row you're on and can populate the property accordingly. Bugs' suggestion is also sensible if you have a smallish number of properties. Sounds like your in-code object model doesn't really match your database though (vertical DB record structure vs horizontal object structure), which could be a sign of a conceptual design problem. — ADyson, Mar 01 '17 at 13:13
@EmmaW. not a problem. Posted comment as an answer for you to accept. — Bugs, Mar 02 '17 at 10:07
@Bugs I've got one more question. if I have two or more queries in the same command. e.g. `Using myCommand As New OracleCommand( select value from table where between 1 and 5;" & "select value from table where id between 5 and 6", DBSettings.GetConnection())` Can I get value throuth DataTable from second request? Is it possible, that my value get the rows from the second request? — Emma W., Mar 02 '17 at 11:33
I'm not sure what exactly you are trying to do however I think the answer would lie in creating a proper `SELECT` statement to retrieve all the values you need. I don't think you can do what you are asking. You could go down the line of loading the first command into `dt1` and then loading the second command into `dt2` and then calling `dt1.Merge(dt2)`. However I don't think that would give you what you are after exactly. Also I don't mean to sound disrespectful (and I really don't!) But the norm for SO is to create a new question not to discuss it within the comments of an existing question). — Bugs, Mar 02 '17 at 11:37

score 0 · Accepted Answer · edited May 23 '17 at 12:25

Instead of using and OracleDataReader you should consider using a DataTable to load the data into. To do this use the following code:

Dim dt As New DataTable
dt.Load(myCommand.ExecuteReader)

The DataTable will now contain a collection of DataRows. You can use these to assign the values to your variables like so:

Value1 = dt.Rows(0).Item(0).ToString().Trim()
Value2 = dt.Rows(1).Item(0).ToString().Trim()
Value3 = dt.Rows(2).Item(0).ToString().Trim()

This may fix your initial problem but it may lead to further problems. As suggested by ADyson in his comment:

@EmmaW. you could add a counter to your loop, so you know what row you're on and can populate the property accordingly. Bugs' suggestion is also sensible if you have a smallish number of properties. Sounds like your in-code object model doesn't really match your database though (vertical DB record structure vs horizontal object structure), which could be a sign of a conceptual design problem

Read each row in a single column in a DataReader

1 Answers1