try to use [ ] in column names, like [MAKE], you can also partially delete
the sql statement one by one to find the bad row.
----- Original Message -----
From: "Tim Heald" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, August 01, 2003 4:19 PM
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
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
