How to save a retrieved column from a datareader into an array?

Question

I would like to retrieve a single column of about 800 rows in a table and save these values in an array so as to reference them later on. How can I do it? I tried:

    con = New OleDb.OleDbConnection("provider=SQLOLEDB;data source=PC;initial catalog=DB1;integrated security=SSPI")  

    cmd = New OleDbCommand("select col1 from table1", con)

    con.Open()

    r = cmd.ExecuteReader

    While r.Read

        prev_ob(i) = r.Item(0)(0)

        i = i + 1

    End While

    For i = 0 To UBound(prev_ob)

        Console.WriteLine(prev_ob(i))

    Next`

And it didn't work. The col1 is of type Int64 and so is the array prev_ob(). Please let me know how I can save it to an array.

callisto · Accepted Answer · 2011-03-17 08:56:13Z

3

What is the error your are getting? More information will help you in getting an answer sooner.

Try this:

Dim int64list As New List(Of Int64)

cmd = New OleDbCommand("select col1 from table1", con)

    con.Open()
    r = cmd.ExecuteReader

    While r.Read

        int64list.Add(Convert.ToInt64(r.Item(0))

    End While

For Each obj In int64list
     Console.WriteLine(obj)
Next obj

PS I wrote a sample that works in C# and tried translating to VB.net.

answered Mar 17, 2011 at 8:56

callisto

5,11311 gold badges55 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

CodingInCircles Over a year ago

First, nice user name! :) and next, when I tried to execute the code you had posted, I got the following error: Object cannot be cast from DBNull to other types. So I tried: prev_ob.Add(r.Item("col1")) and then I got: Conversion from type 'DBNull' to type 'Long' is not valid. What's wrong? :(

CodingInCircles Over a year ago

It worked! :) One of the columns was null. I modified my query and it worked. Thanks! :)

CodingInCircles Over a year ago

Is there any way where I can use the individual values of this list for calculation and possibly, in an SQL query?

callisto Over a year ago

"Is there any way where I can use the individual values..." Please elaborate on this. It should be simple to use these values in the list for any calculation.

CodingInCircles Over a year ago

Never mind. I got it to work. :) Used the method you'd suggested and could use it. Thanks! :)

Collectives™ on Stack Overflow

How to save a retrieved column from a datareader into an array?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related