Download
Academy
Current location: Downcodes.com -> Academy -> Programming -> ASP tutorial -> Juggling data from recordsets, to arrays, to tables
Recommend
HOT TOP10
Juggling data from recordsets, to arrays, to tables
Date: 2008-1-4 Author: Hit: View:[Large font Middle font Small font]

During the course of gathering and rendering data it helps to be able to work with the data in different containers. Sometimes you need a recordset, sometimes you need an array, sometimes you want to present the data as a table. This article shows how to move the data
through this sequence.

The objective of this article is to show how data can be moved. The article is not trying to present the most efficient means of accomplishing these tasks. Rather the emphasis is on using each in turn.

So here is what will happen:

    The article will create a subroutine which will be called to:
        - connect to a database;
        - get the field names from a specified table;
        - move the field names into an array;
        - collect the field values from a specified record of the table;
        - move the field values into a second array;
        - save both arrays into session variables;
        - recall the arrays from the session variables and finally;
        - render the recalled values into a table for the user.

So, let's begin by creating the subroutine. There will be four parameters required as shown in the code block below.


<%

Sub MakeArray(conndb,tablename,recordID,key)


Dim the array variables and open a connection to the database. The SQL statement is written to accept the parameters for a specifed table, field name and record.


dim ARR(), ARRrs()

    set rs = Server.CreateObject("ADODB.Recordset")
    SQL = "SELECT * FROM " & tablename & _
" WHERE " & tablename & ".[" & key & "] = " & recordID
    rs.Open SQL, conndb, 3


Now that we have a recordset from the database, we need to collect the field names from the specified table. As we collect the names we will add them to an array with the following FOR-NEXT loop. We finish by closing the recordset.

Note in the loop that the array is redimensioned on each pass. Since we don't know in advance how many field names exist in the table, this allows us to accept any number of fields. Also, note that the word "preserve" is included. When an array is redimensioned, the data is lost unless explicitly saved with "preserve"


For I = 0 to rs.Fields.Count - 1
    redim preserve ARR(I)
    ARR(I) = rs.Fields(I).Name
next

    rs.close


Now let's get the field values for the specified record. We'll use the same technique of retrieving data from a recordset and placing it into an array. Note that for the limits of this loop we will use the lower an upper bounds of the first array.

We'll finish by closing the recordset and the database connection, as we no longer need it.


For I = LBOUND(ARR) to UBOUND(ARR)
    redim preserve ARRrs(I)
    SQL= "SELECT " & tablename & _
".[" & ARR(I) & "] FROM " & tablename & _
" WHERE " & tablename & ".[" & key & "] = " & recordID
    rs.Open SQL, conndb, 3
    ARRrs(I)=rs(ARR(i))

    rs.close
next

    set rs=nothing


Since we now have the information in arrays, we can do store that data into session variables which will allow us to retrieve it anytime we want so long as the session is active.


session("FieldName")= ARR
session("FieldValue")= ARRrs



To illustrate the storage retrieval ability we'll create two new array values and retrieve the data into them.


Dim ARRx,ARRy
ARRx = session("FieldName")
ARRy = session("FieldValue")


Now let's write out the data from the new arrays into a new table for the user and conclude our subroutine.


For I = LBOUND(ARRx) to UBOUND(ARRx)
Response.Write ("<table border=0 cellpadding=2 cellspacing=2 >")
Response.Write ("<tr>" )
Response.Write ("<td bgcolor=#C0C0C0 width='125'><B>" & _
ARRx(I) & "</B></td>" )
Response.Write ("<td bgcolor=#FFFFCC width='200'><B>" & _
ARRy(I) & "</B></td>" )
Response.Write ("</tr>")
Response.Write ("</table>")
next

End Sub


To use the subroutine, define the database connection, table name, key field for record selection and the specific record desired.

In this example, we will set the parameters to get the data from record #10260 of the orderID field in the orders table with a connection to the Northwind database.


conndb = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= " & _
Server.MapPath("Northwind.mdb")
tablename="orders"
key= "orderid"
recordID=10260%>


We'll need a little heading for our report so...


<CENTER>
<h2><FONT color="red">Working with Arrays</FONT></h2>
<HR color="silver">
</CENTER>


And all that's left to do is call the subroutine and see the results.


<%
Call MakeArray(conndb,tablename, recordID,key )
%>

(From: aspkey)
Relative article:
Relative software: