Solved

How do you fill an array dynamically using a datareader

Posted on 2004-08-30
8
635 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
8 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
Independent Software Vendors: 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 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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