The query you see is my output from the variable I created containing the SQL statement.
Man I hate access. Tim -----Original Message----- From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 9:28 AM To: CF-Talk Subject: Re: Access SQL Question :( can you show us what the query looks like in the debug output? Wait, does CF 4.5 output that.... i forget. :-) If so, send that to the list. That would let us see what the dynamic query looked like when it failed.... i think, and we can go from there. mike ----- Original Message ----- From: "Tim Heald" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, August 01, 2003 9:19 AM 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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
