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
