Below we will attempt to access data from a database without knowing the column names. Clearly the best way to utilize data in your database is to keep track of your schema. Schema is the layout of data in your database. The concept is well beyond the scope of this web site, but it is worth mentioning. Most good resources on SQL will also be good resources on database management. Better database schema leads to better ASP code.
Get Started:
Below is the script for Lesson 18.
<%@LANGUAGE="JavaScript"%><!-- METADATA TYPE="typelib" FILE="C:/Program Files/Common Files/System/ado/msado15.dll" --><HTML><BODY><%var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="; myConnect += Server.MapPath("//")myConnect += "//GlobalScripts//htmlColor.mdb;";var ConnectObj = Server.CreateObject("ADODB.Connection");var RS = Server.CreateObject("ADODB.Recordset");var sql="SELECT * FROM colorChart;";ConnectObj.Open (myConnect);RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockReadOnly,adCmdText);var recordCount = RS.Fields.Count;var x = 0;var getFieldNames = false;Response.Write("<TABLE BORDER=/"1/" CELLSPACING=/"0/">/r");while (!RS.EOF){if (x >= recordCount){x = 0}Response.Write("<TR>");if (!getFieldNames){while (x <= recordCount-1){Response.Write("<TH>" + RS.Fields(x).Name + "</TH>");x++;}getFieldNames = true;x = 0;Response.Write("</TR>/r<TR>")}while (x <= recordCount-1){Response.Write("<TD>" + RS.Fields(x).Value + "</TD>");x++;}Response.Write("</TR>/r");RS.MoveNext();}Response.Write("</TABLE>/r");RS.Close();ConnectObj.Close();RS = null;ConnectObj = null;%></BODY></HTML>Click Here to run the script in a new window.
I don't think this needs much explaining. The
Another Way:
A potentially more elegant way to accomplish this same goal is to use the ADO Method GetRows. It returns a multi-dimensional array containing the Recordset data. WAIT! Aren't JavaScript Arrays lexical (and flat)? Yes. We can emulate multi-dimensional arrays, but in reality they are flat. So it's a no-go on the GetRows... unless we do something really creative.
<%@LANGUAGE="JavaScript"%><!-- METADATA TYPE="typelib" FILE="C:/Program Files/Common Files/System/ado/msado15.dll" --><HTML><BODY><%var myConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="; myConnect += Server.MapPath("//")myConnect += "//GlobalScripts//htmlColor.mdb;";var ConnectObj = Server.CreateObject("ADODB.Connection");var RS = Server.CreateObject("ADODB.Recordset");var sql="SELECT * FROM colorChart;";ConnectObj.Open (myConnect);RS.Open(sql,ConnectObj,adOpenForwardOnly,adLockReadOnly,adCmdText);var myArray = RS.GetRows().toArray();Response.Write("Let's see the results of myArray as JavaScript");Response.Write(" sees them (which is flat).<BR>/r");Response.Write(myArray + "<BR><BR>/r")RS.MoveFirst();var myVBArray = new VBArray(RS.GetRows())Response.Write("We can use the <I>new VBArray</I> constructor and the ")Response.Write("<I>getItem( )</I> method. For example: myVBArray.getItem(1,1) ")Response.Write("returns " + myVBArray.getItem(1,1) + "<BR><BR>/r")Response.Write("Now lets make something useful.<BR>/r")Response.Write("<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>")Response.Write("/r<TR>")for (var x=0; x<=myArray.length-1; x++){Response.Write("<TD>" + myArray[x] + "</TD>")if ((x+1)%RS.Fields.Count==0){Response.Write("</TR>/r<TR>")}}Response.Write("</TR>/r")Response.Write("</TABLE>")RS.Close();RS = null;ConnectObj.Close();ConnectObj = null;%></BODY></HTML>