Access db

[dday39]

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??

[miranda]

Most people here will use a SQL Query to grab the records that meet their criteria

example
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 
        oRs.MoveNext
    Loop

Else
     '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
oRs.Close
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.