Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do you fill an array dynamically using a datareader

Posted on 2004-08-30
8
Medium Priority
?
640 Views
Last Modified: 2008-01-09
Hi,

I have managed to dynamically dimension an array.

nCountOfRecords =countIDs  
ReDim MyArray(nCountOfRecords) 'This dynamically sets the size of my array

What I want to do is pull 1 column of Integer values from 1 sql database table into my array and then be able to access any value in my array.

I would prefer to loop throught the datasource with a datareader if this is the fastest.

Suppose I want to access the following.
eg MyArray(1),MyArray(7),MyArray(15)

Thanks in advance

PG
0
Comment
Question by:pgilfeather
  • 2
  • 2
  • 2
6 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 11931163
I wouldn't use an dynamic array but an arraylist (much better)

try something like this,

        Dim s As New ArrayList
        Dim mySelectQuery As String = "SELECT yourcolumn FROM yourtable"
        Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, OleDbConnection1)
        OleDbConnection1.Open()
        Dim myReader As OleDb.OleDbDataReader
        myReader = myCommand.ExecuteReader()
        While myReader.Read()
            s.Add(myReader.Item("yourcolumn"))
        End While
        myReader.Close()
        OleDbConnection1.Close()

        MsgBox(s.Item(10))
0
 

Author Comment

by:pgilfeather
ID: 11931178
RonaldBiemans

Thankyou very much

I'll try it right now and let you know

Thanks again

PG
0
 

Author Comment

by:pgilfeather
ID: 11931366
Can this be done using the array for this problem as there is another bit of code that has been written which is based on the array rather than an arraylist.

I have the following code.    

    Dim MyArray() As Integer
          
    Dim countIDs as Integer 'This is set in another section of code(Gets the number of rows in the datasource column)


    Dim cnn2 As String = (ConfigurationSettings.AppSettings("SomeWebSite"))
    Dim objConnection2 as new sqlConnection(cnn2)
    objConnection2.Open()
    Dim objCommand2 as new SqlCommand("procGetIDs", ObjConnection2)
    objCommand2.CommandType = CommandType.StoredProcedure
    objCommand2.Connection = ObjConnection2
    Dim objreader As SqlDataReader = objCommand2.ExecuteReader()

    ReDim MyArray(countIDs)

     Now how do I put all the IDs into the array so that they can be accessed
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 2000 total points
ID: 11931575
Yes it can

ReDim MyArray(countIDs)

Dim mySelectQuery As String = "SELECT yourcolumn FROM yourtable"
        Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, OleDbConnection1)
        OleDbConnection1.Open()
        Dim myReader As OleDb.OleDbDataReader
        Dim x As Integer = 0
        myReader = myCommand.ExecuteReader()
        While myReader.Read()
            MyArray(x) = myReader.Item("yourcolumn")
            x += 1
        End While
        myReader.Close()
        OleDbConnection1.Close()

        MsgBox(s(1))
0
 
LVL 8

Expert Comment

by:wguerram
ID: 11936849
Use a DataAdpater to fill a Dataset and then
use the rows.CopyTo Property to copy the values to an array

Dataset.Tables(0).Rows.CopyTo(MyArray,0)
0
 
LVL 8

Expert Comment

by:wguerram
ID: 11936867
An especific example:      

       Dim MyDataSet As DataSet
        Dim MyAdapter As New Data.OleDb.OleDbDataAdapter
        MyAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT MyColumn FROM MyTable", MyConnection)
        MyAdapter.Fill(MyDataSet)

MyDataSet.Tables(0).Rows.CopyTo(MyArray,0)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

783 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