RE: [CFTALKTor] database insert

[Dave Boehm]

Title: Message

You'll notice that the expression in question starts with two single quotes and ends with one single quote. Thus the syntax error is because the string is being truncated before it gets to the closing single quote. I am told that single quotes, not double quotes should be used to surround text fields in sql. Also, in Coldfusion, the use of single quotes inside a sequel string is permitted if the function preservesinglequotes is used as has been done here.

 

The problem remains that the part of the sql string that refers to the memo field data is being truncated. I am wondering if this is a problem with ColdFusion, Access, or ODBC.

 

Thanks for your help though. 

 

 

David Boehm
M&T Printing Group
907 Frederick St.
Kitchener, ON
N2B 2B9
(519) 571-0101
[EMAIL PROTECTED]

-----Original Message-----
From: Phill Addorisio [mailto:[EMAIL PROTECTED]]
Sent: May 13, 2002 10:59 AM
To: [EMAIL PROTECTED]
Subject: RE: [CFTALKTor] database insert

The error message is saying that there is a syntax error in a query expression, and then it displays the query expression in question.  What it then displays is the SQL value for ITEMNOTES (i.e. 'Originals = 1; 1-sided = 1; 2-sided = 0; Finished Size...). This would indicate that this not being treated as a string literal as you had intended, but rather as part of the SQL query.  Since I do work in SQL Server and Access and CF (among others), I always have trouble remembering where single quotes are accepted vs. where double quotes are required (and in some places either can be used) for enclosing string literals.

 

I would try using the double quotes to enclose your string literals.  One other item to point out - which may also be corrected by using double quotes - is that your string contains single quotes in the middle of it.  This is definitely going to cause you some grief as this will be seen as the end of the string literal.

 

Hope this helps.

 

Phill Addorisio

AllianzWeb

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dave Boehm
Sent: May 13, 2002 10:36 AM
To: [EMAIL PROTECTED]
Subject: [CFTALKTor] database insert

I am trying to insert a record into a database (this is an Access 2000 database) using CFQuery with sql such as "insert into tablename (fieldlist) values (values). One of the fields (named "ITEMNOTES") is a memo and the value for it is 300 or more characters long. Something is truncating the data for the field to 257 characters. What might be the problem?

Below is the code. Below that is the page output. The first part of the page is where I output the sql string (for testing purposes) followed by the error message I get.

Thanks for your help.

<cfquery name="adddata" datasource="#db#" dbtype="ODBC">
        #preservesinglequotes(sql)#
</cfquery>

Insert into items2 (ITEMDESCRIPTION,ITEMLANGUAGE,ITEMNAME,ITEMNOTES,ITEMNUMBER,ITEMREVISION,ITEMTYPE,REPLACES)Values ('Main Test Form for testing purposes','English','Test Form','Originals = 1; 1-sided = 1; 2-sided = 0; Finished Size: 8.5 x 11; Stock: 20 lb. bond; Stock Colour: White; Stock Finish: laser; INKS; 1st Front Colour:Black; Staple: corner; Fold: letter; Copy In; Drilling: 3 Standard; Pad in: 100's; Spiral Binding: red; Shrink Wrap: 100's; Use numbering in: red; Start numbering at: 1000; End numbering at: 2000; Perforate; Score; Slip Sheet','1234','2','Form','rev 1')

Bottom of Form 0

Error Occurred While Processing Request

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Originals = 1; 1-sided = 1; 2-sided = 0; Finished Size: 8.5 x 11; Stock: 20 lb. bond; Stock Colour: White; Stock Finish: laser; INKS; 1st Front Colour:Black; Staple: corner; Fold: letter; Copy In; Drilling: 3 Standard; Pad in: 100's; Spiral Binding: red; Sh'.

SQL = "Insert into items2 (ITEMDESCRIPTION,ITEMLANGUAGE,ITEMNAME,ITEMNOTES,ITEMNUMBER,ITEMREVISION,ITEMTYPE,REPLACES)Values ('Main Test Form for testing purposes','English','Test Form','Originals = 1; 1-sided = 1; 2-sided = 0; Finished Size: 8.5 x 11; Stock: 20 lb. bond; Stock Colour: White; Stock Finish: laser; INKS; 1st Front Colour:Black; Staple: corner; Fold: letter; Copy In; Drilling: 3 Standard; Pad in: 100's; Spiral Binding: red; Shrink Wrap: 100's; Use numbering in: red; Start numbering at: 1000; End numbering at: 2000; Perforate; Score; Slip Sheet','1234','2','Form','rev 1')"

Data Source = "ECONOMICAL"
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (216:2) to (216:57) in the template file C:\Inetpub\wwwroot-volpub\CFDOCS\clients\Economical\pagelast.cfm.

Date/Time: 05/13/02 10:29:11
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)
Remote Address: 192.168.1.57   
Please inform the site administrator that this error has occurred (be sure to include the contents of this page in your message to the administrator).

David Boehm
M&T Printing Group
907 Frederick St.
Kitchener, ON
N2B 2B9
(519) 571-0101
[EMAIL PROTECTED]