Parameterized SQL Help
Posted on 2014-03-07
I have a SQL query that runs fine against the database, but when I try to turn it into parameterized SQL to run in classic ASP, I get an error.
First, here's the db environment:
CREATE TABLE #pies (
INSERT INTO #pies
VALUES (1, 'Apple', 1), (2, 'Blueberry', 0), (3,'Cherry',0)
CREATE TABLE #pieorders (
INSERT INTO #pieorders
And here's the raw SQL, where I'm trying to get a list of pies that are either currently available or were ordered in a particular Pie Order (in the below example, order #1):
SELECT P.[ID], P.[Name], PO.[orderid]
FROM #pies P
LEFT OUTER JOIN #pieorders PO ON PO.[PieId]=P.[ID] AND PO.[OrderID] = 1
WHERE P.[Available] = 1 OR PO.[orderid] IS NOT NULL
ORDER BY P.[Name]
When I run it directly against the db, I get:
1, Apple, NULL
3, Cherry, 1
Which is correct.
Here it is in ASP, where I pass in the Pie Order ID:
SQL = "SELECT P.[ID], P.[Name], PO.[orderid] "&_
"FROM #pies P "&_
"LEFT OUTER JOIN #pieorders PO ON PO.[PieId]=P.[ID] AND PO.[OrderID] = ? "&_
"WHERE P.[Available] = 1 OR PO.[orderid] IS NOT NULL "&_
"ORDER BY P.[Name] "
set oCmdPie = Server.CreateObject("ADODB.Command")
oCmdPie.ActiveConnection = conn
oCmdPie.CommandText = SQL
oCmdPie.Parameters(0).value = 1
Set rstPie = oCmdPie.Execute()
It breaks on oCmdPie.Parameters(0).value = 1. I get an error message:
Microsoft OLE DB Provider for SQL Server: Syntax error or access violation
How can I refactor this so it doesn't break?
Please note: although I have given a simplified test environment, this is NOT a homework problem. I need an answer that will not break and will return the same answers as the current raw query. Thank you!