SQL Error

[kevinj]

Can anyone tell me why the following SQL statement gives the following error?

Set RS = DB.execute("SELECT dp.*,p.art_number,p.title FROM department_product dp, products p WHERE dp.department_id=" & DEPARTMENT_ID & " AND p.product_id = dp.product_id ORDER BY dp.sort ASC")

Error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'.

/admin/departments/add-edit.asp, line 82

I understand it's difficult not knowing the layout of the database, but I thought I might have left out some syntax.

Thanks.

[Dreamquick]

Can you post the actual SQL that will be executing as well?

As it stands that *should* work (tried something similar and it worked) but I can't say for certain unless you post the full SQL rather than just the SQL with a variable being merged into it as the contents of that variable could be radically altering the SQL.

I might also be tempted to suggest that a join between the two tables to establish the relationship might be helpful e.g.

SELECT dp.*,p.art_number,p.title
FROM department_product dp
LEFT JOIN products p ON p.product_id = dp.product_id
WHERE dp.department_id = " & DEPARTMENT_ID & "
ORDER BY dp.sort ASC

As now the relationship between department_product and products is cleanly defined rather than relying on a slightely "iffy" contruct (you may need to modify the join a little as ATM its based on a 1-to-many relationship between "products" and "department_product").

- tony

[kevinj]

My knowledge of SQL is limited. This was written for me by another programmer who I no longer can get a hold of. The problem has to do with the fields and tables and not the syntax?

[brotherhood of LAN]

Try dropping the quotes before AND.

I had a similar problem using MSSQL

[kevinj]

I tried it but it still gave the error.

[aspdaddy]

My guess:
DEPARTMENT_ID has no value

:)

[Dreamquick]

It definitely will *not* be the quotes as they are necessary to integrate the VB / VBScript variable into the SQL string which he wants to execute.

Equally it is not a problem with a field or table name (at least as this stage) because the error message would be different, the error message you are getting effectively says "there is a problem with the syntax of the statement you gave me near the AND operator".

How do I know the basic query works? Before I posted my first comment I took that query and modified it to fit a set of tables I had here, plugging in a value in place of the '" & DEPARTMENT_ID & "' and it worked.

As I suggested earlier we really need to be able to see the actual SQL statement which is executing.

Need help getting the SQL out so we can see it?

Depending on what you are using either of these two should help you see the full SQL - for the best effect the need to appear immediately before the "Set RS = ..." line.

Once you are done you can remove the extra lines and the code will be back to normal.

Assuming it's VBSCript try this; (this writes the SQL then stops anything after the response.end from being run to save wasting time and to suppress any errors caused by using response.write in your script)

'Debug - Start
Response.Write "SELECT dp.*,p.art_number,p.title FROM department_product dp, products p WHERE dp.department_id=" & DEPARTMENT_ID & " AND p.product_id = dp.product_id ORDER BY dp.sort ASC"

Response.End
'Debug - End

- Tony

[Sinner_G]

Might not be the solution, but I think you miss a space between dp.department_id= and the quote. And probably before the = too.

[Dreamquick]

Spaces within clauses aren't the problem.

SQL Server will happily let SQL statements use clauses with either the compact form (without spaces) or the expanded form (with spaces) e.g. the following are all valid SQL (for SQL Server at least) and all work the same way;

SELECT * FROM TableA WHERE ID=1
SELECT * FROM TableA WHERE ID= 1
SELECT * FROM TableA WHERE ID =1
SELECT * FROM TableA WHERE ID = 1

The only real reason a space *would* cause a problem would be if there wasn't one separating a non-clause e.g. when I take SQL laid out like this;

SELECT *
FROM TableA
WHERE ID = 1

it quite often goes wrong when I put the entire statement on one line but forget to add spaces {blush}

SELECT * FROMTableAWHERE ID = 1

- Tony

[aspdaddy]

SELECT dp.*,p.art_number

and shoudnt that be p.part_number ?

Before I posted my first comment I took that query and modified it to fit a set of tables I had here, plugging in a value in place of the '" & DEPARTMENT_ID & "' and it worked

Suggests DEPARTMENT_ID is empty, the resulting sql would give error near the AND :

WHERE dp.department_id = AND p.product_id = dp.product

[kevinj]

Dreamquick,

I added the debug code you wrote immediately before the Set RS= line and this is what it returned:

SELECT dp.*,p.art_number,p.title FROM department_product dp, products p WHERE dp.department_id= AND p.product_id = dp.product_id ORDER BY dp.sort ASC

Any thoughts?

Thanks,
Kevin

[Dreamquick]

kevinj,

The various comments about the variable DEPARTMENT_ID possibly being blank were right - that *is* the cause of the problem.

The actual reason for the error is that whenever you say "field = ABC" there always needs to something in the place of ABC, if not you get the kind of error you were seeing as the SQL engine is expecting to find the other half of the comparison.

SELECT dp.*,p.art_number,p.title
FROM department_product dp, products p
WHERE dp.department_id= AND p.product_id = dp.product_id
ORDER BY dp.sort ASC

Contratulations you have now solved half your problem!

Now the only question you need to answer is why is this blank?, and that can be solved by seeing where DEPARTMENT_ID should be set.

- Tony

[kevinj]

Thank you very much for your help. I'm going to look into it and see what I can determine.

[PaulPaul]

aspdaddy: Excellent work.

I myself have been doing SQL for more years than I care to remember, and I didnt see that.

Very good work, aspdaddy.