Help with simple ASP + SQL?

Hi guys, I'm playing around with ASP a bit more and now I'm trying to figure out some basic SQL connections and such. I've made a very simple database in Access, which only has one table. It has the following 3 columns:

NumericTest (int)
DateTest (datetime)
StringTest (text)

For each column, I have 3 records of example data.

Although this database is extremely simple, I have a fairly good knowledge of many advanced Access concepts; I'm just using this very simple example to try to learn to use it with ASP.

Anyways, I'm just trying to do some really basic stuff. What I want to do it to display the database records in an HTML table (I know I have to use Response.Write to do this, but how do I create the connection to the database? Do I do it using CreateObject? Can someone provide me with an example or tell me how it's done?)

Next, I'd like to be able to include a simple search filter on the page...for example, if a user wanted to find a record containing the string "boo" they could type it into a textbox, and have the table only display records that contain that string.

Lastly, I'd like to be able to have the user to select a sort option...in other words, allowing them to sort via ascending or descending on one column.

Can anyone help me out with this? I would greatly appreciate it! Thanks! :D

Quote:

---

Do I do it using CreateObject? Can someone provide me with an example or tell me how it's done?

---

I don't use Access but I use SQL Server. The only thing you need to change is the connection string. Do google search on this (should be many examples). Here is the code to connect to a DB:

Code:

---

Dim objConn As New ADODB.Connection
Dim objRst As New ADODB.Recordset
Dim strSQL As String
Dim strConnectionString = "Google for the connection string to access db"
strSQL = "Select NumericTest, DateTest, StringTest From YourTable"
objConn.Open(strConnectionString)
objRst.Open(strSQL, objConn)
While Not objRst.EOF
      Response.Write objRst("NumericTest").Value & "<br/>"
      Response.Write objRst("DateTest").Value & "<br/>"
      Response.Write objRst("StringTest").Value & "<br/>"
      'If you want this in a table do Response.Write "<table>" etc around this
End While
objRst.Close()
objConn.Close()
objRst = Nothing
objConn = Nothing
        Return strResult

---

Quote:

---

Next, I'd like to be able to include a simple search filter on the page...for example, if a user wanted to find a record containing the string "boo" they could type it into a textbox, and have the table only display records that contain that string.

---

change

Code:

---

strSQL = "Select NumericTest, DateTest, StringTest From YourTable"

---

into

Code:

---

strSQL = "Select NumericTest, DateTest, StringTest From YourTable Where Stringtest = '" & request.form("nameofyourtextfield") & "'"

---

Quote:

---

Lastly, I'd like to be able to have the user to select a sort option...in other words, allowing them to sort via ascending or descending on one column.

---

Code:

---

strSQL = "Select NumericTest, DateTest, StringTest From YourTable Where Stringtest = '" & request.form("nameofyourtextfield") & "' Order by Datetest"

---

Quote:

---

Can anyone help me out with this? I would greatly appreciate it! Thanks!

---

I hope this helps. cheers

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

hello probably these will help you ,There's a basic introduction, with copyable code, to connecting with an Access database here:
http://www.codeproject.com/KB/database/accessdb.aspx

Once you've got something up and running, chuck it up here and we'll see if we can get some filtering working on it.

If you write your code like
Response.Write objRst("NumericTest").Value & "<br/>"
Response.Write objRst("DateTest").Value & "<br/>"
Response.Write objRst("StringTest").Value & "<br/>"

So, its possible after loop statement.