Form Problems With Checkboxes and ID's - Please help

ValHart · Nov 25, 2002

Hello,

I hope someone can point me to a good reference or explain what I am doing wrong here. I keep getting errors no matter what I try. I want to update a checkbox field for multible records, and I seem to get this about the ID field.

ODBC Error Code = 22005 (Error in assignment)

[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification (null)

The error occurred while processing an element with a general identifier of (CFUPDATE), occupying document position (53:1) to (53:80).

Here is the page:

<html>
<head>
<title>Untitled</title>
</head>

<cfparam default="false" name="url.submit">

<body>
<CFQUERY NAME="GetData" DATASOURCE="workdb">
SELECT *
FROM Contacts
WHERE Info_updates = 1
</CFQUERY>

<CFQUERY NAME="GetData2" DATASOURCE="workdb">
SELECT *
FROM MarketingLeadsMaster
WHERE Info_updates = 1
</CFQUERY>

<form action="editrec.cfm?submit=true" method="POST">
<table>

<tr>

<th>E-Mail</th>
<th>Updates Wanted</th>
<th>Company</th>
</tr>

<CFOUTPUT QUERY="GetData">
<tr>
<td>#email#</td>
<input type="Hidden" name="ID" value="#ID#">
<cfparam name="Info_updates" default="'no'">
<td><input checked type="checkbox" name="Info_updates" value="#Info_updates#"></td>
<td>#Company#</td>
</tr>
</CFOUTPUT>
<tr><td> </td></tr>
<td><input type="Submit" name="Submit" value="Update Record"></td>
<tr><td> </td></tr>
</table>
</form>

<cfif url.submit eq true>

<cfupdate datasource="workdb" tablename="Contacts" formfields="ID,Info_updates">
</cfif>

Thank you.
Val

DeZiner · Nov 25, 2002

My guess is that the ID is a primary key in access which can not be updated. Instead of using cfupdate do a standard query with an update statement.

<cfquery name="updateIt" datasource="#ds#">

UPDATE Contacts
SET info_updates = #form.info_updates#
WHERE ID = #form.ID#
</cfquery>

This way you are not trying to update the primary key just updating the record associated with the key.
DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

ValHart · Nov 25, 2002

Thanks, but now I get this and still cannot figure it out!!

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'ID = 8,12,25,28,33,37,38,42,44,48,49,50,51,52,55,56,60,64,68,70,72,74,78,80,82,83,85,92,99,101,102,103,107,108,111,112,113,115,116,126,132,136,140,142,144,146,152,154,161,164,170,171,172'.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (75:1) to (75:44).

Using:
<cfquery name="updateIt" datasource="workdb">
UPDATE Contacts
SET info_updates = '#form.info_updates#'
WHERE ID = #form.ID#
</cfquery>

Any more ideas?

Thank you.

DeZiner · Nov 25, 2002

Your ID field is holding a comma delimeted list of (all?) id's. To update multiple ID's I believe you will need to loop through the list.

<cfloop list="#id#" index="Idx" delim=",">
<cfquery name="updateIt" datasource="workdb">
UPDATE Contacts
SET info_updates = '#form.info_updates#'
WHERE ID = #Idx#
</cfquery>
</cfloop>

May want to ask one of these other experts about load when running such an update and the syntax on the delim value of the loop tag. You can do the loop without the delim in there. DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

ValHart · Nov 26, 2002

I really appreciate the help, I may be getting there. I now get this error:

ODBC Error Code = 22003 (Numeric value out of range)
[Microsoft][ODBC Microsoft Access Driver]Numeric value out of range (null)
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (88:1) to (88:44).

Problem is, how do I know what value (ID or Info_updates) it is complaining about? The Info_updates is a Yes/No in Access and the ID is the auto generated numeric key field.

Anyone?

Thank you.

csteinhilber · Nov 26, 2002

I think the problem is, if I read your code correctly, info_updates will also be returning a comma-delimited list... since there are multiple checkboxes with that name.

Also, you're changing the meaning of a checked checkbox midstride. If the value of info_updates is "no", you set the value of the checkbox to no... but it's still checked. That's counter intuitive. Not only that, but if the checkbox was unchecked, the value of info_updates would never be passed.

Finally, you're passing a parameter in a query string to the FORM's action page (submit=true)... this is very much frowned upon. The form will pass a value for submit on it's own... use that to your advantage.

Try something like:

Code:

<html>
<head>
    <title>Untitled</title>
</head>

<cfparam default=&quot;&quot; name=&quot;form.submit&quot;>

<body>
<CFQUERY NAME=&quot;GetData&quot; DATASOURCE=&quot;workdb&quot;>
    SELECT * 
    FROM Contacts
    WHERE Info_updates = 1 
</CFQUERY>

<CFQUERY NAME=&quot;GetData2&quot; DATASOURCE=&quot;workdb&quot;>
    SELECT * 
    FROM MarketingLeadsMaster
    WHERE Info_updates = 1
</CFQUERY>


<form action=&quot;editrec.cfm&quot; method=&quot;POST&quot;>
<table>

<tr>

<th>E-Mail</th>
<th>Updates Wanted</th>
<th>Company</th>
</tr>

<CFOUTPUT QUERY=&quot;GetData&quot;>
<tr>
<td>#email#</td>
<td>
<input type=&quot;Hidden&quot; name=&quot;ID&quot; value=&quot;#ID#&quot;> 
<CFIF GetData.info_updates EQ &quot;yes&quot;>
    <input checked=&quot;checked&quot; type=&quot;checkbox&quot; name=&quot;wantsUpdates&quot; value=&quot;#ID#&quot;>
<CFELSE>
    <input type=&quot;checkbox&quot; name=&quot;wantsUpdates&quot; value=&quot;#ID#&quot;>
</CFIF>
</td>
<td>#Company#</td>
</tr>
</CFOUTPUT>
<tr><td> </td></tr>
<td><input type=&quot;Submit&quot; name=&quot;Submit&quot; value=&quot;Update Record&quot;></td>
<tr><td> </td></tr>
</table>
</form>



<cfif trim(lcase(FORM.submit)) eq &quot;update record&quot;>


<cfloop list=&quot;#FORM.id#&quot; index=&quot;whichID&quot; delim=&quot;,&quot;>
<cfquery name=&quot;updateIt&quot; datasource=&quot;workdb&quot;>
    UPDATE Contacts
    <cfif ListFind(&quot;#FORM.wantsUpdates#&quot;,whichID) GT 0>
       SET info_updates = 'yes'
    <cfelse>
       SET info_updates = 'no'
    </cfif>
    WHERE ID = #whichID#
</cfquery>
</cfloop>
</cfif>

DeZiner is correct in his assessment that the looped query above isn't terribly scalable. If you have a LOT of IDs, you'll start to see some performance problems. One way around this would be to develop lists in memory for IDs with "yes" values and IDs for "no" values, then perform two updates. A little bit faster and less prone to scale problems, since you're utilizing memory and only hitting the DB twice instead of n times.

Something like:

Code:

<!--- create a list that is essentially the inverse of
      wantUpdates, to contain all the IDs of entries
      where the checkbox was NOT checked  --->
<cfset notWantsUpdates = &quot;&quot;>
<cfloop list=&quot;#FORM.id#&quot; index=&quot;whichID&quot; delim=&quot;,&quot;>
  <cfif ListFind(&quot;#FORM.wantsUpdates#&quot;,whichID) LT 1>
      <cfset notWantsUpdates = ListAppend(&quot;#notWantsUpdates#&quot;,whichID)>
  </cfif>
</cfloop>

<cfif ListLen(&quot;#wantsUpdates#&quot;) GT 0>
<cfquery name=&quot;updateYes&quot; datasource=&quot;workdb&quot;>
    UPDATE Contacts
    SET info_updates = 'yes'
    WHERE ID IN (#wantsUpdates#)
</cfquery>
</cfif>

<cfif ListLen(&quot;#notWantsUpdates#&quot;) GT 0>
<cfquery name=&quot;updateNo&quot; datasource=&quot;workdb&quot;>
    UPDATE Contacts
    SET info_updates = 'no'
    WHERE ID IN (#notWantsUpdates#)
</cfquery>
</cfif>

Hope it helps,
-Carl

ValHart · Nov 26, 2002

Thank you both so much, this last bit really solved my problems, much appreciated!

I'll get better as I learn more

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Form Problems With Checkboxes and ID's - Please help

ValHart

Technical User

DeZiner

Programmer

ValHart

Technical User

DeZiner

Programmer

ValHart

Technical User

csteinhilber

Programmer

ValHart

Technical User

Similar threads

Part and Inventory Search

Sponsor