Has anyone ever gotten this error message when running a query?!?!?

[programmher]

ODBC Error Code = 37000 (Syntax error or access violation)


[MERANT][ODBC SQL Server Driver][SQL Server]Line 8: Incorrect syntax near '<'.


<cfquery name = &quot;viewhistory&quot; datasource&quot;MyDatasource&quot;>

SELECT *

FROM tbl_Names(nolock),

tbl_Clients(nolock)

WHERE tbl_clients.shipaddress= tbl_Names.addr1
AND tbl_Names.addr1 = #addresses#
AND tbl_Clients.customernum= #customer_num#
AND tbl_Clients.CreditOfcr = #payor#

</cfquery>

The odd thing is, I can successfully execute this query from SQL 7's query analyzer window.

Can ANYONE shed some light??????

 

[tleish]

Do you need to quote your strings?

<cfquery name=&quot;viewhistory&quot; datasource=&quot;MyDatasource&quot;>
SELECT *
FROM tbl_Names(nolock), tbl_Clients(nolock)
WHERE tbl_clients.shipaddress = tbl_Names.addr1
AND tbl_Names.addr1 = '#addresses#'
AND tbl_Clients.customernum= #customer_num#
AND tbl_Clients.CreditOfcr = '#payor#'
</cfquery> - tleish

 

[programmher]

tleish,

When I try that, I get an error that reads &quot;Syntax error converting the varchar value to a column of data type int&quot;

When I check my SQL table data type, both the data types on both the tables are integers.

Any thoughts?

 

[xor]

In the line...
<cfquery name = &quot;viewhistory&quot; datasource&quot;MyDatasource&quot;>

Add the =
<cfquery ... datasource=&quot;MyDatasource&quot;>

That kind of error (near <), considering you don't do any less than comparison, is probably incorrect CFM syntax somewhere. For me it's usually a typo or something accidental. I would also check if there is any code just before the query. In other words, is the '<' it's referring to the '<'cfquery ..., in which case some error before it gets to the < makes it seem out of place.

Ah, &quot;The odd thing is, I can successfully execute this query from SQL 7's query analyzer window.&quot; Yes, I would check the code that is called before you call the query. If the query works in SQL Analyzer directly, it's probably not your query that is the problem. So check what code gets called beforehand. For some reason that '<' looks out of place to the CF interpreter. So maybe you are missing a matching closing tag somewhere. Eg,
<cfset VarName=&quot;...&quot;
Or similar.

xor

http://donnamiller.net/

 

[programmher]

xor,

Thanks for the tip! I checked my original code and I did include the &quot;=&quot;. I must have accidentally deleted it when I pasted it into Tek-Tips.

I took your suggestion of checking my preceding code and even put it at the top of my page (in case I did omit a closing tag). I got the SAME message.

Alas and Alack - I've used similar queries and have NEVER gotten this message!

Thanks anyway for taking the time to offer your suggestions...

 

[tleish]

Try outputing query in a <CFOUTPUT> to to see what it's actually outputing. Then run the output query directly against your database without <CFQUERY>. Use HTMLEditFormat() to make sure you see any characters that might not show properly in html just for display purposes.

<CFOUTPUT>
[COLOR=000080]<PRE>[/color]

SELECT *
FROM tbl_Names(nolock),
tbl_Clients(nolock)

WHERE tbl_clients.shipaddress= tbl_Names.addr1
AND tbl_Names.addr1 = #HTMLEditFormat(addresses)#
AND tbl_Clients.customernum= #HTMLEditFormat(customer_num)#
AND tbl_Clients.CreditOfcr = #HTMLEditFormat(payor)#

[COLOR=000080]</PRE>[/color]
</CFOUTPUT> - tleish