Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Access db
07-09-2007, 08:45 PM #1
- Join Date
- Jul 2007
- Thanked 0 Times in 0 Posts
07-16-2007, 09:00 PM #2
- Join Date
- Dec 2002
- Arlington, Texas USA
- Thanked 11 Times in 11 Posts
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 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.