ASP Classic SQL Query error message, right syntax

I am passing two (2) parameters in the URL, and building the following SQL:

mQry = "SELECT DISTINCT name FROM link3 WHERE invoice_num ="  & request.querystring("num") & "AND name LIKE" & request.querystring("nam")

I got an error message:

Microsoft OLE DB Provider for Oracle error '80040e14'

ORA-00933: SQL command not properly ended

What would be the right syntax for this?

回答1:

You need to put quotes around the LIKE clause. Also, you could consider using percents for wildcard matching

mQry = "SELECT DISTINCT name FROM link3 WHERE invoice_num =" & request.querystring("num") & " AND name LIKE '%" & request.querystring("nam") & "%' "

回答2:

Part of your problem may be the improper spacing around the quotes where you're inserting the values. This:

mQry = "SELECT DISTINCT name FROM link3 WHERE invoice_num ="  & request.querystring("num") & "AND name LIKE" & request.querystring("nam")

Will most likely result in sending this to the database:

SELECT DISTINCT name 
FROM link3
WHERE invoice_num =2AND name LIKEsomeothervalue

If you add proper spacing like this:

mQry = "SELECT DISTINCT name FROM link3 WHERE invoice_num = "  & request.querystring("num") & " AND name LIKE " & request.querystring("nam")

It would give you a more properly formatted result like this:

SELECT DISTINCT name 
FROM link3
WHERE invoice_num = 2 AND name LIKE someothervalue

Any time I get errors that indicate problems with SQL formatting/structure I tend to log the SQL that is sent before it goes to the database. This helps spot odd issues like that.

Also, sharpguru is probably right - the LIKE clause probably isn't formatted correctly either. You need to enclose text values in single-quotes, and the % is a wildcard matching 0 or more characters - making this more like what you are probably looking for:

mQry = "SELECT DISTINCT name FROM link3 WHERE invoice_num = "  & request.querystring("num") & " AND name LIKE '%" & request.querystring("nam") & "%'"

Now, this does all assume that invoice_num is some sort of numeric value - which is implied in your question and code. However, if it is not (as suggested by your comment and other questions), you would need to put the value in single quotes - just like any other text field in almost all RDBMSs:

mQry = "SELECT DISTINCT name FROM link3 WHERE invoice_num = '"  & request.querystring("num") & "' AND name LIKE '%" & request.querystring("nam") & "%'"

The above would also be used if the data type of the database column invoice_num was set to a non-numeric data type. Just because the data is somehting that could be called numeric, doesn't mean it's automatically treated as numeric. If the data type of the column is text, ntext, or any other non-numeric type, then you will need to surround the value in quotes just like any other text value.

And, while not related to the question, I'm hoping this is an over-simplified example and you're not directly inserting QueryString values into the SQL statement. If you haven't been told yet, that's opening you up to a wide variety of security problems - look up some information on SQL Injection.