•
•
•
View ASP Code (/bret/resume/adotutorial/datautils.asp)
Go Back to
<%
' =====================================================================
' Net Connex Data Utilities
' =====================================================================
'
' The utilities in this file help with a number of database related
' functions. Read the individual descriptions of the functions for
' more info.
' =====================================================================
%>
<%
' ---------------------------------------------------------------------
' INCLUDE THE ADOVBS.ASP FILE
' ---------------------------------------------------------------------
' The adovbs.asp file contains ADO constants from Microsoft. I will
' include them here so that any page that constants can be used by the
' functions in this include file, as well as the pages that use this
' file.
' ---------------------------------------------------------------------
%>
<!-- #INCLUDE FILE=adovbs.asp -->
<%
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' FieldValue Function
' ---------------------------------------------------------------------
' This function accepts a ADO field object as a parameter. It checks
' to see if the value of the field is NULL. If it is, it returns a
' space, otherwise, it returns the value of the field. This is used
' by routines that are displaying data from the table, and need valid
' spaces to appear if the data is null (like in tables so that cells
' are properly formatted, etc.).
' ---------------------------------------------------------------------
Public Function FieldValue(objField)
If IsNull(objField.Value) Then
FieldValue = " "
Else
FieldValue = objField.Value
End If
End Function
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' Data2HTML Function
' ---------------------------------------------------------------------
' This routine is a handy way to display the contents of a recordset.
' It is not very sophisticated, and will show all fields in all records
' in a recordset, so take care to to pass huge recordsets to it.
' It draws a table. Each column has a header named after the field.
' Each row shows an individual record in the recordset.
' ---------------------------------------------------------------------
Public Sub Data2HTML(rsData)
Dim objField 'This will be used to step through the Fields collection
'of the recordset, and temporarily store each field
'First lets test to see if the object that was passed to
'us was a recordset
If UCase(TypeName(rsData)) = "RECORDSET" Then
'Great, we have a valid recordset.
%>
<TABLE WIDTH=100% BORDER=1 CELLPADDING=2 CELLSPACING=0>
<TR>
<%
'Now show a header for each field
For each objField in rsData.Fields
%>
<TH NOWRAP BGCOLOR=""#C0C0C0"">
<FONT SIZE=-2><%=objField.Name%></FONT>
</TH>
<%
Next
%>
</TR>
<%
'Move to the beginning of the recordset
rsData.MoveFirst
'Now show a row for each record.
While Not rsData.EOF
%>
<TR>
<%
For each objField in rsData.Fields
'Show the value of the field in a table cell.
'Use the FieldValue function to get nice looking values
'out of the field.
%>
<TD>
<FONT SIZE=-2><%=FieldValue(objField)%></FONT>
</TD>
<%
Next
rsData.MoveNext
%>
</TR>
<%
Wend
'Close up the table
%>
</TABLE>
<%
Else
'If we got to this point, the calling routine did not pass the
'right type of object as a paramater. Tsk. Tsk.
%>
<TABLE WIDTH=100% BORDER=0 CELLPADDING=2 CELLSPAING=0>
<TR>
<TD BGCOLOR=#FF0000>
<FONT SIZE=+2>ERROR DISPLAYING DATA TABLE</FONT><BR>
A valid ADO recordset must be passed to the Data2HTML routine.<P>
</TD>
</TR>
</TABLE>
<%
End If
End Sub
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' GenLookup Function
' ---------------------------------------------------------------------
' This is another handy function that lets you generate an HTML Select
' statement based on a SQL query. Read the docs in the function for
' more information on the parameters, etc.
' ---------------------------------------------------------------------
Public Sub GenLookup(ByVal v_sSQL, ByVal v_sName, ByVal v_sID, _
ByVal v_vValField, ByVal v_vDispField, _
ByVal v_vSelected, ByVal v_nMultiCount)
'This routine will generate a lookup table based on the SQL statement
'that is passed. It assumes that a Session("CON") variable exists
'that is a connection to the database.
'Parameter Description
'-------------- -----------------------------------------------------
'v_sSQL The SQL Statement to generate the lookup recordset
'v_sName The name to assign to the form element
'v_sID The ID to assign to the form element
'v_vValField The position or name of the field to use for values
'v_vDispField The position or name of the field to use for display
'v_vSelected The value of the item that should be selected
'v_nMultiCount The number of lines to display for multiselect boxes.
' 0 Implies drop-down list.
Dim conDB
Dim rsData
Dim sHTML
If IsEmpty(Session("CON")) Then
'There is no connection object. Abort
Response.Write "Session(""CON"") Does Not Exist. " & _
"Cannot Create Lookup Element<P>" & vbCrLf
Exit Sub
Else
Set conDB = Session("CON")
End If
Set conDB = Session("CON")
Set rsData = Server.CreateObject("ADODB.Recordset")
rsData.Open v_sSQL,conDB,3,3
If Not (rsData.BOF and rsData.EOF) Then
rsData.MoveFirst
'Start the select statement
sHTML = "<SELECT NAME=""" & v_sName & """ ID=""" & v_sID & """ "
If v_nMultiCount > 0 Then
sHTML = sHTML & " MULTIPLE SIZE=" & v_nMultiCount
End If
sHTML = sHTML & ">"
Response.Write sHTML & vbCrLf
While Not rsData.EOF
sHTML = "<OPTION VALUE=""" & rsData(v_vValField) & """ "
If rsData(v_vValField) = v_vSelected Then
sHTML = sHTML & " SELECTED "
End If
sHTML = sHTML & ">" & rsData(v_vDispField)
Response.Write sHTML & vbCrLf
rsData.MoveNext
Wend
Response.Write "</SELECT>" & vbCrLf
Else
Response.Write "No records were returned by the query<P>" & _
vbCrLf & "Query: " & v_sSQL & "<P>" & vbCrLf
End If
End Sub
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' UserTypeName Function
' ---------------------------------------------------------------------
' For a given user type number, this function will get the name of the
' user type.
' ---------------------------------------------------------------------
Function UserTypeName(ByVal v_nType)
Dim rsTypes
Dim conDB
Dim sSQL
Set rsTypes = Server.CreateObject("ADODB.Recordset")
Set conDB = Session("CON")
sSQL = "Select UserType From UserType Where UserTypeID=" _
& v_nType & ";"
Set rsTypes = conDB.Execute(sSQL)
If Not (rsTypes.BOF and rsTypes.EOF) Then
UserTypeName=rsTypes("UserType")
Else
userTypeName=""
End if
End Function
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' SELECTED Function
' ---------------------------------------------------------------------
' This function accepts two parameters. It compares the parameters.
' If they are equal, it returns 'SELECTED'. If they are not equal, it
' returns an empty string. This can be used in <SELECT...> elements to
' determine if an individual <OPTION...> should be SELECTED or not.
' ---------------------------------------------------------------------
Function SELECTED(ByVal v_vParm1, ByVal v_vParm2)
If v_vParm1 = v_vParm2 Then
SELECTED="SELECTED"
Else
SELECTED=""
End If
End Function
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' CHECKED Function
' ---------------------------------------------------------------------
' This function accepts two parameters. It compares the parameters.
' If they are equal, it returns 'CHECKED'. If they are not equal, it
' returns an empty string. This can be used in checkbox and radio
' button elements to determine if it should be checked or not
' ---------------------------------------------------------------------
Function CHECKED(ByVal v_vParm1, ByVal v_vParm2)
If v_vParm1 = v_vParm2 Then
CHECKED="CHECKED"
Else
CHECKED=""
End If
End Function
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' ExecQuery Function
' ---------------------------------------------------------------------
' This function executes a query by using the Session level "CON"
' variable.
' ---------------------------------------------------------------------
Function ExecQuery(ByVal v_sSQLString)
Dim conDB
Dim bResult
'Keep going even if there is an error. I will handle those later.
'On Error Resume Next
'Err.Clear
'Start out assuming that the query won't succeeed
bResult = False
If Not(IsEmpty(Session("CON"))) Then
If Not(Session("CON") Is Nothing) Then
Set conDB = Session("CON")
conDB.Execute v_sSQLString
If (Err.Number = 0) Then
'If we got this far, then we were able to get to the
'connection object, and execute the query successfully
bResult = True
End If
End If
End If
'Return the result to the calling routine
ExecQuery = bResult
End Function
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' GetNewID Function
' ---------------------------------------------------------------------
' ---------------------------------------------------------------------
Function GetNewID(ByVal SQLString, ByVal sFieldName)
Dim conDB
Dim rsData
Set conDB = Session("CON")
Set rsData = Server.CreateObject("ADODB.Recordset")
rsData.Open SQLString,conDB,3,3
GetNewID = rsData(sFieldName)
End Function
' ---------------------------------------------------------------------
%>
<%
' ---------------------------------------------------------------------
' LookupFieldValue Function
' ---------------------------------------------------------------------
' ---------------------------------------------------------------------
Function LookupFieldValue(ByVal v_sSQL, ByVal v_sLookupField)
Dim nRec
Dim rsLookup
Dim conDB
Dim sLookupSQL
Set conDB = Session("CON")
Set rsLookup = Server.CreateObject("ADODB.Recordset")
rsLookup.Open v_sSQL, conDB, 3,3
If Not (rsLookup.BOF and rsLookup.EOF) Then
rsLookup.MoveFirst
LookupFieldValue = rsLookup(v_sLookupField)
Else
LookupFieldValue = ""
End If
End Function
' ---------------------------------------------------------------------
%>