In some applications you may need to get information from a database without actually knowing what the structure of the data is before hand. In this case you can obtain the schema or structure of a database using methods provided by ADO. In this article we will discuss opening a database, first obtaining the tables and views contained in the database, then getting the fields of a selected table or view, and finally running a query of the database.
Deciding What Database to Open
In this example we will assume that we know the DSN name of the database. You can dynamically obtain database information as in the case of my application WinASP, which is a file browser and database viewer, but in this example we will pass a DSN to the script.
First off in the initial file we set a session variable to be the DSN, which is on the Web server that points to database. You could also use an Application variable that you set in the Global.asa's Application_OnStart event. Then we create an instance of the ADO Database object and open the database. From here we create a record set and use the Open Schema method to tell the object we want the objects contained in the database. Then open the record set to obtain the objects and loop through them. We are building a drop down menu in order to select the table we want and then display it on the next page where will display the fields that are contained in the database.
-------BEGIN CODE FOR PAGE DEFAULT.ASP-------
<HTML>
<HEAD>
<%
'here is where you set the DSN for the application
Session("DSN") = "DSN=MyDSN"
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open Session("DSN")
Set rstSchema = DBConn.OpenSchema(20)
%>
<BODY>
<P>Simple Search</P>
<P><FONT face="" size=2 style= "BACKGROUND-COLOR:#ffffff">Select from the
following tables in thedatabase:</FONT></P>
<P><FONT face="" size=2 style="BACKGROUND-COLOR: #ffffff">
<form Action=default2.asp METHOD=POST>
<SELECT id=select1
name="strTable" size=2 style= "HEIGHT: 102px; WIDTH:302px">
<%
Do Until rstSchema.EOF
'here you specify a table, should be a query this way they can defined by us first and presented to the user.
If rstSchema("TABLE_TYPE") = "TABLE" Or rstSchema("TABLE_TYPE") = "VIEW" Then
'TABLE for database tables and VIEWS for queries or views
response.write "<OPTION VALUE= """ & rstSchema("TABLE_NAME") &""">" & _
rstSchema("TABLE_TYPE") & " : " & rstSchema("TABLE_NAME") &"</option>" & vbCRLF
'nest query in here to get the fields.
End If
rstSchema.MoveNext
Loop
Set rstSchema = Nothing
DBConn.Close
Set DBConn = Nothing
%>
</SELECT>
</FONT></P>
<P><FONT face="" size=2 style="BACKGROUND-COLOR: #ffffff">
<INPUT id=submit1 name=submit1 style= "HEIGHT: 24px; WIDTH:81px" type=submit value="Next>>"></FONT></P>
</form>
</BODY>
</HTML>
Getting the Fields
On this page we will read the value of the form that was passed that contains the name of the table or view, open the object and get the fields that are contained in the database. Then we will check the constant values of the field type to discern what data type the field contains.
-------BEGIN CODE FOR DEFAULT2.ASP---------
<%
'this function is for placing brackets around table names with spaces in them.
'this is done in order to prevent the SQL code from crashing on the spaces.
Function RemoveSpaces(ValueIn)
If Instr(ValueIn, " ") <> 0 Then
RemoveSpaces = "[" & ValueIn & "]"
Else
RemoveSpaces = ValueIn
End If
End Function
%>
<HTML>
<HEAD>
<BODY>
<P>Simple Search</P>
<P><FONT face= ""size=2>Select from the following fields to
be included in your report or used asfilters:</FONT></P>
<form action="default3.asp" method=post>
<%
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open Session("DSN")
strTable = RemoveSpaces(request("strTable"))
SQLQ = "SELECT * FROM " & strTable
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = DBConn
rs.CursorType = adOpenKeyset
rs.Source = SQLQ
%>
<TABLE BORDER=1 BORDERCOLOR=#CCCCCC BORDERCOLORDARK=#CCCCCC BORDERCOLORLIGHT=#CCCCCC CELLPADDING=2 CELLSPACING=0 WIDTH=100%>
<TR>
<TD>Field Name</TD>
<TD>Display</TD>
<TD>Criteria</TD>
</TR>
<%
'first go through the table and pull all the fields from the table that was passed.
For i = 0 To rs.Fields.Count - 1
%>
<TR>
<TD>
<%= request("strTable") & "." & rs(i).Name %>
<input type=hidden name="fieldtype<%= i %>" value="<%= rs(i).Type %>"></FONT>
<input type=hidden name=field<%= i %> value="<%= strTable & "." & RemoveSpaces(rs(i).Name) %>">
</TD>
<TD>
<input type=checkbox value="<%= strTable & "." & RemoveSpaces(rs(i).Name) %>" name="display<%= i %>">
</TD>
<TD>
<%
'here we check to see what data type the field contains so we can add form fields
'for filtering the data and get results from the next page.
Select Case rs(i).Type
Case 3 'number
Response.Write "Not Searchable"
Case 6 'currency
Response.Write "Not Searchable"
Case 11 'yes & no
%>
True: <input type=radio name="criteria<%= i %>" value="-1">
False: <input type=radio name="criteria<%= i %>" value="0">
<%
Case 135 'date
%>
From: <input type=text name="criteria<%= i %>" size=10>
To: <input type=text name="criteria<%= i %>" size=10>
<%
Case 200 'text
%>
<input type=text name="criteria<%= i %>" size=15>
<%
Case 201 'memo field
%>
<input type=text name="criteria<%= i %>" size=15>
<%
Case Else
End Select
%></TD>
</TR>
<%
Next
Set rs = Nothing
DBConn.Close
Set DBConn = Nothing
%>
</TABLE>
<input type="hidden" name="fieldcount" value="<%= i %>">
<input type="hidden" name="table" value="<%= strTable %>">
<P><FONT face=""
size=2>
<INPUT id=submit2 name=submit2 style="HEIGHT: 24px; WIDTH: 79px" type=button value= "<<Back"OnClick="location.href=history.back(1)">
<INPUT id=submit1 name=submit1 style= "HEIGHT: 24px; WIDTH:84px" type=submit value="Next>>"></FONT></P>
</form>
</BODY>
</HTML>
Applying the Filter and Displaying Results
Now that we have displayed the fields, went through the field types and allowed the user to search on those fields, we need to display the results to the user. This next page provides the data display of the search. First the script takes the query data and performs two queries on this page; one is for again checking the data type so we know how to build the SQL, and the next one is where we actually take the SQL that was built and apply it so we can obtain the final record set to display to the user.
-------BEGIN CODE FOR DEFAULT3.ASP---------
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<BODY>
<%
'this function is for handling dates based upon SQL server or Access
Function SplitDates(FieldName, DateValues)
Dim TempVal, StartDate, EndDate
For i = 1 To Len(DateValues)
TempVal = Mid(DateValues, i, 1)
If TempVal = "," Then
StartDate = Mid(DateValues, 1, i-1)
EndDate = Trim(Mid(DateValues, i+1, Len(DateValues)))
End If
Next
If Trim(StartDate) = "" Then StartDate = "1/1/1800"
If Trim(EndDate) = "" Then EndDate = Date()
SplitDates = "(" & FieldName & " BETWEEN #" & StartDate & "# AND #" & EndDate & "#)"
'apostrophes for SQL # for Access on dates
End Function
Sub DisplayRecords
Dim SQLQ, strSelectFields, strCriteriaFields, blnSearchField
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open Session("DSN")
'first go through the fields and find the data type
For i = 0 To request("fieldcount")
If request("display" & i) <> "" Then
strSelectFields = strSelectFields & " " & request("display" & i) & ", " 'here where you insert the criteria
End If
'check and see if criteria was passed for this field so we can build the SQL code.
If request("criteria" & i) <> "" Then
'there is search criteria for the field so now find out the data type.
Select Case request("fieldtype" & i)
Case 3 ' number
Case 6 'currency
Case 11 'true/false
strCriteriaFields = strCriteriaFields & JoinWord & request("field" & i) & _
" = " & request("criteria" & i)
JoinWord = " AND "
blnSearchField = True
Case 135 'date
If Trim(request("criteria" & i)) <> "," Then
strCriteriaFields = strCriteriaFields & JoinWord & SplitDates(request("field" & i), request("criteria" & i))
JoinWord = " AND "
blnSearchField = True
End If
Case 200 'text
strCriteriaFields = strCriteriaFields & JoinWord & request("field" & i) & _
" LIKE '%" & Replace(request("criteria" & i), "'", "''") & "%'"
JoinWord = " AND "
blnSearchField = True
Case 201 'memo
strCriteriaFields = strCriteriaFields & JoinWord & request("field" & i) & _
" LIKE '%" & Replace(request("criteria" & i), "'", "''") & "%'"
JoinWord = " AND "
blnSearchField = True
End Select
End If
Next
If strSelectFields <> "" Then
strSelectFields = Mid(strSelectFields, 1, Len(strSelectFields)-2)
Else
strSelectFields = "*"
End If
'now take the SQL we built with the appropriate data handling and execute it to get the final recordset.
SQLQ = "SELECT " & strSelectFields & " FROM " & request("table")
If blnSearchField = True Then SQLQ = SQLQ & " WHERE " & strCriteriaFields
Response.Write SQLQ
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = DBConn
rs.CursorType = adOpenDynamic
rs.Source = SQLQ
%>
<TABLE BORDER=1 BORDERCOLOR=#CCCCCC BORDERCOLORDARK=#CCCCCC BORDERCOLORLIGHT=#CCCCCC CELLPADDING=2 CELLSPACING=0 WIDTH=100%>
<TR>
<%
'first display the field names as the table headings.
For i = 0 To rs.Fields.Count - 1
Response.Write "<TD><B>" & rs(i).Name &"</B></TD>"
Next
%></TR>
<%
rs.Open
'now display the matching rows.
If Not rs.EOF Then
Do Until rs.EOF
Response.Write "<TRvalign=top>"
For i = 0 To rs.Fields.Count - 1
Response.Write "<TD> " & rs(i) &" </TD>" & vbCrLf
Next
Response.Write"</TR>"
rs.MoveNext
Loop
End If
%></TABLE>
<P> </P>
</BODY>
</HTML>
<%
Set rs = Nothing
DBConn.Close
Set DBConn = nothing
End Sub
Call DisplayRecords
%>
Download the code!
By: Patrick Santry, Microsoft MVP (ASP/ASP.NET), developer of this site, author of books on Web technologies, and member of the DotNetNuke core development team. If you're interested in the services provided by Patrick, visit his company Website at Santry.com.