Forum Moderators: open
Could someone point me in the right direction?
The code below works great BUT . . .
My problems started when I tried to pass a QueryString from
a Form to an SQL statement, which should then fire an Access database.
On a page called: customer.asp the Form passes the querystring to a page called: search.asp - but it is not reaching the SQL.
The querystring is going over like this (fake URL)
[myxxxxx.co.uk...] Model=Focus&County=Essex&B1=Submit
On the page that contains the SQL is a
<!--#Include file="MyDatabase.asp"-->
I have tried many ways to get this to work including placing '" & strsearchtxt & "'" at the end of the Select statement.
MyDatabase.Query = "SELECT * FROM Vehicles WHERE Model='Model' AND County='County '" & strsearchtxt & "'"
I have included both sets of code below.
Any help would be really apprciated
Thanks in advance - Stephen
--------------------
Customer.asp
--------------------
<form method="GET" FORM ACTION="<% Response.Write "search.asp?Formvalue=" & Request.Form("FormValue") %>"
<p><select size="1" name="Model">
<option value="Focus">Focus</option>
<option value="323">323</option>
<option value="Escort">Escort</option>
</select><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
----------------------
Search.asp
---------------------
<%Option Explicit%>
<html>
<head>
<link rel="stylesheet" type="text/css" href="external.css">
<title>Q</title>
</head>
<body topmargin="0" leftmargin="0">
<!--#Include file="MyDatabase.asp"-->
<div align="center">
<center>
<table border="1" cellpadding="0" cellspacing="0" width="100%" bordercolor="#FF0000">
<tr>
<td width="100%">
<div align="center">
<table border="1" cellpadding="0" cellspacing="0" width="90%" bordercolor="#0000FF">
<tr>
<td width="8%" align="left">
<h6>\\\\</h6>
</td>
<td width="79%" align="center">
<h6>zz</h6>
<p> </p>
<p class="h6navy">zz</p>
</td>
<td width="24%" align="right" valign="top">
</td>
</tr>
<tr>
<td width="8%" align="left">
zzzzz
<p> </p>
</td>
<td width="79%" align="center" valign="top">
<%
Dim MyDatabase
Set MyDatabase = New Database
MyDatabase.Query = "SELECT * FROM Vehicles WHERE Model='Model'"
MyDatabase.RecordsPerPage = 10
MyDatabase.RowColor = "#99CCFF"
MyDatabase.AlternateRowColor = "#FF66CC"
MyDatabase.CellPadding = 3
MyDatabase.CellSpacing = 0
MyDatabase.DisplayResults()
Set MyDatabase = Nothing
%>
</td>
<td width="24%" align="right" valign="top">
<h6>ZZZZZzz</h6>
</td>
</tr>
<tr>
<td width="8%" align="left"> </td>
<td width="79%" align="center">
<p> </p>
<h1> </h1>
<h6>QQQQQ</h6>
</td>
<td width="24%" align="right" valign="top"> </td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</center>
</div>
</body>
</html>
----------------------
MyDatabase.asp
---------------------
<%
Class Database
'internal variables
Dim i_objConn
Dim i_objRS
Dim i_query
Dim i_Output
Dim i_StartingRecord
Dim i_EndingRecord
Dim i_RecordsPerPage
Dim i_TotalRecords
Dim i_TotalPages
Dim i_CurrentPage
Dim i_Titles
Dim i_AlternateRowColor
Dim i_RowColor
Dim i_CellPadding
Dim i_CellSpacing
Private Sub Class_Initialize()
Dim i_dbConnection
Dim intTries
Const MAX_TRIES = 10
'edit the next line to point to your database
i_dbConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/fpdb/trythis.mdb")
'get the starting record from the URL
i_StartingRecord = CInt(Request.QueryString("StartingRecord"))
'if nothing is there set it to some default
'also account for errors
If ((i_StartingRecord <= 0) Or (i_StartingRecord > 30000) Or (i_StartingRecord = "")) Then
i_StartingRecord = 1
End If
'default display table row color to use
i_RowColor = "#FFFFFF"
'default display table alternate row color to use
i_AlternateRowColor = "#EAEAEA"
'default display table cellpadding
i_CellPadding = 5
'default display table spacing
i_CellSpacing = 0
'try MAX_TRIES number of times to connect to the database
'this helps in slow connection times
On Error Resume Next
Do
Err.Clear
Set i_objConn = Server.CreateObject("ADODB.Connection")
i_objConn.Open i_dbConnection
Set i_objRS = Server.CreateObject("ADODB.Recordset")
intTries = intTries + 1
Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)
On Error Goto 0
End Sub
'#####################################
'properties you can set
Public Property Let Query(strQuery)
i_query = CStr(strQuery)
End Property
Public Property Let RecordsPerPage(intValue)
i_RecordsPerPage = CInt(intValue)
End Property
Public Property Let RowColor(strValue)
i_RowColor = CStr(strValue)
End Property
Public Property Let AlternateRowColor(strValue)
i_AlternateRowColor = CStr(strValue)
End Property
Public Property Let CellPadding(intValue)
i_CellPadding = CInt(intValue)
End Property
Public Property Let CellSpacing(intValue)
i_CellSpacing = CInt(intValue)
End Property
'######################################
Public Property Get RecordsPerPage
RecordsPerPage = i_RecordsPerPage
End Property
Public Property Get StartingRecord
StartingRecord = i_StartingRecord
End Property
Public Property Get EndingRecord
EndingRecord = i_EndingRecord
End Property
Public Property Get TotalRecords
TotalRecords = i_TotalRecords
End Property
Public Property Get CurrentPage
CurrentPage = i_CurrentPage
End Property
Public Property Get TotalPages
TotalPages = i_TotalPages
End Property
Public Property Get Output
Output = i_Output
End Property
Public Sub AddNew(table,fields,values)
Dim arrFields
Dim arrValues
Dim i
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adCmdTable = 2
i_objRS.Open table, i_objConn, adOpenKeySet, adLockOptimistic, adCmdTable
i_objRS.AddNew
For i = 0 To UBound(fields)
i_objRS(fields(i)) = values(i)
Next
i_objRS.Update
i_Output = i_objRS("ID")
End Sub
Public Sub Update(table,ID,fields,values)
Dim arrFields
Dim arrValues
Dim i
Dim strQuery
Dim strFields
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adCmdText = 1
strQuery = ""
For i = LBound(fields) to UBound(fields)
strQuery = strQuery & fields(i) & ", "
Next
strQuery = Left(strQuery, Len(strQuery) - 2)
strQuery = "select " & strQuery & " from " & table & " where ID=" & ID
i_objRS.Open strQuery, i_objConn, adOpenDynamic, adLockOptimistic, adCmdText
For i = 0 To UBound(fields)
i_objRS(fields(i)) = values(i)
Next
i_objRS.Update
End Sub
'sub that displays everything in a table
Public Sub DisplayResults()
Dim arrResults
Dim strOutput
Dim rowCounter
Dim colCounter
Dim numCols
Dim numRows
Dim strBgColor
Dim thisField
Dim showBlank
Dim showNull
'define variables
showBlank = " "
showNull = "-null-"
'default value for records per page
If i_RecordsPerPage = "" Then
i_RecordsPerPage = 5
End If
'call internal function
arrResults = GetRecordArray(i_RecordsPerPage)
'#of rows and columns
numCols = UBound(arrResults, 1)
numRows = UBound(arrResults, 2)
strOutput = "<table border=""1"" cellpadding=""" _
& i_CellPadding & """ cellspacing=""" & i_CellSpacing & """ " _
& "borderColorLight=""#666666"" borderColorDark=""#ffffff"">" _
& "<tr><td valign=""top"" colspan=""" & (numCols + 1) _
& """ align=""center"" bgcolor=""#CCCCCC"">" _
& GetRecordPositionHTML() & "</td></tr>" _
& "<tr>" & i_Titles & "</tr>"
For rowCounter = 0 To numRows
If rowCounter Mod 2 Then
strBgColor = i_AlternateRowColor
Else
strBgColor = i_RowColor
End If
strOutput = strOutput & "<tr>" & vbcrlf
For colCounter = 0 To numCols
thisField = arrResults(colCounter,rowCounter)
If IsNull(thisfield) Then
thisField = showNull
End If
If Trim(thisfield) = "" Then
thisField = showBlank
End If
strOutput = strOutput & "<td valign=""top"" bgcolor=""" & strBgColor & """>" _
& thisField & "</td>" & vbcrlf
Next
strOutput = strOutput & "</tr>" & vbcrlf
Next
If (i_TotalPages > 1) Then
strOutput = strOutput & "<tr><td valign=""top"" align=""left"" colspan=""" _
& (numCols + 1) & """ bgcolor=""#cccccc"">" & GetPagingHTML() & "</td></tr>"
End If
strOutput = strOutput & "</table>"
Response.Write(strOutput)
End Sub
'create the record position index
Public Function GetRecordPositionHTML()
Dim strOutput
strOutput = "Results " & i_StartingRecord & " - " & i_EndingRecord & " of " & i_TotalRecords
GetRecordPositionHTML = strOutput
End Function
'create the navigation links for paging
Public Function GetPagingHTML()
Dim strOutput
Dim strThisPage
Dim i
strThisPage = Request.ServerVariables("SCRIPT_NAME")
strOutput = "Result Page:"
If i_CurrentPage <> 1 Then
strOutput = strOutput & " <a href=""" & strThisPage _
& "?StartingRecord=" & ((i_CurrentPage - 1)* i_RecordsPerPage) - i_RecordsPerPage + 1 _
& """>Previous</a> "
End If
For i = 1 To i_TotalPages
If i <> i_CurrentPage Then
strOutput = strOutput & " <a href=""" & strThisPage _
& "?StartingRecord=" & (i * i_RecordsPerPage) - i_RecordsPerPage + 1 _
& """>" & i & "</a> "
Else
strOutput = strOutput & " <b>" & i & "</b> "
End If
Next
If i_CurrentPage <> i_TotalPages Then
strOutput = strOutput & " <a href=""" & strThisPage _
& "?StartingRecord=" & (i_CurrentPage * i_RecordsPerPage) + 1 & """>Next</a> "
End If
GetPagingHTML = strOutput
End Function
'returns the database records in an array
Public Function GetRecordArray(intRecordsToShow)
Dim arrLocal
Dim arrOut
Dim rowCounter
If isNull(intRecordsToShow) Then
'default number of records to return
i_RecordsPerPage = 3000
Else
i_RecordsPerPage = intRecordsToShow
End If
'-- Cursor Type, Lock Type
' ForwardOnly 0 - ReadOnly 1
' KeySet 1 - Pessimistic 2
' Dynamic 2 - Optimistic 3
' Static 3 - BatchOptimistic 4
i_objRS.CursorLocation = 3
i_objRS.Open i_query, i_objConn, 0, 1
If Err.Number <> 0 Then
Response.Write("There was an error processing your request.<br>Please try again.")
Exit Function
Else
If i_objRS.EOF and i_objRS.BOF Then
Response.Write("There are no results in the database.")
Exit Function
Else
'-- set the recordset starting position so that we can get the number
' of records we want from this point on using the GetRows() function
i_objRS.AbsolutePosition = i_StartingRecord
'-- set the pagesize through the object so we can count # of pages returned
i_objRS.PageSize = i_RecordsPerPage
'-- # of total records found
i_TotalRecords = CInt(i_objRS.RecordCount)
'-- # of current page
i_CurrentPage = CInt(i_objRS.AbsolutePage)
'-- # of total pages found
i_TotalPages = CInt(i_objRS.PageCount)
'-- just in case the starting record is set to higher than
' the total number of found records
If i_StartingRecord > i_TotalRecords Then
Response.Write("Your starting record is higher than the max results.")
Response.End
End If
'-- create a 2 simensional array of the records using GetRows()
' and only select how many records we want to see per page
Dim countField
for each countField in i_objRS.fields
i_Titles = i_Titles & "<td bgcolor=""#cccccc"">" & countField.name & "</td>" & vbcrlf
next
If isNull(intRecordsToShow) Then
arrLocal = i_objRS.GetRows()
Else
arrLocal = i_objRS.GetRows(intRecordsToShow)
End If
If IsArray(arrLocal) Then
i_EndingRecord = UBound(arrLocal, 2) + i_StartingRecord
GetRecordArray = arrLocal
Else
Response.Write("Recordset not returned as an array.")
End If
End If
End If
End Function
Private Sub Class_Terminate()
'close connection objects if they are still open
If Not i_objRS Is Nothing Then
Set i_objRS = Nothing
End If
If Not i_objConn Is Nothing Then
Set i_objConn = Nothing
End If
End Sub
End Class
%>
Mac
