ne**********@gmail.com wrote:
can you post the whole sql string?
looks like there is a where criterion before the where clause...
<%
'Constants declared
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001
Dim PAGE_SIZE
PAGE_SIZE = dbRecordsPerPage 'The size of our pages.
'Variable Declaration.
Dim strURL ' The URL of this page so the form will work
Dim rstSearch ' ADO recordset
Dim reqname ' The Course text being looked for
Dim reqCategory ' The Category text being looked for
Dim iPageCurrent ' The page we're currently on
Dim iPageCount ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I ' Standard looping variable
Dim blnWhere
Dim cnt
blnWhere = False
' Retreive the URL of this page from Server Variables
strURL = Pub_Server_Name & request.ServerVariables("HTTP_HOST") &
request.ServerVariables("URL")
' Retreive the term being searched for.
reqname = Request.QueryString("txtname")
reqname = Replace(reqname, "'", "''")
if IsDate(reqname) then
reqname = reqname
else
reqname = ""
end if
' Retreive the term being searched for.
reqcategory = Request.QueryString("txtcategory")
reqcategory = Replace(reqcategory, "'", "''")
' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
'Build dynamic sql.
sql = "select * from tbl_TimeLimit "
'--Name (partial and exact search)
'If Not IsEmpty(reqname) Then
if reqname<>"" then
Dim strName
strName = Trim(reqname)
If strName <"" Then
'Test for WHERE
If blnWhere Then sql = sql & " AND " Else sql = sql & " WHERE " :
blnWhere = True
sql = sql & "LimitDate LIKE #" & strName & "# "
End If
End If
'--Name (partial and exact search)
'If Not IsEmpty(reqcategory) Then
if reqcategory<>"" then
strName = Trim(reqcategory)
If strName <"" Then
'Test for WHERE
sql = sql & " WHERE skill in (select skill from tbl_timelimit where "
: blnWhere = True
If (Left(strName, 1) = "*" And Len(strName) 1) Then 'Partial search
sql = sql & "skill LIKE '%" & Replace(Mid(strName, 2), "'", "''") &
"' "
ElseIf (Right(strName, 1) = "*" And Len(strName) 1) Then 'Partial
search
sql = sql & "skill LIKE '" & Replace(Mid(strName, 1,
Len(strName)-1), "'", "''") & "%' "
Else 'Exact match
sql = sql & "skill LIKE '%" & Replace(strName, "'", "''") & "%' "
End If
sql = sql & ") "
End If
End If
If blnWhere Then sql = sql & " AND " Else sql = sql & " WHERE " :
blnWhere = True
sql = sql & "LimitDate >= #" & date & "# "
sql = sql & " ORDER BY LimitDate ASC"
session("tmpSqlExcel")= sql
' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = Server.CreateObject("ADODB.Recordset")
rstSearch.PageSize = PAGE_SIZE
rstSearch.CacheSize = PAGE_SIZE
' Open our recordset
rstSearch.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
'Response.Write sql & "<BR>"
' Get a count of the number of records and pages
' for use in building the header and footer text.
iRecordCount = rstSearch.RecordCount
iPageCount = rstSearch.PageCount
Dim iPageMax
Dim iPageMin
Dim iPageOffSet
iPageOffSet = 5
if iPageCurrent < 5 then
iPageOffSet = 5 + (5-iPageCurrent)
end if
if (iPageCurrent + iPageOffSet) iPageCount then
iPageMax = iPageCurrent + (iPageCount - iPageCurrent)
else
iPageMax = iPageCurrent + iPageOffSet
end if
if (iPageCurrent - iPageOffSet) < 1 then
iPageMin = 1
else
if (iPageCurrent + iPageOffSet) iPageCount then
iPageMin = ((iPageCount - (iPageOffSet + iPageOffSet )) + 1)
else
iPageMin = (iPageCurrent - iPageOffSet) + 1
end if
end if
if iPageMin < 1 then iPageMin = 1
if iPageMax iPageCount then iPageMax = iPageCount
If iRecordCount = 0 Then
' Display no records error.
Else
'Move to the page we need to show.
rstSearch.AbsolutePage = iPageCurrent
End if
' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
%>
