Copy a recordset (multiple rows) into an array

I've found two ways of doing this, but neither one is exactly what i
need. I want to be able to refer to the fields by name, but also be
able to store more then one record.

Option 1. dictionary opject. - It seems this would be vbscripts what of
doing an associative array. I could get a value from a row like this.

' i havn't tried it, but it seemed pretty straightforward.
strMyName = objDict.Item("Name")

But what if i had more then one row?


Option 2 - This solves the problem with the dictionary object, but now
i can't specify the name of the field. I have to know what position it
is in.

arrRecordArray = rsA.GetRows
' assuming my "Name" is in the second row, and in the second colomn.
strMyName = arrRecordArray(1,1)



Is there any way to store the recordset so that i can access it by
record number and fieldname?
ie: strMyName = aryRecords(intRecNum)("Name")

McKirahan
Fri May 12 01:12:02 CDT 2006

"Dan" <danncasey@gmail.com> wrote in message
news:1147397587.678730.175970@y43g2000cwc.googlegroups.com...

[snip]

> Is there any way to store the recordset so that i can access it by
> record number and fieldname?
> ie: strMyName = aryRecords(intRecNum)("Name")


Will this help or at least give you an idea? Watch for word-wrap.

This builds an array by (FieldNamePosition,RecordNumber)
and uses a Dictionary to resolve both.

Option Explicit
'*
'* Declare Constants
'*
Const cVBS = "whose.vbs"
Const cMDB = "who.mdb"
Const cDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
'*
'* Declare Globals
'*
Dim arrRST()
ReDim arrRST(3,0)
arrRST(1,0) = "NameID"
arrRST(2,0) = "LastName"
arrRST(3,0) = "FirstName"
Dim intRS1
Dim intRS2
'*
'* Lookup()
'*
Dim objDIC
Set objDIC = CreateObject("Scripting.Dictionary")
'*
Call Build()
MsgBox "Lookup('FirstName',7) = " &
Lookup("FirstName",7),vbInformation,cVBS
MsgBox "Lookup('LastName',3) = " &
Lookup("LastName",3),vbInformation,cVBS
'*
Set objDIC = Nothing

Sub Build()
'*
'* Declare Variables
'*
Dim strSQL
strSQL = "SELECT"
For intRS1 = 1 To UBound(arrRST,1)
objDIC.Add arrRST(intRS1,0), intRS1
strSQL = strSQL & " " & arrRST(intRS1,0)
If intRS1 < UBound(arrRST,1) Then strSQL = strSQL & ","
Next
intRS1 = 0
intRS2 = 0
strSQL = strSQL & " FROM [who] ORDER BY NameID"
'*
'* Declare Objects
'*
Dim objADO
Set objADO = CreateObject("ADODB.Connection")
objADO.Open cDSN & cMDB
Dim objRST
Set objRST = objADO.Execute(strSQL)
'*
'* Read Table and Build Array
'*
Do While Not objRST.EOF
intRS2 = intRS2 + 1
ReDim Preserve arrRST(UBound(arrRST,1),intRS2)
For intRS1 = 1 To UBound(arrRST,1)
arrRST(intRS1,intRS2) = objRST(arrRST(intRS1,0)).Value
Next
objDIC.Add objRST(arrRST(1,0)).Value, intRS2
objRST.MoveNext
Loop
'*
'* Destroy Objects
'*
Set objRST = Nothing
objADO.Close
Set objADO = Nothing
End Sub

Function Lookup(fld,rec)
Lookup = ""
'*
If Not objDIC.Exists(fld) _
Or Not objDIC.Exists(rec) Then Exit Function
'*
Dim intFLD
intFLD = objDIC.Item(fld)
Dim intREC
intREC = objDIC.Item(rec)
'*
Lookup = arrRST(intFLD,intREC)
End Function


In my example, the "NameID" is an AutoNumber field (MS-Access)
and this presumes that it is used for RecordNumber rather than the
physical (sequential) record number. Is this what you want?

The database table's FieldNames are declared once in the array and
referenced elsewhere by their array position

Alexander
Fri May 12 04:11:08 CDT 2006

Dan schrieb:

> I've found two ways of doing this, but neither one is exactly what i
> need. I want to be able to refer to the fields by name, but also be
> able to store more then one record.
>
> Option 1. dictionary opject. - It seems this would be vbscripts what of
> doing an associative array. I could get a value from a row like this.
>
> ' i havn't tried it, but it seemed pretty straightforward.
> strMyName = objDict.Item("Name")
>
> But what if i had more then one row?
>
>
> Option 2 - This solves the problem with the dictionary object, but now
> i can't specify the name of the field. I have to know what position it
> is in.
>
> arrRecordArray = rsA.GetRows
> ' assuming my "Name" is in the second row, and in the second colomn.
> strMyName = arrRecordArray(1,1)
>
>
>
> Is there any way to store the recordset so that i can access it by
> record number and fieldname?
> ie: strMyName = aryRecords(intRecNum)("Name")



This functionality is already built-in into an ADODB-recordset.
You have a 'Move'-method to move to a position relative to the
first record (start-parameter = adBookmarkFirst) and you have the
Fields-collection to access fields by name.
All you have to to do, is to ensure the RS supports backward-movements
and bookmarks (i.e. CursorType = adOpenStatic or adKeyset)


Function GetFieldValue(intRow, strFieldName)
m_rs.Move intRow, 1' 1 == adBookmarkFirst
GetFieldValue = m_rs.Fields(strFieldName)
End Function

Also, if you use GetRows to retrieve a 2D-Array, you simple need to
map the Field-Names to ordinal-positional values, or use named constants
e.g.:

DB-SCHEMA
T_PERSON:
ID (0)
NAME (1)
AGE (2)


VBS:
Const ID = 0
Const NAME = 1
Const AGE = 2

'..create rs
'..fill arr using GetRows

aryRecords = rs.GetRows()
strMyName = aryRecords(intRecNum, NAME)


MfG,
Alex

ekkehard
Fri May 12 07:15:59 CDT 2006

Alexander Mueller wrote:

> Dan schrieb:
>
[...]
>> Is there any way to store the recordset so that i can access it by
>> record number and fieldname?
>> ie: strMyName =3D aryRecords(intRecNum)("Name")
[...]

> This functionality is already built-in into an ADODB-recordset.
[...]

> Also, if you use GetRows to retrieve a 2D-Array, you simple need to
> map the Field-Names to ordinal-positional values, or use named constan=
ts
> e.g.:
>
> DB-SCHEMA
> T_PERSON:
> ID (0)
> NAME (1)
> AGE (2)
>
>
> VBS:
> Const ID =3D 0
> Const NAME =3D 1
> Const AGE =3D 2
>
> '..create rs
> '..fill arr using GetRows
>
> aryRecords =3D rs.GetRows()
> strMyName =3D aryRecords(intRecNum, NAME)
[...]

Just one remark to this very useful information:

From the (german) ADO docs:

Verwenden Sie die GetRows-Methode, um Datens=E4tze aus einem Recordset=
in ein
zweidimensionales Array zu kopieren. Der erste Index kennzeichnet das =
Feld
und der zweite die Datensatznummer. Die Variable Array erh=E4lt automa=
tisch
die richtige Gr=F6=DFe, wenn die GetRows-Methode die Daten zur=FCckgib=
t.

The first index is for the field, the second for the recordnumber.

strMyName =3D aryRecords( NAME, intRecNum )

Alexander
Fri May 12 10:22:56 CDT 2006

ekkehard.horner schrieb:

> Alexander Mueller wrote:
>
> > Dan schrieb:
> >
> [...]
> >> Is there any way to store the recordset so that i can access it by
> >> record number and fieldname?
> >> ie: strMyName = aryRecords(intRecNum)("Name")
> [...]
>
> > if you use GetRows to retrieve a 2D-Array, you simple need to
> > map the Field-Names to ordinal-positional values, or use named constants
> > e.g.:

> > aryRecords = rs.GetRows()
> > strMyName = aryRecords(intRecNum, NAME)
> [...]
>
> Just one remark to this very useful information:
>
> From the (german) ADO docs:
>
> Verwenden Sie die GetRows-Methode, um Datensätze aus einem Recordset
> in ein
> zweidimensionales Array zu kopieren. Der erste Index kennzeichnet das
> Feld
> und der zweite die Datensatznummer. Die Variable Array erhält automatisch
> die richtige Größe, wenn die GetRows-Methode die Daten zurückgibt.
>
> The first index is for the field, the second for the recordnumber.
>
> strMyName = aryRecords( NAME, intRecNum )
>

Hi Ekkehard,

Danke für die Aufklärung.
Hatte es ehrlich gesagt nicht getestet, bei 50:50-Chancen
liegt man ja manchmal richtig ;-)

MfG,
Alex

Dan
Fri May 12 12:13:57 CDT 2006

The only problem I have is that all of these methods require you to
know the name and location of the field. I might not know the specific
order that my sql is in.

IE:

Const FLD_FNAME = 0
Const FLD_LNAME = 1

' Assuming the sql below, I should be fine.
SQL = "SELECT fname,lname FROM....

' However, it were like this then I have a problem
SQL = "SELECT lname,fname FROM....

Response.Write("Last name is " & FLD_LNAME

You see what i mean?

' ------------------------------------
I think trying to seperate this from my main code is going to be more
messy then just redoing my work over and over. I think i'll just open
and close the record set from within my main code rather trying to have
a function do it.

ekkehard
Fri May 12 13:12:17 CDT 2006

Dan wrote:

> The only problem I have is that all of these methods require you to
> know the name and location of the field. I might not know the specific
> order that my sql is in.
[...]
> I think trying to seperate this from my main code is going to be more
> messy then just redoing my work over and over. I think i'll just open
> and close the record set from within my main code rather trying to have
> a function do it.

No need to fall back to 'redoing my work over and over'. Using field names
could be as easy as:

Dim sDSN : sDSN = "nordwind"
Dim sSQL : sSQL = "SELECT TOP 5 [Artikel-Nr], [Artikelname] FROM Artikel"

Dim oCN, dicFNaNu, oRS, nCnt, oFld, aRows, sVal

Set oCN = CreateObject( "ADODB.Connection" )
Set dicFNaNu = CreateObject( "Scripting.Dictionary" )
oCN.Open "DSN=" + sDSN
Set oRS = oCN.Execute( sSQL )
aRows = oRS.GetRows()
nCnt = 0
For Each oFld In oRS.Fields
dicFNaNu.Add oFld.Name, nCnt
nCnt = nCnt + 1
Next

For nCnt = 2 To 3
WScript.Echo aRows( dicFNaNu( "Artikelname" ) , nCnt )
WScript.Echo aRows( dicFNaNu( "Artikel-Nr" ) , nCnt )
Next

Dan
Fri May 12 16:53:20 CDT 2006

Basically what im trying to do is call another function to the database
activity. So my code would look like this .


aryResults = getRowsFromDB("Select name from ...")

response.write aryResults[0]("Name")
' which would print my name


i think ill just use ado