Pulling Information From Your Database
Now that we know how to insert data into our database, it would be nice to create a page that pulled that information to view on a page, right? Well, here is how we do it. This is actually one of the easiest parts of an ASP application. First we’ll start out by simply pulling data from our database. Secondly, we’ll look at how to pull information based on certain criteria. So, what are we waiting for.
Just like every other ASP page we’ve created so far, we start with declaring what language we’re using, declare option explicit to catch bugs, and then declare our variables.
<% Option Explicit %>
Dim objConn, RS
'We set our RS variable equal to our SQL statement. The * tells it to select all from the users table and order the results by the username field. The default is in ascending order, but you can change this by putting DESC after your ORDER BY username statement.
Set RS = objConn.execute("SELECT * FROM users ORDER BY username")
'Now we need to iterate through our recordset, so we tell it that while it's not rs.E(nd)O(f)F(ile), to response.write our text followed by our records.
while not RS.eof
response.write "Username: " & RS("username") & ", "
response.write "Password: " & RS("password") & " <br>"
'Now we tell it to move to the next record and do the same thing
'Finally, we end the loop because the rs.eof property has become true
'Now we clean up everything by closing and setting our RS and objConn properties to nothing
Set RS = nothing
Set objConn = nothing
If we wanted to put our data in a table, we could have done things a bit differently. Replace the code from while to wend with the following code if you want to put the recordset information in a table.
while not RS.eof
response.write "<tr><td>Username: </td><td>" & RS("username") & " </td><td> Password: </td><td>" & RS("Password") & "</td></tr>"
You should understand now how to pull information from a database. Now we need to take a quick look at a conditional pull. Let’s say that we only want to pull the username and password from our list of a certain person. We would add to our SELECT statement to do this. See the changes below.
‘First we declare a variable to hold our username that someone just submitted from a form. Then we SELECT all from our users table where the username field matches with the username variable.
username = trim(request.form(“username”))
Set RS = objConn.execute(“SELECT * FROM users WHERE username =’ “& username &” ‘ “)
‘Notice that when we stuck our variable into our SELECT statement, that we used ‘ ” & variablename & ” ‘ “. Be sure to notice the way the quotes work. This was always a confusing part of ASP for me, but I think I got it now…so just make sure you understand how to insert a value into a SQL statement with using the quotes.
By following the above example, you can create a page where you have a link that contains a hyperlink. When someone clicks on the hyperlink, it will pass an id to the asp page and the asp page will pull only the information related to that id. We’ll talk about making dynamic hyperlinks more in our next lesson. See ya next time!
Times Viewed: 6