Incorrect syntax near the keyword 'SELECT' 1

[artguy]

I'm getting this error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

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

I have read the dealers table successfully without using the SQL statement just to make sure I'm connecting to the database fine and that the table name is correct. I simplified my query to as simple as possible.

Here's my ASP code snippet:

	Set rs = Nothing
	
	Set rs = Server.CreateObject("ADODB.Recordset")
	rs.CursorLocation = adUseClient
	strSQL = "SELECT * FROM dealers"
	Response.Write strSQL
	rs.Open strSQL, connStr, adOpenStatic, adLockReadOnly, adCmdTableDirect

If you need more info to help me out, let me know. If this has been answered before, I apologize but I searched for this error and couldn't find anything.

Thanks for your time and any kind of assistance would be appreciated.

Bob

 

[gmmastros]

I think your problem is NOT with the sql string. I would look in to the adCmdTableDirect. Check it out.

adCmdTableDirect, 512, Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect. This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute.

You could probably change your SQL to strSQL = "dealers"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[artguy]

	Const adCmdTableDirect = &H0200
	Const adLockReadOnly = 1
	Const adOpenStatic = 1
	Const adUseClient = 3

These are the values. Are they incorrect?

If I do just use strSQL = "dealers" it works fine, but the SQL statement I want to use once this is figured out is:

strSQL = "SELECT * FROM dealers WHERE (county_covered LIKE '%" & countyName & "%' AND state_covered LIKE '%"& stateCovered & "%') OR (county_covered = 'ALL' AND state_covered LIKE '%"& stateCovered & "%')"

Bob

 

[gmmastros]

Instead of using adCmdTableDirect, use adCmdText.

adCmdTableDirect is used ONLY for getting all the data from the table without any joins or filters. adCmdText allows your SQL command to be as complex as you want.

With that being said, you should investigate the ADO command object because it protects you from SQL Injection attacks. Just for fun, google the phrase "SQL Injection".

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[artguy]

That did the trick! I changed the adCmdTableDirect in a few spots to adCmdText and left it alone in another where it complained when I did change it.

I will look into the SQL Injection. Not familiar with the ADO command object at all. I'm still trying to get the hang of this whole SQL thing, so I really appreciate your help!

Thanks again!

Bob

 

[gmmastros]

No problem. I'm glad to help, and thanks for the star.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom