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

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:

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');

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

<!--#include file="AssociativeArrayClass.asp" -->

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…

Set DbConnection = Server.CreateObject("ADODB.Connection")

Select a Provider for MS Access or MySQL:

dbConnectionString = _
   "driver={Microsoft Access Driver (*.mdb)}; DBQ=" & _
           Server.MapPath("myDatabase.mdb")

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

"driver={MySQL ODBC 3.51 Driver}; Server=; Uid=; Pwd=; Database=;"

Open a database connection and create a SQL Select:

DbConnection.Open dbConnectionString
Dim sql : sql = "SELECT * FROM myTable"

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

Dim Table
Set Table = New AssociativeArray
Table.Fill DbConnection, sql
Set DbConnection = Nothing

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:

Response.Write( _
                Table(0)("myField1") & " , " & _
                Table(0)("myField2") & " , " & _
                Table(0)("myField3") & "<br />")

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

Response.Write( _
                Table(1)("myField1") & " , " & _
                Table(1)("myField2") & " , " & _
                Table(1)("myField3") & "<br />")

it gets the total Records Count

Response.Write("<p>Total Records: " & Table.Count & "</p>")

this enumerates all Records inside the Associative Array:

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>")

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

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>")

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

Max

Leave a Reply