Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do you fill an array dynamically using a datareader

Posted on 2004-08-30
8
Medium Priority
?
638 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

715 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