Hi Doug,
I have had a look at the code and couldn't find the error. I shall
include the entire code here, maybe you or any others can find what i
am missing.
Thanks for all your help so far.
Phill
The Code:
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"
%>
<%
ewCurSec = 0 ' Initialise
' User levels
Const ewAllowAdd = 1
Const ewAllowDelete = 2
Const ewAllowEdit = 4
Const ewAllowView = 8
Const ewAllowList = 8
Const ewAllowReport = 8
Const ewAllowSearch = 8
Const ewAllowAdmin = 16
%>
<%
' Initialize common variables
x_Job_No = Null
x_Customer = Null
x_Address = Null
x_Post_Code = Null
x_Telephone = Null
x_Mobile = Null
x_Vehicle = Null
x_Chassis_No = Null
x_Engine_No = Null
x_Raised_By = Null
x_Date = Null
x_Registration = Null
x_Mileage = Null
x_Work_Requested = Null
x_Authorised_By = Null
x_Higher_Authority_Price = Null
x_q1 = Null
x_q2 = Null
x_q3 = Null
x_q4 = Null
x_q5 = Null
x_q6 = Null
x_q7 = Null
x_q8 = Null
x_q9 = Null
x_q10 = Null
x_d1 = Null
x_d2 = Null
x_d3 = Null
x_d4 = Null
x_d5 = Null
x_d6 = Null
x_d7 = Null
x_d8 = Null
x_d9 = Null
x_d10 = Null
x_p1 = Null
x_p2 = Null
x_p3 = Null
x_p4 = Null
x_p5 = Null
x_p6 = Null
x_p7 = Null
x_p8 = Null
x_p9 = Null
x_p10 = Null
x_price1 = Null
x_price2 = Null
x_price3 = Null
x_price4 = Null
x_price5 = Null
x_price6 = Null
x_price7 = Null
x_price8 = Null
x_price9 = Null
x_price10 = Null
x_Additional_Work_Requested = Null
x_Workshop_Report = Null
x_Other_Notes = Null
x_Date_Completed = Null
%>
<%
sExport = Request.QueryString("export") ' Load Export Request
If sExport = "html" Then
' Printer Friendly
End If
If sExport = "excel" Then
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;
filename=Data.xls"
End If
If sExport = "word" Then
Response.ContentType = "application/vnd.ms-word"
Response.AddHeader "Content-Disposition:", "attachment;
filename=Data.doc"
End If
If sExport = "csv" Then
Response.ContentType = "application/csv"
Response.AddHeader "Content-Disposition:", "attachment;
filename=Data.csv"
End If
%>
<!--#include file="db.asp"-->
<!--#include file="aspmkrfn.asp"-->
<%
nStartRec = 0
nStopRec = 0
nTotalRecs = 0
nRecCount = 0
nRecActual = 0
sKeyMaster = ""
sDbWhereMaster = ""
sSrchAdvanced = ""
sSrchBasic = ""
sSrchWhere = ""
sDbWhere = ""
sDefaultOrderBy = ""
sDefaultFilter = ""
sWhere = ""
sGroupBy = ""
sHaving = ""
sOrderBy = ""
sSqlMaster = ""
nDisplayRecs = 20
nRecRange = 10
' Open connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str
' Handle Reset Command
ResetCmd()
' Get Search Criteria for Basic Search
SetUpBasicSearch()
' Build Search Criteria
If sSrchAdvanced <> "" Then
sSrchWhere = sSrchAdvanced ' Advanced Search
ElseIf sSrchBasic <> "" Then
sSrchWhere = sSrchBasic ' Basic Search
End If
' Save Search Criteria
If sSrchWhere <> "" Then
Session("Data_searchwhere") = sSrchWhere
' Reset start record counter (new search)
nStartRec = 1
Session("Data_REC") = nStartRec
Else
sSrchWhere = Session("Data_searchwhere")
End If
' Build WHERE condition
sDbWhere = ""
If sDbWhereMaster <> "" Then
sDbWhere = sDbWhere & "(" & sDbWhereMaster & ") AND "
End If
If sSrchWhere <> "" Then
sDbWhere = sDbWhere & "(" & sSrchWhere & ") AND "
End If
If Len(sDbWhere) > 5 Then
sDbWhere = Mid(sDbWhere, 1, Len(sDbWhere)-5) ' Trim rightmost AND
End If
' Build SQL
sSql = "SELECT * FROM [Data] WHERE Date_Completed is Null "
' Load Default Filter
sDefaultFilter = ""
sGroupBy = ""
sHaving = ""
' Load Default Order
sDefaultOrderBy = ""
sWhere = ""
If sDefaultFilter <> "" Then
sWhere = sWhere & "(" & sDefaultFilter & ") AND "
End If
If sDbWhere <> "" Then
sWhere = sWhere & "(" & sDbWhere & ") AND "
End If
If Right(sWhere, 5) = " AND " Then sWhere = Left(sWhere,
Len(sWhere)-5)
If sWhere <> "" Then
sSql = sSql & " WHERE " & sWhere
End If
If sGroupBy <> "" Then
sSql = sSql & " GROUP BY " & sGroupBy
End If
If sHaving <> "" Then
sSql = sSql & " HAVING " & sHaving
End If
' Set Up Sorting Order
sOrderBy = ""
SetUpSortOrder()
If sOrderBy <> "" Then
sSql = sSql & " ORDER BY " & sOrderBy
End If
'Session("ewmsg") = sSql ' Uncomment to show SQL for debugging
' Export Data only
If sExport = "xml" Or sExport = "csv" Then
Call ExportData(sExport, sSql)
conn.Close ' Close Connection
Set conn = Nothing
Response.End
End If
%>
<% If sExport <> "word" And sExport <> "excel" Then %>
<script type="text/javascript" src="ew.js"></script>
<script type="text/javascript">
<!--
EW_dateSep = "/"; // set date separator
//-->
</script>
<% End If %>
<%
' Set up Record Set
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sSql, conn, 1, 2
nTotalRecs = rs.RecordCount
If nDisplayRecs <= 0 Then ' Display All Records
nDisplayRecs = nTotalRecs
End If
nStartRec = 1
SetUpStartRec() ' Set Up Start Record Position
%><style type="text/css">
<!--
INPUT, TEXTAREA, SELECT {font-family: Verdana; font-size: xx-small;}
.aspmaker {font-family: Verdana; font-size: xx-small;}
-->
</style>
<style type="text/css">
<!--
..style1 {
font-family: Arial, Helvetica, sans-serif;
font-size: 10;
}
..style6 {
font-family: Arial, Helvetica, sans-serif;
font-size: 9px;
}
-->
</style>
<p class="style1"><span class="aspmaker style6"><img src="images/
Dastek_Logo.jpg" width="294" height="112"></span></p>
<p align="center" class="style6"><span class="aspmaker ">
<% If sExport = "" Then %>
<a href="Datalist.asp?export=html">Printer Friendly</a>
<a href="Datalist.asp?export=excel">Export to
Excel</a> <a href="Datalist.asp?export=word">Export to
Word</a> <a
href="Datalist.asp?export=csv">Export to CSV</a>
<% End If %>
</span></p>
<span class="style6">
<% If sExport = "" Then %>
</span>
<form action="Datalist.asp" class="style6">
<table border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td><span class="aspmaker">
<input type="text" name="psearch" size="20">
<input type="Submit" name="Submit" value="Search
(*)">
<a href="Datalist.asp?cmd=reset">Show all</a>
</span></td>
</tr>
<tr><td><span class="aspmaker"><input type="radio" name="psearchtype"
value="" checked>Exact phrase <input
type="radio" name="psearchtype" value="AND">All
words <input type="radio" name="psearchtype"
value="OR">Any
word</span></td></tr>
</table>
</form>
<span class="style6">
<% End If %>
<% If sExport = "" Then %>
</span>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="style6"><span class="aspmaker"><a href="Dataadd.asp">Add
New Job</a></span></td>
</tr>
</table>
<a href="FullDatalist.asp" class="aspmaker">View past Jobs
</a>
<p class="style6">
<% End If %>
<%
If Session("ewmsg") <> "" Then
%>
<p class="style6"><span class="aspmaker" style="color: Red;"><%=
Session("ewmsg") %></span></p>
<span class="style6">
<%
Session("ewmsg") = "" ' Clear message
End If
%>
</span>
<form method="post" class="style6">
<table border="0" align="center" cellpadding="4" cellspacing="1"
bgcolor="#CCCCCC">
<% If nTotalRecs > 0 Then %>
<!-- Table header -->
<tr bgcolor="#666666">
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Job No
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Job_No") %>"
style="color: #FFFFFF;">Job No (*)
<% If Session("Data_x_Job_No_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Job_No_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Customer
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Customer") %>"
style="color: #FFFFFF;">Customer (*)
<% If Session("Data_x_Customer_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Customer_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Post Code
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Post_Code") %>"
style="color: #FFFFFF;">Post Code (*)
<% If Session("Data_x_Post_Code_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Post_Code_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Telephone
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Telephone") %>"
style="color: #FFFFFF;">Telephone (*)
<% If Session("Data_x_Telephone_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Telephone_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Mobile
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Mobile") %>"
style="color: #FFFFFF;">Mobile (*)
<% If Session("Data_x_Mobile_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Mobile_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Vehicle
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Vehicle") %>"
style="color: #FFFFFF;">Vehicle (*)
<% If Session("Data_x_Vehicle_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Vehicle_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Date
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Date") %>"
style="color: #FFFFFF;">Date
<% If Session("Data_x_Date_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Date_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Registration
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Registration")
%>" style="color: #FFFFFF;">Registration (*)
<% If Session("Data_x_Registration_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Registration_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<td valign="top"><div align="center"><span class="aspmaker"
style="color: #000000;">
<% If sExport <> "" Then %>
Date Completed
<% Else %>
<a href="Datalist.asp?order=<%= Server.URLEncode("Date_Completed")
%>" style="color: #FFFFFF;">Date Completed
<% If Session("Data_x_Date_Completed_Sort") = "ASC" Then %>
<img src="images/sortup.gif" width="10" height="9" border="0">
<% ElseIf Session("Data_x_Date_Completed_Sort") = "DESC" Then %>
<img src="images/sortdown.gif" width="10" height="9" border="0">
<% End If %>
</a>
<% End If %>
</span></div></td>
<% If sExport = "" Then %>
<td><div align="center"></div></td>
<td><div align="center"></div></td>
<% End If %>
</tr>
<% End If %>
<%
' Avoid starting record > total records
If CLng(nStartRec) > CLng(nTotalRecs) Then
nStartRec = nTotalRecs
End If
' Set the last record to display
nStopRec = nStartRec + nDisplayRecs - 1
' Move to first record directly for performance reason
nRecCount = nStartRec - 1
If Not rs.Eof Then
rs.MoveFirst
rs.Move nStartRec - 1
End If
nRecActual = 0
Do While (Not rs.Eof) And (nRecCount < nStopRec)
nRecCount = nRecCount + 1
If CLng(nRecCount) >= CLng(nStartRec) Then
nRecActual = nRecActual + 1
' Set row color
sItemRowClass = " bgcolor=""#FFFFFF"""
' Display alternate color for rows
If nRecCount Mod 2 <> 0 Then
sItemRowClass = " bgcolor=""#F5F5F5"""
End If
' Load Key for record
sKey = rs("Job_No")
x_Job_No = rs("Job_No")
x_Customer = rs("Customer")
x_Address = rs("Address")
x_Post_Code = rs("Post_Code")
x_Telephone = rs("Telephone")
x_Mobile = rs("Mobile")
x_Vehicle = rs("Vehicle")
x_Chassis_No = rs("Chassis_No")
x_Engine_No = rs("Engine_No")
x_Raised_By = rs("Raised_By")
x_Date = rs("Date")
x_Registration = rs("Registration")
x_Mileage = rs("Mileage")
x_Work_Requested = rs("Work_Requested")
x_Authorised_By = rs("Authorised_By")
x_Higher_Authority_Price = rs("Higher_Authority_Price")
x_q1 = rs("q1")
x_q2 = rs("q2")
x_q3 = rs("q3")
x_q4 = rs("q4")
x_q5 = rs("q5")
x_q6 = rs("q6")
x_q7 = rs("q7")
x_q8 = rs("q8")
x_q9 = rs("q9")
x_q10 = rs("q10")
x_d1 = rs("d1")
x_d2 = rs("d2")
x_d3 = rs("d3")
x_d4 = rs("d4")
x_d5 = rs("d5")
x_d6 = rs("d6")
x_d7 = rs("d7")
x_d8 = rs("d8")
x_d9 = rs("d9")
x_d10 = rs("d10")
x_p1 = rs("p1")
x_p2 = rs("p2")
x_p3 = rs("p3")
x_p4 = rs("p4")
x_p5 = rs("p5")
x_p6 = rs("p6")
x_p7 = rs("p7")
x_p8 = rs("p8")
x_p9 = rs("p9")
x_p10 = rs("p10")
x_price1 = rs("price1")
x_price2 = rs("price2")
x_price3 = rs("price3")
x_price4 = rs("price4")
x_price5 = rs("price5")
x_price6 = rs("price6")
x_price7 = rs("price7")
x_price8 = rs("price8")
x_price9 = rs("price9")
x_price10 = rs("price10")
x_Additional_Work_Requested = rs("Additional_Work_Requested")
x_Workshop_Report = rs("Workshop_Report")
x_Other_Notes = rs("Other_Notes")
x_Date_Completed = rs("Date_Completed")
%>
<!-- Table body -->
<tr<%=sItemRowClass%>>
<!-- Job_No -->
<td><div align="center"><span class="aspmaker ">
<% Response.Write x_Job_No %>
</span></div></td>
<!-- Customer -->
<td><div align="center"><span class="aspmaker ">
<% Response.Write x_Customer %>
</span></div></td>
<!-- Post_Code -->
<td><div align="center"><span class="aspmaker ">
<% Response.Write x_Post_Code %>
</span></div></td>
<!-- Telephone -->
<td><div align="center"><span class="aspmaker ">
<% Response.Write x_Telephone %>
</span></div></td>
<!-- Mobile -->
<td><div align="center"><span class="aspmaker ">
<% Response.Write x_Mobile %>
</span></div></td>
<!-- Vehicle -->
<td><div align="center"><span class="aspmaker ">
<% Response.Write x_Vehicle %>
</span></div></td>
<!-- Date -->
<td><div align="center"><span class="aspmaker ">
<% If IsDate(x_Date) Then Response.Write EW_FormatDateTime(x_Date,7)
Else Response.Write x_Date End If %>
</span></div></td>
<!-- Registration -->
<td><div align="center"><span class="aspmaker ">
<% Response.Write x_Registration %>
</span></div></td>
<!-- Date_Completed -->
<td><div align="center"><span class="aspmaker ">
<% If IsDate(x_Date_Completed) Then Response.Write
EW_FormatDateTime(x_Date_Completed,7) Else Response.Write
x_Date_Completed End If %>
</span></div></td>
<% If sExport = "" Then %>
<td><div align="center"><span class="aspmaker "><a href="<% If Not
IsNull(sKey) Then Response.Write "Dataview.asp?key=" &
Server.URLEncode(sKey) Else Response.Write "javascript:alert('Invalid
Record! Key is null');" End If
%>">View</a></span></div></td>
<td><div align="center"><span class="aspmaker "><a href="<% If Not
IsNull(sKey) Then Response.Write "Dataedit.asp?key=" &
Server.URLEncode(sKey) Else Response.Write "javascript:alert('Invalid
Record! Key is null');" End If
%>">Edit</a></span></div></td>
<% End If %>
</tr>
<%
End If
rs.MoveNext
Loop
%>
</table>
</form>
<span class="style6">
<%
' Close recordset and connection
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
<% If sExport = "" Then %>
</span>
<form action="Datalist.asp" name="ewpagerform" class="style6"
id="ewpagerform">
<table bgcolor="" border="0" cellspacing="1" cellpadding="4"
bgcolor="#CCCCCC">
<tr>
<td nowrap>
<%
If nTotalRecs > 0 Then
rsEof = (nTotalRecs < (nStartRec + nDisplayRecs))
PrevStart = nStartRec - nDisplayRecs
If PrevStart < 1 Then PrevStart = 1
NextStart = nStartRec + nDisplayRecs
If NextStart > nTotalRecs Then NextStart = nStartRec
LastStart = ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1
%>
<table border="0" cellspacing="0" cellpadding="0"><tr><td><span
class="aspmaker">Page </span></td>
<!--first page button-->
<% If CLng(nStartRec)=1 Then %>
<td><img src="images/firstdisab.gif" alt="First" width="16"
height="16" border="0"></td>
<% Else %>
<td><a href="Datalist.asp?start=1"><img src="images/first.gif"
alt="First" width="16" height="16"
border="0"></a></td>
<% End If %>
<!--previous page button-->
<% If CLng(PrevStart) = CLng(nStartRec) Then %>
<td><img src="images/prevdisab.gif" alt="Previous" width="16"
height="16" border="0"></td>
<% Else %>
<td><a href="Datalist.asp?start=<%=PrevStart%>"><img src="images/
prev.gif" alt="Previous" width="16" height="16"
border="0"></a></td>
<% End If %>
<!--current page number-->
<td><input type="text" name="pageno" value="<
%=(nStartRec-1)\nDisplayRecs+1%>" size="4"></td>
<!--next page button-->
<% If CLng(NextStart) = CLng(nStartRec) Then %>
<td><img src="images/nextdisab.gif" alt="Next" width="16" height="16"
border="0"></td>
<% Else %>
<td><a href="Datalist.asp?start=<%=NextStart%>"><img src="images/
next.gif" alt="Next" width="16" height="16"
border="0"></a></td>
<% End If %>
<!--last page button-->
<% If CLng(LastStart) = CLng(nStartRec) Then %>
<td><img src="images/lastdisab.gif" alt="Last" width="16" height="16"
border="0"></td>
<% Else %>
<td><a href="Datalist.asp?start=<%=LastStart%>"><img src="images/
last.gif" alt="Last" width="16" height="16"
border="0"></a></td>
<% End If %>
<td><span class="aspmaker"> of <%=(nTotalRecs-1)\nDisplayRecs
+1%></span></td>
</tr></table>
<% If CLng(nStartRec) > CLng(nTotalRecs) Then nStartRec = nTotalRecs
nStopRec = nStartRec + nDisplayRecs - 1
nRecCount = nTotalRecs - 1
If rsEOF Then nRecCount = nTotalRecs
If nStopRec > nRecCount Then nStopRec = nRecCount %>
<span class="aspmaker">Records <%= nStartRec %> to <%= nStopRec %> of
<%= nTotalRecs %></span>
<% Else %>
<span class="aspmaker">No records found</span>
<% End If %>
</td>
</tr>
</table>
</form>
<span class="style1">
<% End If %>
<% If sExport <> "word" And sExport <> "excel" Then %>
</span>
<!--#include file="footer.asp"-->
<% End If %>
<%
'-------------------------------------------------------------------------------
' Function BasicSearchSQL
' - Build WHERE clause for a keyword
Function BasicSearchSQL(Keyword)
Dim sKeyword
sKeyword = AdjustSql(Keyword)
BasicSearchSQL = ""
If IsNumeric(sKeyword) Then BasicSearchSQL = BasicSearchSQL &
"[Job_No] = " & sKeyword & " OR "
BasicSearchSQL = BasicSearchSQL & "[Customer] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Address] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Post_Code] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Telephone] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Mobile] LIKE '%" & sKeyword & "%'
OR "
BasicSearchSQL = BasicSearchSQL & "[Vehicle] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Chassis_No] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Engine_No] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Raised_By] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Registration] LIKE '%" & sKeyword
& "%' OR "
BasicSearchSQL = BasicSearchSQL & "[Mileage] LIKE '%" & sKeyword &
"%' OR "
BasicSearchSQL = BasicSearchSQL & "[Work_Requested] LIKE '%" &
sKeyword & "%' OR "
BasicSearchSQL = BasicSearchSQL & "[Authorised_By] LIKE '%" &
sKeyword & "%' OR "
BasicSearchSQL = BasicSearchSQL & "[Higher_Authority_Price] LIKE '%"
& sKeyword & "%' OR "
BasicSearchSQL = BasicSearchSQL & "[d1] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d2] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d3] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d4] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d5] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d6] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d7] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d8] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d9] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[d10] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p1] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p2] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p3] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p4] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p5] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p6] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p7] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p8] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p9] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[p10] LIKE '%" & sKeyword & "%' OR
"
BasicSearchSQL = BasicSearchSQL & "[Additional_Work_Requested] LIKE
'%" & sKeyword & "%' OR "
BasicSearchSQL = BasicSearchSQL & "[Workshop_Report] LIKE '%" &
sKeyword & "%' OR "
BasicSearchSQL = BasicSearchSQL & "[Other_Notes] LIKE '%" & sKeyword
& "%' OR "
BasicSearchSQL = BasicSearchSQL & "[Date_COmpleted] LIKE '%" &
sKeyword & "%' OR "
If Right(BasicSearchSQL, 4) = " OR " Then BasicSearchSQL =
Left(BasicSearchSQL, Len(BasicSearchSQL)-4)
End Function
'-------------------------------------------------------------------------------
' Function SetUpBasicSearch
' - Set up Basic Search parameter based on form elements pSearch &
pSearchType
' - Variables setup: sSrchBasic
Sub SetUpBasicSearch()
Dim sSearch, sSearchType, arKeyword, sKeyword
sSearch = Request.QueryString("psearch")
sSearchType = Request.QueryString("psearchType")
If sSearch <> "" Then
If sSearchType <> "" Then
While InStr(sSearch, " ") > 0
sSearch = Replace(sSearch, " ", " ")
Wend
arKeyword = Split(Trim(sSearch), " ")
For Each sKeyword In arKeyword
sSrchBasic = sSrchBasic & "(" & BasicSearchSQL(sKeyword) & ") " &
sSearchType & " "
Next
Else
sSrchBasic = BasicSearchSQL(sSearch)
End If
End If
If Right(sSrchBasic, 4) = " OR " Then sSrchBasic = Left(sSrchBasic,
Len(sSrchBasic)-4)
If Right(sSrchBasic, 5) = " AND " Then sSrchBasic = Left(sSrchBasic,
Len(sSrchBasic)-5)
End Sub
'-------------------------------------------------------------------------------
' Function SetUpSortOrder
' - Set up Sort parameters based on Sort Links clicked
' - Variables setup: sOrderBy, Session("Table_OrderBy"),
Session("Table_Field_Sort")
Sub SetUpSortOrder()
Dim sOrder, sSortField, sLastSort, sThisSort
Dim bCtrl
' Check for an Order parameter
If Request.QueryString("order").Count > 0 Then
sOrder = Request.QueryString("order")
' Field Job_No
If sOrder = "Job_No" Then
sSortField = "[Job_No]"
sLastSort = Session("Data_x_Job_No_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Job_No_Sort") = sThisSort
Else
If Session("Data_x_Job_No_Sort") <> "" Then
Session("Data_x_Job_No_Sort") = ""
End If
' Field Customer
If sOrder = "Customer" Then
sSortField = "[Customer]"
sLastSort = Session("Data_x_Customer_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Customer_Sort") = sThisSort
Else
If Session("Data_x_Customer_Sort") <> "" Then
Session("Data_x_Customer_Sort") = ""
End If
' Field Post_Code
If sOrder = "Post_Code" Then
sSortField = "[Post_Code]"
sLastSort = Session("Data_x_Post_Code_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Post_Code_Sort") = sThisSort
Else
If Session("Data_x_Post_Code_Sort") <> "" Then
Session("Data_x_Post_Code_Sort") = ""
End If
' Field Telephone
If sOrder = "Telephone" Then
sSortField = "[Telephone]"
sLastSort = Session("Data_x_Telephone_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Telephone_Sort") = sThisSort
Else
If Session("Data_x_Telephone_Sort") <> "" Then
Session("Data_x_Telephone_Sort") = ""
End If
' Field Mobile
If sOrder = "Mobile" Then
sSortField = "[Mobile]"
sLastSort = Session("Data_x_Mobile_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Mobile_Sort") = sThisSort
Else
If Session("Data_x_Mobile_Sort") <> "" Then
Session("Data_x_Mobile_Sort") = ""
End If
' Field Vehicle
If sOrder = "Vehicle" Then
sSortField = "[Vehicle]"
sLastSort = Session("Data_x_Vehicle_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Vehicle_Sort") = sThisSort
Else
If Session("Data_x_Vehicle_Sort") <> "" Then
Session("Data_x_Vehicle_Sort") = ""
End If
' Field Date
If sOrder = "Date" Then
sSortField = "[Date]"
sLastSort = Session("Data_x_Date_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Date_Sort") = sThisSort
Else
If Session("Data_x_Date_Sort") <> "" Then
Session("Data_x_Date_Sort") = ""
End If
' Field Registration
If sOrder = "Registration" Then
sSortField = "[Registration]"
sLastSort = Session("Data_x_Registration_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Registration_Sort") = sThisSort
Else
If Session("Data_x_Registration_Sort") <> "" Then
Session("Data_x_Registration_Sort") = ""
End If
' Field Date_Completed
If sOrder = "Date_Completed" Then
sSortField = "[Date_Completed]"
sLastSort = Session("Data_x_Date_Completed_Sort")
If sLastSort = "ASC" Then sThisSort = "DESC" Else sThisSort = "ASC"
Session("Data_x_Date_Completed_Sort") = sThisSort
Else
If Session("Data_x_Date_Completed_Sort") <> "" Then
Session("Data_x_Date_Completed_Sort") = ""
End If
Session("Data_OrderBy") = sSortField & " " & sThisSort
Session("Data_REC") = 1
End If
sOrderBy = Session("Data_OrderBy")
If sOrderBy = "" Then
sOrderBy = sDefaultOrderBy
Session("Data_OrderBy") = sOrderBy
End If
End Sub
'-------------------------------------------------------------------------------
' Function SetUpStartRec
' - Set up Starting Record parameters based on Pager Navigation
' - Variables setup: nStartRec
Sub SetUpStartRec()
Dim nPageNo
' Check for a START parameter
If Request.QueryString("start").Count > 0 Then
nStartRec = Request.QueryString("start")
Session("Data_REC") = nStartRec
ElseIf Request.QueryString("pageno").Count > 0 Then
nPageNo = Request.QueryString("pageno")
If IsNumeric(nPageNo) Then
nStartRec = (nPageNo-1)*nDisplayRecs+1
If nStartRec <= 0 Then
nStartRec = 1
ElseIf nStartRec >= ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1
Then
nStartRec = ((nTotalRecs-1)\nDisplayRecs)*nDisplayRecs+1
End If
Session("Data_REC") = nStartRec
Else
nStartRec = Session("Data_REC")
If Not IsNumeric(nStartRec) Or nStartRec = "" Then
nStartRec = 1 ' Reset start record counter
Session("Data_REC") = nStartRec
End If
End If
Else
nStartRec = Session("Data_REC")
If Not IsNumeric(nStartRec) Or nStartRec = "" Then
nStartRec = 1 'Reset start record counter
Session("Data_REC") = nStartRec
End If
End If
End Sub
'-------------------------------------------------------------------------------
' Function ResetCmd
' - Clear list page parameters
' - RESET: reset search parameters
' - RESETALL: reset search & master/detail parameters
' - RESETSORT: reset sort parameters
Sub ResetCmd()
Dim sCmd
' Get Reset Cmd
If Request.QueryString("cmd").Count > 0 Then
sCmd = Request.QueryString("cmd")
' Reset Search Criteria
If UCase(sCmd) = "RESET" Then
sSrchWhere = ""
Session("Data_searchwhere") = sSrchWhere
' Reset Search Criteria & Session Keys
ElseIf UCase(sCmd) = "RESETALL" Then
sSrchWhere = ""
Session("Data_searchwhere") = sSrchWhere
' Reset Sort Criteria
ElseIf UCase(sCmd) = "RESETSORT" Then
sOrderBy = ""
Session("Data_OrderBy") = sOrderBy
If Session("Data_x_Job_No_Sort") <> "" Then
Session("Data_x_Job_No_Sort") = ""
If Session("Data_x_Customer_Sort") <> "" Then
Session("Data_x_Customer_Sort") = ""
If Session("Data_x_Post_Code_Sort") <> "" Then
Session("Data_x_Post_Code_Sort") = ""
If Session("Data_x_Telephone_Sort") <> "" Then
Session("Data_x_Telephone_Sort") = ""
If Session("Data_x_Mobile_Sort") <> "" Then
Session("Data_x_Mobile_Sort") = ""
If Session("Data_x_Vehicle_Sort") <> "" Then
Session("Data_x_Vehicle_Sort") = ""
If Session("Data_x_Date_Sort") <> "" Then
Session("Data_x_Date_Sort") = ""
If Session("Data_x_Registration_Sort") <> "" Then
Session("Data_x_Registration_Sort") = ""
If Session("Data_x_Date_Completed_Sort") <> "" Then
Session("Data_x_Date_Completed_Sort") = ""
End If
' Reset Start Position (Reset Command)
nStartRec = 1
Session("Data_REC") = nStartRec
End If
End Sub
'-------------------------------------------------------------------------------
' Function ExportData
' - Export Data in Xml or Csv format
Sub ExportData(sExport, sSql)
Dim oXmlDoc, oXmlTbl, oXmlRec, oXmlFld
Dim sCsvStr
Dim rs
' Set up Record Set
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sSql, conn, 1, 2
nTotalRecs = rs.RecordCount
nStartRec = 1
SetUpStartRec() ' Set Up Start Record Position
If sExport = "csv" Then
sCsvStr = sCsvStr & """Job_No""" & ","
sCsvStr = sCsvStr & """Customer""" & ","
sCsvStr = sCsvStr & """Post_Code""" & ","
sCsvStr = sCsvStr & """Telephone""" & ","
sCsvStr = sCsvStr & """Mobile""" & ","
sCsvStr = sCsvStr & """Vehicle""" & ","
sCsvStr = sCsvStr & """Date""" & ","
sCsvStr = sCsvStr & """Registration""" & ","
sCsvStr = sCsvStr & """Date_Completed""" & ","
sCsvStr = Left(sCsvStr, Len(sCsvStr)-1) ' Remove last comma
sCsvStr = sCsvStr & vbCrLf
End If
' Avoid starting record > total records
If CLng(nStartRec) > CLng(nTotalRecs) Then
nStartRec = nTotalRecs
End If
' Set the last record to display
If nDisplayRecs < 0 Then
nStopRec = nTotalRecs
Else
nStopRec = nStartRec + nDisplayRecs - 1
End If
' Move to first record directly for performance reason
nRecCount = nStartRec - 1
If Not rs.Eof Then
rs.MoveFirst
rs.Move nStartRec - 1
End If
nRecActual = 0
Do While (Not rs.Eof) And (nRecCount < nStopRec)
nRecCount = nRecCount + 1
If CLng(nRecCount) >= CLng(nStartRec) Then
nRecActual = nRecActual + 1
x_Job_No = rs("Job_No")
x_Customer = rs("Customer")
x_Post_Code = rs("Post_Code")
x_Telephone = rs("Telephone")
x_Mobile = rs("Mobile")
x_Vehicle = rs("Vehicle")
x_Date = rs("Date")
x_Registration = rs("Registration")
x_Date_Completed = rs("Date_Completed")
If sExport = "csv" Then
' Field Job_No
sCsvStr = sCsvStr & """" & Replace(x_Job_No&"","""","""""") & """"
& ","
' Field Customer
sCsvStr = sCsvStr & """" & Replace(x_Customer&"","""","""""") &
"""" & ","
' Field Post_Code
sCsvStr = sCsvStr & """" & Replace(x_Post_Code&"","""","""""") &
"""" & ","
' Field Telephone
sCsvStr = sCsvStr & """" & Replace(x_Telephone&"","""","""""") &
"""" & ","
' Field Mobile
sCsvStr = sCsvStr & """" & Replace(x_Mobile&"","""","""""") & """"
& ","
' Field Vehicle
sCsvStr = sCsvStr & """" & Replace(x_Vehicle&"","""","""""") &
"""" & ","
' Field Date
sCsvStr = sCsvStr & """" & Replace(x_Date&"","""","""""") & """" &
","
' Field Registration
sCsvStr = sCsvStr & """" & Replace(x_Registration&"","""","""""")
& """" & ","
' Field Date_Completed
sCsvStr = sCsvStr & """" &
Replace(x_Date_Completed&"","""","""""") & """" & ","
sCsvStr = Left(sCsvStr, Len(sCsvStr)-1) ' Remove last comma
sCsvStr = sCsvStr & vbCrLf
End If
End If
rs.MoveNext
Loop
' Close recordset and connection
rs.Close
Set rs = Nothing
If sExport = "csv" Then
Response.Write sCsvStr
End If
End Sub
%>
How does sSql get assigned? It looks as though rather than
'Date_Completed is NULL WHERE (([Customer] LIKE '%dave%' OR [Address] LIKE
'%dave%' OR [Post_Code] LIKE '%dave%' OR [Telephone] LIKE '%dave%' OR
[Mobile] LIKE '%dave%' OR [Vehicle] LIKE '%dave%' OR [Chassis_No] LIKE
'%dave%' OR [Engine_No] LIKE '%dave%' O'.
it should be
'Date_Completed is NULL AND (([Customer] LIKE '%dave%' OR [Address] LIKE
'%dave%' OR [Post_Code] LIKE '%dave%' OR [Telephone] LIKE '%dave%' OR
[Mobile] LIKE '%dave%' OR [Vehicle] LIKE '%dave%' OR [Chassis_No] LIKE
'%dave%' OR [Engine_No] LIKE '%dave%' O'.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
I hope that one of you may be able to help me, I am new to Access
queries and ASP and have inherited a project with a problem.
I get the following error:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'Date_Completed is
NULL WHERE (([Customer] LIKE '%dave%' OR [Address] LIKE '%dave%' OR
[Post_Code] LIKE '%dave%' OR [Telephone] LIKE '%dave%' OR [Mobile]
LIKE '%dave%' OR [Vehicle] LIKE '%dave%' OR [Chassis_No] LIKE '%dave%'
OR [Engine_No] LIKE '%dave%' O'.
/Jobsheet/Datalist.asp, line 227
' Set up Record Set
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sSql, conn, 1, 2 -- This line in particular----
nTotalRecs = rs.RecordCount
If nDisplayRecs <= 0 Then ' Display All Records
nDisplayRecs = nTotalRecs
End If
nStartRec = 1
SetUpStartRec() ' Set Up Start Record Position
%>
Any help would be much appreciated.
Phill- Hide quoted text -
- Show quoted text -
