no problem... -----Original Message----- From: Tim Heald [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 9:08 AM To: CF-Talk Subject: RE: Access SQL Question :(
ohhh I thought it was around the field value. You got it. Thanks everyone Tim -----Original Message----- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 10:05 AM To: CF-Talk Subject: RE: Access SQL Question :( 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
