Mo3s has a nice example - but opens up some scary possibilities with things like sql injection (passing certain strings through form field or query string values which can wreak havoc on your DB and data).
Best bet is to use the getrows() to grab data as it's fastest. Here's a quick function which makes grabbing your data easy. It will take a connection string, query and array name and put all of your results into an array to be used later. In addition, it creates an array of column names and grabs the datatype found in the column, to be used if need be.
Usage: call GrabDataArray(conx,thequery,arrname)
where conx= connection string
thequery= your db query
arrname= the target array to store the results
For Example:
Call GrabDataArray("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=pathToYourDB.mdb","select NumericTest, DateTest, StringTest from yourTable","arr")
Code:
function GrabDataArray(conx,thequery,arrname)
dim cnt
cnt=0
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open conx
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open thequery, conn
'HandleSQLErrors conn, "QUERY", Query
if not objRS.eof then
basearr=objRS.GetRows()
execute(arrname &"=basearr")
if not isempty(basearr) then
execute ("redim " & arrname & "fieldtype(ubound(baseArr,1)+1)")
for each countField in objRS.fields
thefields=thefields & ucase(countField.name) & ","
if not isempty(basearr) then
execute(arrname & "fieldtype(cnt)=countField.type")
cnt=cnt+1
end if
next
if thefields<>"" then thefields=split(left(thefields,len(thefields)-1),",")
execute ("dim "& arname & "fields")
execute(arrname & "fields= thefields")
end if
end if
objrs.close
set objRS=nothing
conn.close
set conn=nothing
set basearr=nothing
end function
To iterate through the results, you test for values and then run through the array (each array starts at position 0, and is a 2 dimension array). Arrays are stored as array(column, row). As we have name/value pairs, to get the first row, first column in the array it would be arr(0,0). To get the first row, 2nd column, it would be arr(1,0). Notice the 2nd column is called using the number 1- that's because arrays start at 0. To get the 2nd row, 3rd column, you'd look at arr(2,1).
In this example, we'll use a loop called "x" to loop through the rows. Ubound is the command to tell us the "upper boundary" of an array, or its last known values.
Code:
if not isempty(arr) then
for x=0 to ubound(arr,2)
response.write(arr(0,x) & "," & arr(1,x) & "," & arr(2,x) & "<BR>"
next
end if
Voila.
To add things like clickable links, you need to escape out any characters that might be used negatively in your querystring or forms. A simple replace function can be used to do so, by replacing the characters entered with other characters, or none at all.
It should be more comprehensive, but this is a simple starting off point to get the idea:
Code:
function ClearSqlInjection(formfield)
ClearSqlInjection=trim(replace(replace(replace(replace(request(formfield),"'","''"),"--",""),";",""),"@@",""))
end function
To do something like you are asking, with sortable columns, it'll take a little more effort. Here's a quick example that you could copy verbatim that will get you a rough layout, displayed in a table with clickable links. Edit with your conenction and query, stylize, etc as you see fit.
Code:
<%
function GrabDataArray(conx,thequery,arrname)
dim cnt
cnt=0
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open conx
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open thequery, conn
if not objRS.eof then
basearr=objRS.GetRows()
execute(arrname &"=basearr")
if not isempty(basearr) then
execute ("redim " & arrname & "fieldtype(ubound(baseArr,1)+1)")
for each countField in objRS.fields
thefields=thefields & ucase(countField.name) & ","
if not isempty(basearr) then
execute(arrname & "fieldtype(cnt)=countField.type")
cnt=cnt+1
end if
next
if thefields<>"" then thefields=split(left(thefields,len(thefields)-1),",")
execute ("dim "& arname & "fields")
execute(arrname & "fields= thefields")
end if
end if
objrs.close
set objRS=nothing
conn.close
set conn=nothing
set basearr=nothing
end function
function ClearSqlInjection(formfield)
ClearSqlInjection=trim(replace(replace(replace(replace(request(formfield),"'","''"),"--",""),";",""),"@@",""))
end function
'--check to see if we have column name and sort in the query string
'--col=column name dir=sort direction
if request("col")<>"" then col=ClearSqlInjection("col") else col="StringTest"
if request("dir")<>"" then dir=ClearSqlInjection("dir") else dir="ASC"
'--Grab the data and dump into the array
Call GrabDataArray("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=pathToYourDB.mdb","select NumericTest, DateTest, StringTest from yourTable order by " & col & " "& dir ,"arr")
'--change the direction of the sort so each click does the opposite
if dir="ASC" then dir="DESC" else dir="ASC"
'--Create the table
response.write("<table width='100%' border='0' cellpading='2' cellspacing='0'>")
'--Grab the column names and display them
if not isempty(arrfields) then
'--start table row
response.write("<tr>")
for x=0 to ubound(arrfields)
'--write column name and href link to the same page
response.write("<td align='left' valign='middle'><a href='" & request.servervariables("script_name") & "?col=" & arrfields(x) & "&dir=" & dir & "'>" & arrfields(x) & "</a></td>")
next
'--end table row
response.write ("</tr>")
end if
'--write out the data returned from the query, where y is the number of columns (found in column names) and z is rows
if not isempty(arr) then
for y=0 to ubound(arr,2)
response.write ("<tr>")
for z=0 to ubound(arrfields)
response.write ("<td align='left' valign='middle'>" & arr(z,y) & "</td>")
next
response.write ("</tr>")
next
end if
'--Close the table
response.write("</table>")
%>
HTH
Help with simple ASP + SQL?
Reply With Quote
Bookmarks