[thelist] Syntax error

[Eric Stanton]

Here is the code dealing with vID:
Function GetSubCatIDs(vID)
dim rstSubCat,sSQL ,sHierarchy,iLen
dim tempID,sCriteria
Set rstSubCat = Server.CreateObject("ADODB.RecordSet")

if Instr(vId,";")> 0 then
 sSql = "Select SubCatId  From sfSub_Categories Where subcatCategoryId = " &
Cint(left(vID,len(instr(vid,";")-1))) _
 & " AND left(CatHierarchy,4)= '" & "none" & "'"
 rstSubCat.Open sSql, cnn,adOpenStatic ,adLockReadOnly , adCmdText

  vID = rstSubCat("SubCatID")
   rstSubCat.Close
end if
sSql = "Select CatHierarchy,hasprods From sfSub_Categories Where SubcatID =
" & vID
rstSubCat.Open sSql, cnn,adOpenStatic ,adLockReadOnly , adCmdText
if rstSubCat.EOF =true and rstSubCat.BOF = true then
else
  if rstSubCat("hasprods") = 1 then
   GetSubCatIDs =vID
  else
   sHierarchy = rstSubCat("CatHierarchy")
   iLen = len(sHierarchy)
   sSql = "Select SubCatID,CatHierarchy,hasprods From sfSub_Categories Where
left(CatHierarchy," & iLen & ") = '" & sHierarchy & "' AND Hasprods = 1 AND
Depth > 0"
   rstSubCat.Close
   rstSubCat.Open sSql, cnn,adOpenStatic ,adLockReadOnly , adCmdText
     if rstSubCat.EOF =true and rstSubCat.BOF = true then
      GetSubCatIDs = vID
     else
        tempID =""
        sCriteria = " OR subcatCategoryId ="
       while rstSubCat.EOF =false
         tempID = tempID & rstSubCat("SubCatID") & sCriteria
         rstSubCat.MoveNext
       wend
        tempID = left(tempID,len(tempID) - len(sCriteria))
        GetSubCatIDs = tempID
     end if
  end if
end if
if GetSubCatIDs ="" then
 GetSubCatIDs = vID
end if

----- Original Message -----
From: "Warden, Matt" <mwarden at mattwarden.com>
To: <thelist at lists.evolt.org>
Sent: Saturday, October 27, 2001 4:20 AM
Subject: Re: [thelist] Syntax error


> On Oct 27, Eric Stanton had something to say about [thelist] Syntax error
>
> >Hello List,
> >
> >I am working on a dynamic shopping cart for a customer.  Using ASP, Java,
> >Javascript.  We use a search page "search.asp" that throws variables to
> >"Search_Results.asp"  and displays records from an access DB. Search_
> >Results.asp displays the first 10 records as it should. But open click
the
> >link to retrieve the next 10 records I get the following error:
> >
> >"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> >[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in
> >query expression 'Depth = And subcatCategoryId = AND LEFT(CatHierarchy,3)
=
> >'vID''.
> >
> >/SFLib/incSearchResult.asp, line 169 "
>
> replace:
>
> "Depth = And subcatCategoryId = AND LEFT(CatHierarchy,3) = 'vID'"
>
> with:
>
> "Depth = SOMETHING And subcatCategoryId = AND
> " & LEFT(CatHierarchy,3) & " ='vID'"
>
> and if that last vID is a variable:
>
> "Depth = SOMETHING And subcatCategoryId = AND
> " & LEFT(CatHierarchy,3) & " ='" & vID & "'"
>
>
> basically, your problems where:
>
> 1. Depth =
> - no comparison value.... just Depth =
> 2. LEFT(...)
> - this is a vbscript function, not a sql function, so it muct
> occur "outsite" or before your sql string and then its result is
> included in the sql string. see above for how to do this. basically, treat
> it like it's a variable.
> 3. vID (maybe)
> - if this is a variable, again, this is a vbscript variable so
> only its result can be included in the sql string. otherwise, the sql
> server will think it's a literal string and treat it as such.
>
>
> hope this helps. if you have the actual line of code that includes the sql
> above, maybe i can help you further.
>
>
> thanks,
>
>
>
> --
> mattwarden
> mattwarden.com
>
>
> ---------------------------------------
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt !