...

View Full Version : Database Query using SELECT * FROM WHERE and OR giving Syntax error



markkivits
05-06-2011, 09:07 PM
Hi, am having an issue when trying to query a database where i want to display a result if it meets either criteria. i.e. I want it to look at the database and determine if two or more of the database fields correspond to a request.form variable. The query works fine with just one field but when i try to query to see if two or more of the database field match i get a syntax error.

This is the error i get

[ICODE] Type mismatch: '[string: "SELECT * FROM Vendor"]'

When i use the following it works:

[ICODE] RsVendors.Open "SELECT * FROM Vendors where VendorCategory1 = '" + Replace(VendorCategory, "'", "''") + "'", OBJdbConn

(Just one field ) But when i try this:

[ICODE] RsVendors.Open "SELECT * FROM Vendors WHERE VendorCategory1 = '" & request.form("VendorCategory") & "'" OR " WHERE VendorCategory2 = '" & request.form("VendorCategory") & "'",OBJdbConn

It doesn't

New to this and am really stuck. Would appreciate any help i can get. Thanks

angst
05-06-2011, 09:26 PM
give this a try:


"SELECT * FROM Vendors WHERE ( VendorCategory1 = '" & request.form("VendorCategory") & "' OR VendorCategory2 = '" & request.form("VendorCategory") & "')",OBJdbConn

markkivits
05-06-2011, 09:45 PM
Brilliant, thank you so much....

angst
05-06-2011, 09:46 PM
welcome :)

Old Pedant
05-06-2011, 10:04 PM
Except you really *SHOULD* protect against SQL injection and handle apostrophes in values.

So I'd do:


vc = Replace( Trim(Request.Form("VendorCategory")), "'", "''" )

SQL = "SELECT * FROM Vendors WHERE '" & vc & "' IN ( VendorCategory1, VendorCategory2 )"

RsVendors.Open SQL, OBJdbConn

Or, even better, get rid of the line (not shown) that does


Set RsVendors = Server.CreateObject("ADODB.Recordset")


And simply use


vc = Replace( Trim(Request.Form("VendorCategory")), "'", "''" )

SQL = "SELECT * FROM Vendors WHERE '" & vc & "' IN ( VendorCategory1, VendorCategory2 )"

Set RsVendors = OBJdbConn.Execute( SQL )

For more efficiency.

You like the sneaky trick to check for the value in more than one DB field?

You've surely seen SQL that does


... WHERE whatever IN ('xyz','abc','def')

But there is no reason at all you can't turn it around and use IN( ) as shown.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum