devxlogo

Display Selective Data From a Database

Display Selective Data From a Database

Why Trust Us

n last month’s 10-Minute Solution, you learned how to display data from a database. This time, you’ll see how to use this technique to pick and choose what to display from the database based on the user’s choice. Before you begin, you need to get your database ready. You can access any ODBC-compliant database from your ASP pages using ActiveX Data Objects (ADO). This means you can access a Microsoft Access database, a Microsoft SQL Server database, or even an Oracle database. For the sake of this article, I will demonstrate how to access a Microsoft Access database.

Imagine that you’re presenting a page where the user can choose to see a list of customers. Instead of dumping the entire contents of the table “Customers”, this time you let the user choose the criteria for selecting the customer. Assume that the user wishes to view customers filtered by their state. After the user enters or chooses a state, you display the customers that reside in that state.

When a user accesses a page from a Web site, the browser sends a request to the Web server. The Web server then serves or returns the page. The user can take action on the page and either visit another link, or if it is an HTML form page, submit the form. If the user submits a form, a specific page on the Web server will receive the submitted form’s data.

First, you need a page that displays an HTML form asking users what state they wish to pick. You will need a second page that will accept that information, obtain the data from the database, and then display the data. Figure 1 shows the chain of events that occur when the user first visits the page till the user gets the data requested.

Normally, textbooks will teach that you need two pages on the server to handle this scenario: the page that displays the HTML form, and the page that accepts the form data and returns the data from the database. However, from my experience, it is much easier to code this schema with a single ASP page. The code remains in a single location, making it easier to maintain, debug, and enhance. However, you will need to add code to distinguish the browser’s request?is it asking for page 1 or returning the HTML form’s data? You can create each page portion separately and then see how you can link them up to be part of the same page.

Build the HTML Form
For this demo, you will create a simple HTML form. To begin with, you can make it really simple and provide the user with a label, a text box, and a command button. The user types in a state abbreviation in the text box and clicks the command button to submit the form. Create a non-Active Server Pages (ASP) page called Page1.htm and use this code to generate the HTML form (see Listing 1). This makes it easy to test and debug.

This code should result in an HTML form that looks like Figure 2. Notice that you have three form controls on the page: a text box called “tS”, a command button called “cmd”, and an invisible textbox called “PASS”, which will always contain the value “1”. When the user submits this form, you will be able to read the values of these controls. Also note that you have not yet completed the form tag in your code. For this testing page, you only used

as in the line of code:

That code alone will not work; you will need to modify it. An HTML form needs two other attributes within its tag. You need an ACTION attribute and a METHOD attribute. The ACTION attribute specifies what page on your Web server will receive this form’s data and process it. The METHOD attribute informs the browser how the data will be sent?as a long string within the query to the target page (GET) or as separate invisible headers to the Web server (POST). Remember, you will be placing all your code in a single ASP page?let’s call it 10MinPrimer2.asp. So you want the form’s ACTION attribute to point to this page. For this demo, you can use a POST METHOD. So, modify the tag to read as follows:

Create the Form Processing Piece
Now work with your ASP page that will display selective data from the database. If you followed the instructions in last month’s article, you probably have a template for your ASP pages that looks like Listing 2. Save the template as your new ASP page, 10MinPrimer2.asp. Then, obtain the data from the HTML form right in the beginning under the line of code that reads:

'----------------------------------------------------------------------' All your ASP preprocessing code goes here'----------------------------------------------------------------------

At this point, you need to get the data from the form. To do so, use the Request object to query the value of the control on the form. For example, obtain the state entered in the textbox “tS” using this code:

Dim strState, intPassstrState  = Request("tS")

Whoa! Wait a minute. All the documentation and all the other textbooks teach you that, in order to obtain the value of a control on an HTML form, you need to use this code:

strState = Request.Form("tS")

In this case, you’re not using this syntax; you drop the “.Form”. Why? Well, it so happens, you can easily access the form’s contents directly from the Request object, instead of going via the Form object contained within the Request object. Not only may that be slightly faster (you have less indirection), but a bigger advantage is that if you change the METHOD tag of your HTML FORM from POST to GET, your code will still work unchanged?the text book code will need to be modified to Request.QueryString(“tS”). So it’s your choice: have two separate blocks of code to handle a GET or a POST, or use a single line of code irrespective of how the form was submitted.

You now have the value of the state within your variable strState. You must also obtain the value of the variable “PASS” from the form into your variable intPass.

intPass = Request("PASS")

You are now ready to take action. The variable “PASS” comes from an invisible textbox on the page so the user cannot tamper with its value. It can have only two possible values: a value of “1” or a value of “” or nothing. This variable “PASS” tells you how the page 10MinPrimer2.asp was accessed. If you are coming to this page for the first time (Step 2 to 3 in your chain of events), the variable “PASS” will have no value. If you are coming to this page for the second or later time (Step 5 to 6 in your chain of events), the variable “PASS” will have a value of “1”. You use this variable to decide what you need to do next.

' -- Take Action based on how you came into this pageSelect Case Trim(intPass)	Case Trim("1")		' -- Repeat Visit, display database data		DisplayDatabaseData	Case Else		' -- First Time Visit, display HTML Form		DisplayHTMLFormEnd Select' -- Make sure nothing else gets processedResponse.End

You use a ‘SELECT CASE’ statement to evaluate the value of the variable intPass. If it is equal to 1, you call a separate subroutine to handle displaying the data from the database. If it is anything else, you again call a separate subroutine to display the HTML form. You also make sure that no code below the Select Case ever gets accidentally executed by ending the response with a “Response.End” statement.

You create the DisplayHTMLForm subroutine by simply cutting and pasting the HTML text from your testing page you created earlier (see Listing 3). You are now ready to create the DisplayDatabaseData subroutine. This routine will use most of the code from last month’s article. Since the variable strState was declared at the very top of the page, it is visible to all subroutines within your page. So this subroutine can access it and use ADO to then obtain and display the data. You create a subroutine by using the Sub and End Sub statements, making sure that they are within your ASP code section bounded by <% and %>.

<%'----------------------------------------------------------------------' Sub Routine: DisplayDatabaseData'				      Displays the data from the database'----------------------------------------------------------------------Sub DisplayDatabaseDataEnd Sub%>

Place ADO Objects in Your ASP Page
The simplest way to access a database with ADO is to use just two objects: a Connection object to connect to the database, and a Recordset object to obtain the data from the database. This technique is also the fastest because it returns a read-only, forward-scrolling Recordset. In 90 percent of the cases of database access from an ASP page, you only wish to access data from the database and dump the contents on to the browser. For this purpose, a forward-scrolling, read-only Recordset is ideal.

Within your new subroutine, use this code to create a Connection Object and a Recordset object:

' -- Declare VariablesDim objConn			' your Connection ObjectDim objRS			' your Recordset ObjectDim strSQL			' your SQL String to access the databaseDim strConnection		' your Connection string to access the databaseDim i				' a counter variable' -- Create objectsSet objConn = Server.CreateObject("ADODB.Connection")Set objRS = Server.CreateObject("ADODB.Recordset")

At this point, you have an empty Connection object (objConn) and an empty Recordset object (objRS). Use the Open method of the Connection object to open a connection to the database. The Open method requires up to three arguments. A connection string is the first argument, followed by an optional user ID, and an optional password argument. The connection string tells ADO how to connect to the database. If you are using an ODBC DSN, all you need to provide is the name of your DSN. If the DSN holds the username and password within it, you do not need to supply any more arguments. If not, you will need to include the user ID and password arguments. In the sample, the File DSN itself contains the user ID and password, so you do not need any more arguments. All you need to write is:

ObjConn.Open "DSNNAME"

The way you specify the DSN Name in the Open method differs based on whether you are using a System DSN or a File DSN. If you’re using a System DSN called NWIND, then you would write this code:

ObjConn.Open "DSN=NWIND"

If you are using a File DSN, the code is almost identical, except instead of saying “DSN=”, you code it as “FILEDSN=”. Also, the full path and name of the DSN file needs to be spelled out. For example, if your DSN file were physically located on the Web server machine at “C:WEBSASPPRONWIND.DSN” then your code would be:

ObjConn.Open "FILEDSN=C:WEBSASPPRONWIND.DSN"

If you need to provide the user ID and password, add them as separate arguments:

ObjConn.Open "DSN=NWIND", "USERID", "PASSWORD"

To make your code easier to modify, place the connection string in a variable and use the variable name instead:

' -- Connection String ValuestrConnection = "FILEDSN=path on my machineNWIND.dsn"' -- Open the ConnectionobjConn.Open strConnection

Populate the Recordset Object with Data
Unlike last month’s article, you have to selectively obtain the data from the database this time. You will need to modify your SQL statement to obtain only records that match particular criteria. Instead of bringing back all records with a “SELECT * FROM CUSTOMERS”, you will need to bring back data by using a “WHERE” clause. You evaluate the value of the strState variable to decide what your SQL statement will look like. Place this SQL statement in a variable, again to facilitate code maintenance, and then use the Connection object’s Execute method to open a Recordset:

' -- your SQL Statement now needs to incorporate the State the user	' -- asked for.	Select Case Trim(strState)		Case ""	' user did not type anything, display all customers			strSQL = "SELECT * FROM CUSTOMERS"		Case Else	' display only selected customers			strSQL = "SELECT * FROM CUSTOMERS WHERE REGION = '" & strState & "' "	End Select' -- Populate your Recordset with dataset objRS = objConn.Execute (strSQL)

Note that you concatenate the value of the variable “strState” within your SQL statement. You also make sure that you have the proper field name for your state field (in this case it is “REGION”). Also, because the REGION field expects a string data, you have to make sure that the state variable value is enclosed within quotes. If the user had entered “WA” as the state, the previous SQL statement would have resolved to:

SELECT * FROM CUSTOMERS WHERE REGION = 'WA'

At this point, you have a Recordset (objRS) filled with data. That’s all it takes to get selective data from a database.

Before you proceed to access the data from the Recordset, it is always a good idea to check to see if you did indeed get back any data or not. To do so, check the Recordset’s BOF and EOF properties. If they are both true, you got back an empty Recordset with no data. In the sample, you are simply outputting an error message and ending the page with a “Response.end” so that no further code gets processed. In reality, you may want to provide a better error message, perhaps by redirecting the user to a separate error-handling ASP page:

if (objRS.BOF and objRS.EOF) then	response.write "No records found"	response.endEnd if

Process the Recordset Data
If you do get data back in the database, you can process the data. You can move from record to record within the Recordset by using the Recordset’s MoveNext method. You can access any field in the Recordset by using the Recordset(“fieldname”) syntax, or the Recordset(“fieldnumber”) syntax. In the sample, you are outputting all the columns of the Recordset as columns of a table, and all the records as rows in the table. You can begin by creating an HTML table. In the section of your ASP page, create an HTML table tag:

First, output a heading row containing the names of the fields in the Recordset object. Because you do not know the names, you will just iterate through the Fields collection of the Recordset to obtain the field names. In this code, you obtain the field names by using “objRS.Fields(i).Name”:
<%	' -- Output the Field Names as the first row in the table	Response.Write "
"	For i = 0 to objRS.Fields.Count - 1		Response.Write ""	Next	Response.write ""
Then “walk” through the Recordset outputting the field values. Again, in this code, you obtain the value of a field by using “objRS.Fields(i)”. This defaults to the .Value property of the field. You could have also used the code “objRS(i)” and obtained the same results:
' -- Now output the contents of the Recordset	objRS.MoveFirst	Do While Not objRS.EOF		' -- output the contents		Response.Write "
"		For i = 0 to objRS.Fields.Count - 1			Response.Write ""		Next		Response.write ""		' -- move to the next record		objRS.MoveNext	LoopClose the Objects Properly
Once you are done processing data, it is important to close the objects. Doing so not only regains your server resources, but it also disconnects from the database server, thereby improving performance in a multi-user scenario. Close the objects by using the Close method on the object. Physically purge them from memory by setting their values to “Nothing”:
objRS.Close	set objRS = Nothing	objConn.Close	set objConn = Nothing
To see a working example of this solution, as well as to get hold of the copy of the code, visit: www15.brinkster.com/theasppro/10MinPrimer2.asp . The first time you enter the page, you will see the HTML form. Assuming you entered the value “WA” within the text box for the region/state, you should get a screen that looks like Figure 3. I have modified the output by changing the font used for the display (it makes the output look nicer than the default Times New Roman font that the browser uses).
In this article, you saw how you can obtain data from the user and use it as the criteria to selectively obtain data from a database.  Next month, you’ll learn how to improve the code in this article to display a combo box of only the states/regions present in the database. You will also see how to store frequently accessed data (such as look-up tables, and so on) in an application level variable to speed your Web site’s performance.
 devxblackblue
About Our Editorial Process
At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.
See our full editorial policy.
About Our Journalist
 
 Charlie Frank
 
 Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
  View Author 
 
   Show More  
 
  
   Linkedin      Twitter   
  
  
©2024 Copyright DevX - All Rights Reserved. Registration or use of this site constitutes acceptance of our Terms of Service and Privacy Policy.
Sitemap — Privacy Policy
" & objRS.Fields(i).Name & "
" & objRS.Fields(i) & "