Tim,
AS has been mentioned, if you are creating a string and passing it to the
cfquery tag, make sure and use preserveSinglequotes( ) around it:
<cfquery name="update" datasource="blah">
#preservesinglequotes(myQueryString)#
</cfquery>
If you do not, the CF escapes the single quotes FOR you.... and you end up
with:
update printers set MAKE = '' new make'',
So the driver interprets that as Make = ''... then it trys to do something
with the word "new". Since there is no comma, it is expecting a WHERE or
some other key word - hence the violation. Why don't you send the full
detaisl of the error to the list - where the sytax is visible in the
details. That might be a better clue.
-Mark
-----Original Message-----
From: Tim Heald [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 8:19 AM
To: CF-Talk
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5. I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE =
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1, SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID = 120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a great
many fields, and would just be a major pain to write conditionals for each
and every one.
Tim
<cfscript>
// this is the beginning of the query string, I will append values to it as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = "sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8";
zerolist = '';
for (i=1; i LTE ListLen(myList, ","); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ","),
",")){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ","), ",");
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq "printerid"){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery & thisField & " = ";
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery &
form[thisField];
}else{
variables.myQuery = variables.myQuery & "'" &
form[thisField] & "'";
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery & ", ";
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery = variables.myQuery & thisField & " = 0";
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery & ", ";
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
</cfscript>
---
[This E-mail scanned for viruses by Declude Virus]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
