How to populate an ASP Associative Array with records from MySQL / Access database Table

In this example, You can learn how to populate an ASP Associative Array with the records of a database table (MS Access or MySQL); then, how to programmatically manage well structured data without any extra SQL.

Before to start:
For this tutorial, You need to download this MS Access database example or You can create a new one in MySQL executing this SQL:

[sourcecode language=”sql”] CREATE TABLE `myTable` (
`myField1` INT( 11 ) NOT NULL ,
`myField2` VARCHAR( 255 ) NULL DEFAULT ‘test’,
`myField3` VARCHAR( 255 ) NULL DEFAULT ‘test’,
PRIMARY KEY ( `myField1` )
) TYPE = MYISAM ;
INSERT INTO `myTable` (`myField1`, `myField2`, `myField3`)
 VALUES(1, ‘rec1 Text2’, ‘rec1 Text3’);
INSERT INTO `myTable` (`myField1`, `myField2`, `myField3`)
 VALUES(2, ‘rec2 Text2’, ‘rec2 Text3’);
INSERT INTO `myTable` (`myField1`, `myField2`, `myField3`)
 VALUES(3, ‘rec3 Text2’, ‘rec3 Text3’);
[/sourcecode]

Besides, to create an “AssociativeArray” object, You need to include the “ASP Associative Array Class” file in your asp page with an include statement:

[sourcecode language=”vb”] <!–#include file="AssociativeArrayClass.asp" –>
[/sourcecode]

Tutorial:
The target is to grab data from a database Table and save records in an Associative Array, then You need to create a Database connection by ADODB…

[sourcecode language=”vb”]Set DbConnection = Server.CreateObject("ADODB.Connection")[/sourcecode]

Select a Provider for MS Access or MySQL:

[sourcecode language=”vb”]dbConnectionString = _
   "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
           Server.MapPath("myDatabase.mdb")[/sourcecode]

For MySQL Databases You can also use a connection string like the following:

[sourcecode language=”vb”]"driver={MySQL ODBC 3.51 Driver}; Server=; Uid=; Pwd=; Database=;"[/sourcecode]

Open a database connection and create a SQL Select:

[sourcecode language=”vb”]DbConnection.Open dbConnectionString
Dim sql : sql = "SELECT * FROM myTable"[/sourcecode]

Create and Populate the Associative Array with selected Records and close database connection:

[sourcecode language=”vb”]Dim Table
Set Table = New AssociativeArray
Table.Fill DbConnection, sql
Set DbConnection = Nothing[/sourcecode]

Manage Records inside the Associative Array:
Right now, You can manage each record (and its values) inside the Associative Array. For example, You could get the first Record and its Values in each Field:

[sourcecode language=”vb”]Response.Write( _
                Table(0)("myField1") & " , " & _
                Table(0)("myField2") & " , " & _
                Table(0)("myField3") & "<br />")[/sourcecode]

the following code gets the second Record and its Values in each Field:

[sourcecode language=”vb”]Response.Write( _
                Table(1)("myField1") & " , " & _
                Table(1)("myField2") & " , " & _
                Table(1)("myField3") & "<br />")[/sourcecode]

it gets the total Records Count

[sourcecode language=”vb”]Response.Write("<p>Total Records: " & Table.Count & "</p>")[/sourcecode]

this enumerates all Records inside the Associative Array:

[sourcecode language=”vb”]Response.Write("<ul>")
For Each row In Table.Items
   Dim record
   Set record = row.Value ‘get current Record Object
   Response.Write("<li>" & record("myField1") & " : " & _
                           record("myField2") & " : " & _
                           record("myField3") & "</li>")
Next
Response.Write("</ul>")[/sourcecode]

and finally it gets all records by index (zero-based):

[sourcecode language=”vb”]Response.Write("<ul>")
For n = 0 To Table.Count – 1
     Response.Write("<li>" & Table(n)("myField1") & " : " & _
                             Table(n)("myField2") & " : " & _
                             Table(n)("myField3") & "</li>")
Next
Response.Write("</ul>")[/sourcecode]

That’s all,
Get the full example at http://sourceforge.net/projects/asp-assoc-array/

Max