Incorrect syntax error

[omansur]

Hi David:

You advice worked!... I now get another error and can't see anything wrong with the script ... I'm I going blind...

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'ORDER'.

/ssl/SFLIB/incAE.asp, line 2390

--------------------------------------------------------------------------------------------------
sql = "Select * FROM sfMTPrices WHERE mtprodid= '" & makeInputSafe(strProductID) & "' AND mtQUANTITY <= " & makeInputSafe(totqty) & " ORDER BY mtValue DESC"
Set rst = Server.CreateObject("ADODB.RecordSet")
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText
--------------------------------------------------------------------------------------------------

I would really appreciate your help. I'm sure many people have this problem.

O

[ramesh]

Hi David:

You advice worked!... I now get another error and can't see anything wrong with the script ... I'm I going blind...

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'ORDER'.

/ssl/SFLIB/incAE.asp, line 2390

--------------------------------------------------------------------------------------------------
sql = "Select * FROM sfMTPrices WHERE mtprodid= '" & makeInputSafe(strProductID) & "' AND mtQUANTITY <= " & makeInputSafe(totqty) & " ORDER BY mtValue DESC"
Set rst = Server.CreateObject("ADODB.RecordSet")
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText
--------------------------------------------------------------------------------------------------

I would really appreciate your help. I'm sure many people have this problem.

O

& " ORDER BY mtValue DESC"

Shouldn't this be:

& " ORDER BY " & mtValue & " DESC"

[omansur]

I tried it and get a new earlier error: This function has the change you advised.

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'mtValue'

/ssl/SFLIB/incAE.asp, line 2388
------------------------------------------------------------

Here is the entire function:

Function GetMTPrice (strProductID,sProdPrice,lOrderId)
Dim rst,sql
Dim totqty
dim diff
If lOrderId > 0 then
'get from order details
sql = "Select SUM(odrdtQuantity) as totqty FROM sfOrderDetails WHERE odrdtProductID= '" & makeInputSafe(strProductID) & "' AND odrdtOrderId= " & makeInputSafe(lOrderID)
else
'get from temp order details
sql = "Select SUM(odrdttmpQuantity) as totqty FROM sftmpOrderDetails WHERE odrdttmpProductID= '" & makeInputSafe(strProductID) & "' AND odrdttmpSessionID= " & session("SessionID")
End If

Set rst = Server.CreateObject("ADODB.RecordSet")
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText
if rst.recordcount <= 0 then
GetMTPrice = sProdPrice 'no mtp
CloseObj (rst)
exit function
end if
totqty = rst("totqty")
CloseObj (rst)

sql = "Select * FROM sfMTPrices WHERE mtprodid= '" & makeInputSafe(strProductID) & "' AND mtQUANTITY <= " & makeInputSafe(totqty) & " ORDER BY " & mtValue & " DESC"
Set rst = Server.CreateObject("ADODB.RecordSet")
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText

If rst.recordcount <= 0 then
GetMTPrice = sProdPrice 'no mtp
else
rst.movefirst
If rst("mtType") = "Amount" then
GetMTPrice = cdbl(sProdPrice) - cdbl(rst("mtValue"))
else
diff = cdbl(sProdPrice) * (cdbl(rst("mtValue"))/100)
GetMTPrice = cdbl(sProdPrice) - cdbl(diff)
end if
End If

If cdbl(GetMTPrice) > cdbl(sProdPrice) then
GetMTPrice = sProdPrice
End IF


CloseObj (rst)
If GetMTPrice < 0 then GetMTPrice = 0

End Function

[ramesh]

Perhaps it's my mistake. I take it you need to order by mtValue then DESC? Try this, then:

sql = "Select * FROM sfMTPrices WHERE mtprodid= '" & makeInputSafe(strProductID) & "' AND mtQUANTITY <= " & makeInputSafe(totqty) & " ORDER BY mtValue, DESC"

I believe what you needed was the comma after mtValue.

[omansur]

It still does not work. Same error.

O

[Arch_Angel]

A comma shouldn't be there ramesh. Omansur, do this, add the following code in red to your function and tell us the result:

sql = "Select * FROM sfMTPrices WHERE mtprodid= '" & makeInputSafe(strProductID) & "' AND mtQUANTITY <= " & makeInputSafe(totqty) & " ORDER BY mtValue DESC"
Response.Write sql
Response.End
Set rst = Server.CreateObject("ADODB.RecordSet")
rst.Open sql, cnn, adOpenStatic, adLockReadOnly, adCmdText

You should get the output of the query printed in your browser. Post the query here so we can see what the problem is exactly.

[omansur]

With the added code, it stops before reaching the credit card processing screen and gives me the following code:

Select * FROM sfMTPrices WHERE mtprodid= 'PTF2ST' AND mtQUANTITY <= 3 ORDER BY mtValue DESC

omansur

-What I find strange is that this code is running fine on another server. The site is Newpak (dot) net.

[Arch_Angel]

Is mtQuantity an integer field or a text/character field in the database?

If it is, you need to put single quotes around the 3.

sql = "Select * FROM sfMTPrices WHERE mtprodid= '" & makeInputSafe(strProductID) & "' AND mtQUANTITY <= '" & makeInputSafe(totqty) & "' ORDER BY mtValue DESC"

[omansur]

With the change I now get an error earlier in the code... Should these errors come up an any server I run it on?

Incorrect syntax near the keyword 'AND'.

------------------------------------------------
if pageid = 3 then sql = gorderSQL & " WHERE odrdtOrderId=" & makeInputSafe(iOrderID) & " AND odrdtProductID='"& sProdId & "'"

Set rst = Server.CreateObject("ADODB.RecordSet")
rst.Open sql, cnn, adOpenStatic, adLockReadOnly,adcmdText
-----------------------------------------------

Thanks for you help, (an more help and I may have to pay you)

omansur

[Arch_Angel]

If you are getting syntax errors, just make check to see if the comparison you are doing before the keyword AND, is a text field, you put single quotes around the variable. If it is an integer you remove the single quotes.

You need to do the same for that error too.

... odrdtOrderId='" & makeInputSafe(iOrderID) & "' ...

How did you move your database from your previous server to your new one?
Was it the same version SQL Server that you were on previously?