Solved

How do you fill an array dynamically using a datareader

Posted on 2004-08-30
8
614 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
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now