<% Dim DataConn, cmdDC, rsDC Dim Item Dim iFieldCount, iLoopVar Dim strLTorGT, iCriteria, iCriteria2, iCriteria3, iCriteria4, strSortBy, strOrder ' Retrieve QueryString Variables and convert them to a usable form strLTorGT = Request.QueryString("LTorGT") Select Case strLTorGT Case "GT" strLTorGT = ">" Case "EQ" strLTorGT = "=" Case "NE" strLTorGT = "<>" Case else strLTorGT = "<" End Select iCriteria = Request.QueryString("criteria") If IsNumeric(iCriteria) Then iCriteria = CLng(iCriteria) Else iCriteria = Request.QueryString("criteria") End If iCriteria2 = Request.QueryString("criteria2") If IsNumeric(iCriteria2) Then iCriteria2 = "" Else iCriteria2 = Request.QueryString("criteria2") End If iCriteria3 = Request.QueryString("criteria3") If IsNumeric(iCriteria3) Then iCriteria3 = "" Else iCriteria3 = Request.QueryString("criteria3") End If iCriteria4 = Request.QueryString("criteria4") If IsNumeric(iCriteria4) Then iCriteria4 = "" Else iCriteria4 = Request.QueryString("criteria4") End If strSortBy = Request.QueryString("sortby") If strSortBy = "" Then strSortBy = "name" strOrder = Request.QueryString("order") ' Finally we've got all our info, now to the cool stuff ' Create and establish data connection Set DataConn = Server.CreateObject("ADODB.Connection") DataConn.ConnectionTimeout = 15 DataConn.CommandTimeout = 30 'Access connection code DataConn.Open "DBQ=" & Server.MapPath("FamHist.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=30;", "username", "password" 'Our SQL Server code - use above line to use sample on your server 'DataConn.Open Application("SQLConnString"), Application("SQLUsername"), Application("SQLPassword") ' Create and link command object to data connection then set attributes and SQL query Set cmdDC = Server.CreateObject("ADODB.Command") cmdDC.ActiveConnection = DataConn cmdDC.CommandText ="SELECT name,dob,place,mother,father,died,dplace,spouse,married,mplace,ref_occ FROM parsonage WHERE name like '%" & iCriteria2 & "%' and mother like '%" & iCriteria4 & "%' and place like '%" & iCriteria3 & "%' and dob " & strLTorGT & " " & iCriteria & " ORDER BY " & strSortBy & " " & strOrder & ";" cmdDC.CommandType = 1 ' Create recordset and retrieve values using command object Set rsDC = Server.CreateObject("ADODB.Recordset") ' Opening record set with a forward-only cursor (the 0) and in read-only mode (the 1) rsDC.Open cmdDC, , 0, 1 %> PARSONAGE QUERY RESULTS
Back

Results of Query :

<% For Each Item in rsDC.Fields %> <% Next %> <% ' Loop through recordset and display results If Not rsDC.EOF Then rsDC.MoveFirst ' Get the number of fields and subtract one so our loops start at 0 iFieldCount = rsDC.Fields.Count - 1 ' Continue till we get to the end, and while in each loop through fields Do While Not rsDC.EOF %> <% For iLoopVar = 0 to iFieldCount %> <% Next %> <% rsDC.MoveNext Loop %>

<%= Item.Name %>
<%= rsDC.Fields(iLoopVar) %>

<% ' Close Data Access Objects and free DB variables rsDC.Close Set rsDC = Nothing ' can't do a "cmdDC.Close" ! Set cmdDC = Nothing DataConn.Close Set DataConn = Nothing %> Build your own query - HERE - Just change the details below :

Name:

Date of Birth: Less Than Equal To Not Equal To Greater Than

Place :

Mother :

Sort By: Name DOB Place Mother Father Died Ascending Descending