Array, DataReader and count

[VBAHole22]

I am trying to fill an array with two string values pulled with one hit to the db:

 

Like so:

 

Dim strSQL As String = "SELECT D,DPATH FROM pTable WHERE PP = '&'"
       Dim cmdFill As New OracleCommand(strSQL, OracleConn)
       cmdFill.CommandType = CommandType.Text
       cmdFill.CommandText = strSQL
       Dim da As New OracleDataAdapter(cmdFill)
       Dim dr As OracleDataReader
       Dim DList(10000, 1) As String
       Dim j As Integer

       Try
           OracleConn.Open()
           dr = cmdFill.ExecuteReader(CommandBehavior.CloseConnection)
           If dr.HasRows Then

               Do While dr.Read()
                   DList(j, 0) = CType(dr.GetValue(0), String)
                   DList(j, 1) = CType(dr.GetString(1), String)
                   j += 1
               Loop

               For j = 0 To DList.GetUpperBound(0) - 2
                   Me.txtStatus.Text = Me.txtStatus.Text & System.Environment.NewLine & DList(j, 0) & " : " & DList(j, 1)
               Next j
           End If

 

This isn't working for several reasons:

I have to dim my array really high because I don't know how many recs I will get.

I can't use arraylist because it's 2-d

I can't get rec counts because it's a datareader

 

There has to be a more elegant way of doing this?

Could I use a dataadapter to get a rec count and then dim the array with a variable like this

 

dim DList(recCount,1) as String

 

Or isn't there just a way to use fill to dump the results set into an array?

 

Any suggestions on a late Friday afternoon?

[Robby]

First thing is get rid of this

Dim DList(10000, 1) As String

 

do this instead

 

Dim DList(,) As String

 

 

then inside your loop place a counter (x) and do this

 

Redim Preserve DList(1,x)

x += 1

[VBAHole22]

Thanks, that worked.

 

I had to flip flop my array because you can only redim the rightmost parameter.

Still, I don't think 1000 ReDim calls is very efficient either, but as the noob say, if it works, it works.

 

 

 

dr = cmdFill.ExecuteReader(CommandBehavior.CloseConnection)

If dr.HasRows Then

 

Do While dr.Read()

ReDim Preserve DList(1, j)

DList(0, j) = CType(dr.GetValue(0), String)

DList(1, j) = CType(dr.GetString(1), String)

j += 1

Loop

 

For j = 0 To DList.GetUpperBound(1)

 

Me.txtStatus.Text = Me.txtStatus.Text & System.Environment.NewLine & DList(0, j) & " : " & DList(1, j)

 

Next j

End If

[Robby]

If there will be that many redims then do it in chunks of 10 or 100.

Use MOD 100 or something to that affect.