what are the data types for the columns? what version of access of are you using?
----- Original Message ----- From: "Tim Heald" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, August 01, 2003 9:52 AM Subject: RE: Access SQL Question :( > Here's what it's producing now: > > 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, [SOL8] = 1, [sol1] = 0, [sol3] = 0, > [sol5] = 0, [sol7] = 0 where printerID = 120 > > Same errror: > > ODBC Error Code = 37000 (Syntax error or access violation) > > > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in > query expression '''new make''' > > But it runs fine in SQL view in access. > > Weird > > Tim > > -----Original Message----- > From: Tim Heald [mailto:[EMAIL PROTECTED] > Sent: Friday, August 01, 2003 9:48 AM > To: CF-Talk > Subject: RE: Access SQL Question :( > > > 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 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
