BEGINNING ASP DATABASES PART 1 – PREPARING TO USE SIMPLE RECORDSETS (Page 2)

Stuffing Data into a Variable

Sometimes we don’t need the data to go directly to the page, in which case we can save the information into a variable. For example, we may need to perform some string manipulation or validation prior to building the page.

VarNameFirst = ORSp("PeopleNameFirst")

The code on the above line stores the data into a variable for later use. This must be performed within ASP delimiters, since HTML lacks capacity to use variables.

The most common mistakes are:

  • Attempting to perform this operation in HTML, outside of ASP
  • Wrong RS name or wrong Field name
  • Misspelled Field Name (very common error)

Using Data in an Expression

Data retrieved by ADO can be used directly in an expression. The pseudo-code listings below show examples:

If ORSp("NameFirst")="Enrico" then
 ' code for Enrico
End If

In the above code we use the data in a field of the current record of the oRSp recordset as the text to compare against the word “Enrico.” A similar test is performed in the code below to determine if it is time to end the looping.

Do while NOT oRSp("NameFirst")="Enrico"
 ' code for people OTHER then Enrico
 oRSp.MoveNext
Loop

If oRSp("Member") then
 ' code for members
Else
 ' code for non-members
End If

In our last case we switch to retrieving data from a different field. The member field was established (at the time the database was designed) as of type True/False. Therefore it will return a value of true or false, which can be directly used as a whole expression. If the database contains true the code for members will be run.

The most common mistakes are:

  • Attempting to perform this operation in HTML, outside of ASP
  • Leaving the comparison sign (= or > or <) out of the expression
  • Writing expressions where the two sides of the comparison sign are of two different data types. For example, “Joe” should not be compared to “2”.
  • Wrong RS name or wrong Field name
  • Misspelled Field Name (very common error)
  • Errors in upper/lower case for data stores that are case sensitive
  • Quotes around numerical values

Using Data as an Argument in a Function

Data read by ASP-ADO can be used as an argument for another function. For example:

VarNameFirstLetter = Left(oRSp("PeopleNameFirst"),1)

VarPassword = lCase(oRSp("PeopleNameFirst"))

VarSpaceLocation = instr (oRSp("PeopleNameFirst")," ")

Although the above works, many coding shops prefer that you first read the data into a local variable. It is easier to read and maintain code without all of the quotes and parentheses.

The most common mistakes are:

  • Attempting to perform this operation in HTML, outside of ASP
  • Providing data from ASP-ADO which is of the wrong type for the argument
  • Wrong RS name or wrong Field name
  • Misspelled Field Name (very common error)
  • Not writing test code to handle a request that returns a NULL

Try It Out – Using Recordset Data

We’ll create a page that opens a recordset based on the items table of the Clothier database (see Appendix B for the source and structure of this database). From that recordset, we’ll perform four tasks:

  • Writing the name of the first item on the page
  • Putting the type of the first item into a variable and then print that variable to the page
  • Using an If..Then structure so that items with less than 10 to a box show the warning “Small Box”
  • Printing the price of the first item (using the Format function to make it with two decimal places only)

The following listing shows the code to generate the required page:

<%<
dim oRSi
set oRSi=server.CreateObject("adodb.recordset")
oRSi.open "items", "DSN=clothier"
oRSi.MoveFirst

Response.Write "Next line is a simple write of data:<BR>"
Response.Write oRSi("ItemName") & "<BR><BR>"

Response.Write "Next line is writing a variable that" &_
                   " holds the data:<BR>"
dim varItemName
varItemName = oRSi("ItemType")
Response.Write varItemName & "<BR><BR>"

Response.Write "Next line is deciding what to write based on an" & _
                   " If...Then using the data:<BR>"
If oRSi("ItemQtyPerBox")<10 then
 Response.Write "Small Box<BR><BR>"
Else
 Response.Write "Large Box<BR><BR>"
End If

Response.Write "Next line uses the data as an argument" &_
"                       for a function:<BR>"
Response.Write UCase(oRSi("ItemDepartment")) & "<BR><BR>"
%>

Your page should appear as below in your browser:

unnamed-file-282 BEGINNING ASP DATABASES PART 1 - PREPARING TO USE SIMPLE RECORDSETS
       (Page 2)

How It Works – Using Recordset Data

The first few lines in the next listing create the recordset. A recordset will open with the pointer at record one, so in this simple case there is actually no need for the MoveFirst method. On the other hand, if a recordset has been opened earlier and been in use, you would want to use the MoveFirst so you are sure you are at BOF.

<%
dim oRSi
set oRSi=server.CreateObject("adodb.recordset")
oRSi.open "items", "DSN=clothier"
oRSi.MoveFirst

In our first section we merely need to print the data which is returned from the recordset, as follows:

Response.Write "Next line is a simple write of data:<BR>"
Response.Write oRSi("ItemName") & "<BR><BR>"

Response.Write "Next line is writing a variable that" &_
                    " holds the data:<BR>"
dim varItemName
varItemName = oRSi("ItemType")
Response.Write varItemName & "<BR><BR>"

As we can see in the next list, you can use data without printing it to the page. Below we use it in a test to determine which of two messages to write to the page:

Response.Write "Next line is deciding what to write based on an" & _
                   " If...Then using the data:<BR>"
If oRSi("ItemQtyPerBox")<10 then
 Response.Write "Small Box<BR><BR>"
Else
  Response.Write "Large Box<BR><BR>"
End If

Another option is to use the data returned from the recordset as an argument in a function. Below we use the name of the department of the item as the argument for the Upper Case function.

Response.Write "Next line uses the data as an argument" &_
                     " for a function:<BR>"
Response.Write ucase(oRSi("ItemDepartment")) & "<BR><BR>"
%>