Error Code = 37000 (Syntax error or access violation)

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

March 11th, 2005, 04:53 PM

|3enjam

Registered User

Join Date: Mar 2005

Posts: 4

Time spent in forums: 43 m 14 sec
Reputation Power: 0

Error Code = 37000 (Syntax error or access violation)

I'm getting this error when trying to run an UPDATE query:

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'test'.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (94:1) to (94:30).

here is my code (w/ line nums added):

Code:

47 <cfset PostDate = createodbcdatetime( now() )>
49 <cfquery datasource="Library">
50   INSERT INTO Checkout
51 		(Postdate, Name, COType)
52   VALUES
53 		(#PostDate#, '#FORM.Name#', '#FORM.COType#')
54 </cfquery>
56 <cfquery name="FindRecord" datasource="Library">
57 	SELECT Record
58 	FROM Checkout
59 	WHERE Postdate >= #PostDate#
60 		AND Name = '#FORM.Name#'
61 </cfquery>
63 <cfset #RecordFound# = #FindRecord.Record#>
65 <cfset querytext = "UPDATE Checkout SET "> <!--- initialize the query --->
66
67 <cfset querytext = iif( (isdefined("FORM.ScanItem1")  and FORM.ScanItem1  is not ""), de(querytext & "ScanItem1  = '#FORM.ScanItem1#', SIDateOut1  = #PostDate#,"), de(querytext) )>
<!--- many more of these, removed for space --->

78 <cfset querytext = iif( (isdefined("FORM.LongItem1")  and FORM.LongItem1  is not ""), de(querytext & "LongItem1  = '#FORM.LongItem1#', LIDateOut1  = #PostDate#,"), de(querytext) )>
<!--- many more removed for space --->

89 <cfset querytext = left(querytext, len(querytext) - 1) & " WHERE Record = #RecordFound#"> <!--- remove that last comma, and finalize --->  
90 
91 <cfdump var="#querytext#"> <!--- debugging --->
92 
93 <!--- run it --->
94 <cfquery datasource="Library">
95 	#querytext#
96 </cfquery>

when i copy-paste the cfdump output into the SQL server directly, everything works just fine.

here is a sample query:

Code:

UPDATE Checkout SET ScanItem1 = 'test', SIDateOut1 = {ts '2005-03-11 15:36:24'},ScanItem2 = 'items', SIDateOut2 = {ts '2005-03-11 15:36:24'},ScanItem3 = 'and', SIDateOut3 = {ts '2005-03-11 15:36:24'},ScanItem4 = 'stuff', SIDateOut4 = {ts '2005-03-11 15:36:24'} WHERE Record = 324

i'm baffled as to why this error is occuring, the types are correct, and copy-paste works.

thanks for any help

March 12th, 2005, 08:27 AM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,343

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 5 m 22 sec
Reputation Power: 891

if ScanItem1 is a numeric column, you don't want the single quotes around 'test'

__________________
r937.com | rudy.ca

March 14th, 2005, 03:25 PM

|3enjam

Registered User

Join Date: Mar 2005

Posts: 4

Time spent in forums: 43 m 14 sec
Reputation Power: 0

all the variable types are correct.

when i copy-paste the query into the database manager directly, the query functions perfectly.

somewhere between coldfusion and the database server, the query is not working.

all ScanItems are nvarchar's and all the Dates are smalldatetime's and Record is an int

March 14th, 2005, 03:29 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,343

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 5 m 22 sec
Reputation Power: 891

i think you're getting two single quotes for one, or something like that

note that the error message points to test which comes right after a quote

look up the PreserveSingleQuotes() function

it may not be the solution here -- quite frankly, i don't understad your de() function -- but at least it talks about the issue

March 14th, 2005, 03:37 PM

|3enjam

Registered User

Join Date: Mar 2005

Posts: 4

Time spent in forums: 43 m 14 sec
Reputation Power: 0

the de(string) function is delayed evaluation function that basically just returns the string inside the function as a string.

the iif(condition, true_expression, false_expression) function returns an evaluated version of true_expression if condiditon is true, and an evaluated version of false_expression if condition is false.

what i mean by evaluated version of the string is this:
if the string 'thisvar' is a variable, it will return the value of that variable, if 'thisvar' is merely a string and not a varible, the iif() function will return an error saying that it cannot evaluate the 'thisvar' variable, the de() function circumvents this by evaluating the string within it as merely a string.

i know its a little confusing at first, but the problem is not with the iif() or de() functions, the query runs fine when copy-pasted, and this is where my consternation lies: why not work when run from cold fusion and run fine when copy-pasted with ZERO edits into the SQL database manager directly?

but i will try your preservesinglequotes() function and see if that helps...

March 14th, 2005, 03:49 PM

|3enjam

Registered User

Join Date: Mar 2005

Posts: 4

Time spent in forums: 43 m 14 sec
Reputation Power: 0

UPDATE:

i have made those changes to the code and now it works.

still confused as to why, but relieved.

thanks for your help.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: