Syntax error (missing operator) in query expression

Discussion in 'ASP .Net' started by D, Jul 18, 2006.

  1. D

    D Guest

    I think this is a simple fix, but a second set of eyes could certainly
    help. Here is the error in it's entirety followed by my search criteria
    code. Any suggestions?

    Microsoft JET Database Engine error '80040e14'

    Syntax error (missing operator) in query expression 'LimitDate LIKE
    #7/18# WHERE skill in (select skill from tbl_timelimit where skill LIKE
    '%sales%' ) AND LimitDate >= #7/18/2006#'.

    /tier1overtime/overtime/TimeList.asp, line 220

    '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
     
    D, Jul 18, 2006
    #1
    1. Advertising

  2. D

    Guest

    can you post the whole sql string?

    looks like there is a where criterion before the where clause...


    D wrote:
    > I think this is a simple fix, but a second set of eyes could certainly
    > help. Here is the error in it's entirety followed by my search criteria
    > code. Any suggestions?
    >
    > Microsoft JET Database Engine error '80040e14'
    >
    > Syntax error (missing operator) in query expression 'LimitDate LIKE
    > #7/18# WHERE skill in (select skill from tbl_timelimit where skill LIKE
    > '%sales%' ) AND LimitDate >= #7/18/2006#'.
    >
    > /tier1overtime/overtime/TimeList.asp, line 220
    >
    > '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
     
    , Jul 18, 2006
    #2
    1. Advertising

  3. D

    D Guest

    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.

    %>
     
    D, Jul 18, 2006
    #3
  4. D

    Guest

    just the resultant sql string please


    D wrote:
    > 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.
    >
    > %>
     
    , Jul 18, 2006
    #4
  5. D

    D Guest

    Believe it or not Neil just your posting helped to jog my brain cells
    again. Thanks. I resolved this issue :) You were correct I had my
    criteria before my clause. Changed and working.
     
    D, Jul 18, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. D
    Replies:
    0
    Views:
    2,238
  2. alexz
    Replies:
    0
    Views:
    117
    alexz
    Jul 11, 2003
  3. Maciej Paras
    Replies:
    1
    Views:
    160
    Bob Barrows
    Oct 30, 2003
  4. shank
    Replies:
    26
    Views:
    383
    Bob Barrows [MVP]
    Aug 9, 2004
  5. Replies:
    4
    Views:
    201
    Bob Barrows
    Sep 30, 2005
Loading...

Share This Page