View Full Version : Access db

07-09-2007, 09:45 PM
I've seen a lot of messages about how to post info from HTML forms to a db. I'm working on a very large db (in MS Access) and would like to know the best ('easiest) way to pull data from that db by using a search box. I don't have much experience in programming, but I do have a class in ASP.net coming in Sept. I see Javascript and VBscript used to post info to the db. Can someone help this novice??:confused:

07-16-2007, 10:00 PM
Most people here will use a SQL Query to grab the records that meet their criteria

SELECT Address, City, State, Zip FROM MyTable WHERE UserID LIKE 'm%'

In that query all users who's UserID begins with an m would be returned.
That is all that is need in the db to get the results. However, to get them to display on an ASP page you need to have a bit more info. You need to tell the page which database to open, and to put the results into either a recordset or an array to display all of the results.

Now to make it so that this uses a variable in the query you just add the variable to the code and since this variable is going to come from a form with the method=post and the inputs name set to txtSearch we will fill in the variable Using the Request Collection

So let's assign that value to a variable called sSQL, and have a variable called sConn which handles our database connection (either the DSN or the DSNLess connection), and an object called oRs which will be our recordset object.

'declare our variables
Dim sSearchFor
Dim sConn
Dim sSQL
Dim oRs

'Assign the value of sSearchFor
sSearchFor = Request.Form("txtSearch")

'If you want to make it so that the search uses a wildcard you can add the wildcard here or at the time of assignment, or even in the SQL Query
sSearchFor = sSearchFor & "%"

'assign the DSN or DSNLess connection string for your DB
sConn = "my_Connection"

'assign the query variable
sSQL = "SELECT Address, City, State, Zip FROM MyTable WHERE UserID LIKE '" & sSearchFor & "'"

'Assign the Recordset object
Set oRs = Server.CreateObject("ADODB.Recordset")

'open the recordset object using the SQL query and the connection string as the two parameters that you are going to pass
oRs.Open sSQL, sConn

'Look to see if there are any matches by checking for EndOfFile
If Not oRs.EOF Then

'Let's loop through all the results
Do Until oRs.EOF

'Write the results to the page
Response.Write oRs("Address") & "<br>"
Response.Write oRs("City") & ", " & oRs("State") & " " & oRs("Zip") & "<br>"
Response.Write "<hr>"

'Move to the next Record

'There was no match
Response.Write "No results match your search"
End If

'Close the recordset and dispose of the object to save server resources
Set oRs = Nothing

that is the most basic way of displaying database info onto a web page using ASP classic. ASP.NET offers a bunch of easier ways to do this.