Checkbox Frustration..Help Please?

[susanh]

Hi All,

I am trying to get multiple values inserted into a database based on user selection of checkboxes.

I am getting a database error and not sure what is wrong with my code.

If someone has a minute I would sure appreciate another set of eyes to look at it and tell me what I am doing wrong. I is probably right in front of me but I have looked at it so many times I can't see it

Anyway...code here it goes

Form Page
.....
<input name="to_bill" type="checkbox" value="#GetTasks.TaskID#" />.....

Action Page
....
<CFLOOP INDEX="Checked_ITEM" LIST="Form.to_do">
<CFQUERY NAME="Billing" DATASOURCE="intranet" DBTYPE="ODBC">
INSERT INTO Billing(to_bill,projectid,taskid,customer,unit,name,jobtype,labor,
travel,totalhours,partnumber,partdescription,Qty,Price)
VALUES ('#Checked_ITEM#','#Form.ProjectID#','#Form.TaskID#','#Form.Customer#','#Form.Unit#','#Form.Name#','#Form.jobtype#','#Form.labor#','#Form.travel#','#Form.totalhours#','#Form.partnumber#','#Form.partdescription#','#Form.Qty#','#Form.Price#')
</CFQUERY>
</CFLOOP>


Error Message
...
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to numeric.

The error occurred in ....\action.cfm: line 86

84 : <CFQUERY NAME="Billing" DATASOURCE="intranet" DBTYPE="ODBC">
85 : INSERT INTO Billing(to_bill,projectid,taskid,customer,unit,name,jobtype,labor,travel,totalhours,partnumber,partdescription,Qty,Price)
86 : VALUES ('#Checked_ITEM#','#Form.ProjectID#','#Form.TaskID#','#Form.Customer#','#Form.Unit#','#Form.Name#','#Form.jobtype#','#Form.labor#','#Form.travel#','#Form.totalhours#','#Form.partnumber#','#Form.partdescription#','#Form.Qty#','#Form.Price#')
87 : </CFQUERY>


Thanks for the help
Sue

 

[ecobb]

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer][red]Error converting data type varchar to numeric.[/red]

Usually means you either have quotes around a variable in your query when you shouldn't, or your trying to insert a text value into a numeric database field.

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[GUJUm0deL]

I'm guessing '#Form.TaskID#', should not be in quotes.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[imstillatwork]

and no reason to loop a list to insert..

<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.Checked_ITEM#" list="Yes">


If you ARE going to loop make sure you #Form.to_do# your list if it's a variable, otherwise it;s just a string 'Form.to_do'

<CFLOOP INDEX="Checked_ITEM" LIST="Form.to_do">

should be

<CFLOOP INDEX="Checked_ITEM" LIST="#Form.to_do#">

I STRONGLY suggest NOT looping, and doing the first suggestion

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

<CF_embarrassed>
please ignore my post about the loop!!!!!!

I just got home from a LONG drive!!!!!!!!!!

I wish there was an edit feature....
</CF_embarrassed>


the part about cfloop still stands though. use ## around your list variable

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

I give up... please ignore the part about cfqueryparam...

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[susanh]

Thanks Guys,

I am still not doing my loop correctly.

My query outputs 6 rows (which is correct)

If I select all 6 checkboxes and do this with my loop:

<CFLOOP INDEX="Checked_ITEM" LIST="Form.to_bill">

My data inserts in the database taking only the first row

If I select all 6 checkboxes and do this with my loop:

<CFLOOP INDEX="Checked_ITEM" LIST="#Form.to_bill#">

My data inserts in the database taking all the checkboxes but just inserts the values from the first row not the other rows.

I am almost there....just missing something.

 

[susanh]

Do I need to make my loop do a record count? Instead of a List?

 

[imstillatwork]

start debugging, don't just change things and hope it works.

start with a simple cfloop, and cfoutput the index. make sure your getting the value you expect.

if you're getting the value you expect, add a simple query to insert it. maybe leave off all the non-required fields for now.




Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[imstillatwork]

We need to see your form script also, in the mean time...

If you are cflooping a list, the list attributes takes a string. if you use Form.to_do as your list attribute string, then the list is literally 'Form.to_do' if you use #Form.to_do# as the list attribute value, then the list is whatever #Form.to_do# evaluates to.

I've been assuming that your FORM script is using multiple checkboxes with the same NAME, and different values like

<input type="checkbox" name="to_do" value="value1" />
<input type="checkbox" name="to_do" value="value2" />
<input type="checkbox" name="to_do" value="value3" />

When submitted, this will look like

form.todo = "value1,value2,value3"

So First just try to just loop over that form field name to see if the iterations of the loop are giving you what you want:

<cfloop index="Checked_ITEM" list="#Form.to_do#">
	#Checked_ITEM#<br/>
</cfloop>

Then, if the above looks like the values you want, try a very simple insert, then check the DB.

<cfloop index="Checked_ITEM" list="#Form.to_do#">
	<cfquery name="Billing" datasource="intranet" dbtype="ODBC">
	INSERT INTO Billing(to_bill)
	VALUES ('#Checked_ITEM#')
	</cfquery>
</cfloop>

please post the form page that also.



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[GUJUm0deL]

I think Kevin has got it.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[susanh]

Yes..my form I believe is incorrect..or I am just going about this the wrong way.

Somehow I think I am trying to work the cfoutput of my queries as a form.

I think I need to pass them to my action page as url variables not form variable OR
rework my form page with form variable....

Form Page Code:

<CFQUERY NAME="GetTasks" DATASOURCE="intranet" DBTYPE="ODBC">
SELECT *
FROM Tasks
WHERE PROJECTID = #ProjectID#
AND JobType NOT IN ('Parts')
</CFQUERY>
<CFQUERY NAME="GetParts" DATASOURCE="intranet" DBTYPE="ODBC">
SELECT *
FROM InventoryUsed
WHERE PROJECTID = #ProjectID#

<CFFORM ACTION="/intranet/projecttracking/action.cfm?Reason=Modify&Object=Bill" METHOD="POST">
<DIV ALIGN="center">
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="0">
<TR>
<TD ALIGN="center">
<TABLE WIDTH="100%" BORDER="0" CELLSPACING="1" CELLPADDING="2" CLASS="wrapper">
<TR>
<CFIF ParameterExists(Sort) AND Sort IS "DESC">
<CFSET Sort="ASC">
<CFELSE>
<CFSET Sort="DESC">
</CFIF>
<CFOUTPUT>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Bill This Task</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">TaskID</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Name</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">JobType</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Labor</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Travel</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Total Hours</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Part Number</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Part Description</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Parts Quanity</FONT></FONT></TD>
<TD NOWRAP CLASS="title"><FONT CLASS="ColumnFont"><FONT COLOR="444444">Cost of Parts</FONT></FONT></TD>
</CFOUTPUT> </TR>
<CFOUTPUT QUERY="GetTasks" STARTROW="#StartRow#" MAXROWS="#MaxRows#">
<CFIF Cookie.UserName IS "Manager" OR BillingRights IS "Full" OR Creator IS "#Cookie.UserName#">
<CFSET Show="Yes">
<CFELSE>
<CFSET Show="No">
<CFQUERY NAME="GetTasks" DATASOURCE="intranet" DBTYPE="ODBC">
SELECT *
FROM Tasks
WHERE ProjectID = '#ProjectID#'
</CFQUERY>

<CFIF Show IS "Yes">
<CFQUERY NAME="GetLabor" DATASOURCE="intranet" DBTYPE="ODBC">
SELECT SUM(Labor + Travel) AS TotalLabor
FROM Tasks
WHERE TaskID = '#TaskID#'
</CFQUERY>
<CFQUERY NAME="GetJobCost" DATASOURCE="intranet" DBTYPE="ODBC">
SELECT JobCost
FROM JobTypes
WHERE JobType = '#GetTasks.JobType#'
</CFQUERY>
<CFSET TotalLabor="#GetLabor.TotalLabor#">
<CFSET JobCost="#GetJobCost.JobCost#">
<CFTRY>
<CFSET TotalHours="#NumberFormat(TotalLabor * JobCost)#">
<CFCATCH>
<CFSET TotalHours="0">
</CFCATCH>
</CFTRY>
<TR>
<TD NOWRAP CLASS="DataTD" ID="firstcolumnsolid"><FONT CLASS="DataFont"><input name="to_bill" type="checkbox" value="#GetTasks.TaskID#" /></FONT></TD>
<TD NOWRAP CLASS="DataTD" ID="firstcolumnsolid"><A HREF="/intranet/projecttracking/taskdetails.cfm?Reason=Modify&ProjectID=#ProjectID#&TaskID=#TaskID#"><FONT CLASS="DataFont">#GetTasks.TaskID#</FONT></A></TD>
<TD NOWRAP CLASS="DataTD"><A HREF="/intranet/projecttracking/taskdetails.cfm?Reason=Modify&ProjectID=#ProjectID#&TaskID=#TaskID#"><FONT CLASS="DataFont">#MID(GetTasks.Name, 1, 18)#<CFIF Len(GetTasks.Name) GT "18">...</CFIF></FONT></A></TD>
<TD NOWRAP CLASS="DataTD"><A HREF="/intranet/projecttracking/taskdetails.cfm?Reason=Modify&ProjectID=#ProjectID#&TaskID=#TaskID#"><FONT CLASS="DataFont">#GetTasks.JobType#</FONT></A></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">#GetTasks.Labor#</FONT></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">#GetTasks.Travel#</FONT></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">#TotalHours#</FONT></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">&nbsp;</FONT></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">&nbsp;</FONT></TD>
<TD NOWRAP CLASS="DataTD" ALIGN="right" ID="lastcolumnsolid">&nbsp;</TD>
<TD NOWRAP CLASS="DataTD" ALIGN="right" ID="lastcolumnsolid">&nbsp;</TD>
</TR> </CFIF>
</CFOUTPUT>
<CFOUTPUT QUERY="GetParts" STARTROW="#StartRow#" MAXROWS="#MaxRows#"><TR>
<TD NOWRAP CLASS="DataTD" ID="firstcolumnsolid"><FONT CLASS="DataFont"><input name="to_bill" type="checkbox" value="#GetParts.TaskID#" /></FONT></TD>
<TD NOWRAP CLASS="DataTD" ID="firstcolumnsolid"><A HREF="/intranet/projecttracking/taskdetails.cfm?Reason=Modify&ProjectID=#ProjectID#&TaskID=#TaskID#"><FONT CLASS="DataFont">#TaskID#</FONT></A></TD>
<TD NOWRAP CLASS="DataTD"><A HREF="/intranet/projecttracking/taskdetails.cfm?Reason=Modify&ProjectID=#ProjectID#&TaskID=#TaskID#"><FONT CLASS="DataFont">&nbsp;</FONT></A></TD>
<TD NOWRAP CLASS="DataTD"><A HREF="/intranet/projecttracking/taskdetails.cfm?Reason=Modify&ProjectID=#ProjectID#&TaskID=#TaskID#"><FONT CLASS="DataFont">Parts</FONT></A></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">&nbsp;</FONT></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">&nbsp;</FONT></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">&nbsp;</FONT></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">#GetParts.Name#</FONT></TD>
<TD NOWRAP CLASS="DataTD"><FONT CLASS="DataFont">#GetParts.Description#</FONT></TD>
<TD NOWRAP CLASS="DataTD" ALIGN="right" ID="lastcolumnsolid">#GetParts.qty#</TD>
<TD NOWRAP CLASS="DataTD" ALIGN="right" ID="lastcolumnsolid">#GetParts.price#</TD>
</TR></CFOUTPUT>

</TABLE>
</TD>
</TR>
</TABLE>
<CFOUTPUT><INPUT TYPE="hidden" NAME="Object" VALUE="Bill">
<INPUT TYPE="hidden" NAME="ProjectID" VALUE="#GetProjects.ProjectID#">
<INPUT TYPE="hidden" NAME="Customer" VALUE="#GetProjects.Customer#">
<INPUT TYPE="hidden" NAME="Unit" VALUE="#GetProjects.Unit#">
<INPUT TYPE="hidden" NAME="Name" VALUE="#GetTasks.Name#">
<INPUT TYPE="hidden" NAME="JobType" VALUE="#GetTasks.Jobtype#">
<INPUT TYPE="hidden" NAME="Labor" VALUE="#GetTasks.Labor#">
<INPUT TYPE="hidden" NAME="Travel" VALUE="#GetTasks.Travel#">
<INPUT TYPE="hidden" NAME="TotalHours" VALUE="#TotalHours#">
<INPUT TYPE="hidden" NAME="PartNumber" VALUE="#GetParts.Name#">
<INPUT TYPE="hidden" NAME="PartDescription" VALUE="#GetParts.Description#">
<INPUT TYPE="hidden" NAME="qty" VALUE="#GetParts.qty#">
<INPUT TYPE="hidden" NAME="Price" VALUE="#GetParts.price#">
<INPUT TYPE="hidden" NAME="ShipDate" VALUE="">
<INPUT TYPE="hidden" NAME="Reason" VALUE="Modify">
<INPUT TYPE="hidden" NAME="TaskID" VALUE="#GetTasks.TaskID#"></CFOUTPUT>
</p>
</TD>
<TD ALIGN="right" CLASS="Text1"><IMG SRC="/intranet/images/spacer.gif" WIDTH=14 HEIGHT=5 BORDER=0 ALT=""><INPUT NAME="Continue" TYPE="submit" VALUE="Continue" CLASS="FormButton"></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</DIV></CFFORM>

 

[susanh]

Ok Guys,

I think I got it.....

I changed my form so I output my data but I only have 2 form variables.

My checkbox

<input name="billed" type="checkbox" value="Yes" />

2 hidden form fields...

<INPUT TYPE="hidden" NAME="Reason" VALUE="Modify">
<INPUT TYPE="hidden" NAME="ProjectID" VALUE="#GetTasks.ProjectID#">

So you hit the check boxes and then hit the button...

The Action page does...

<CFLOOP INDEX="Checked_ITEM" LIST="#Form.ProjectID#">
<CFQUERY NAME="Billing" DATASOURCE="intranet" DBTYPE="ODBC">
UPDATE Tasks
SET
billed ='#Checked_ITEM#'
WHERE ProjectID = #FORM.ProjectID#
</CFQUERY>
</CFLOOP>

I modified my database a bit where I added the "Billed" column.

So my data updates the correct tasks....YEAH!!

The only problem I have is that it updates the column with a '10' instead of 'Yes'

Did I come up with a correct solution? Do you think?

 

[susanh]

Ok I figured out what I did for the 10, but not sure what I need to change to make input the Yes instead

 

[susanh]

Ok. I got it. I change my List to form.billed

 

[imstillatwork]

check the value of the form field that you are inserting into the DB!

why on earth would the value be 'yes'? You've entered a projectID value in the hidden form field. that value goes to the action page and the cfloop. cfloop is looping a list (in this example you've posted the list only has one item...) The cfloop index is checked_ITEM, which will contain an individual projectID from the list you're looping. I can only assume that you wanted to put billed = '#form.billed#' in the query.

no the next part of the query, the WHERE projectID = #form.projectID# - - this works FOR NOW... it will FAIL as soon as you actually have more than one projectID that you are looping over. it should read WHERE projectID = #Checked_ITEM#

remember what I showed you in my other post with multiple checkboxes and how the values are sent? Back on your original forms action page use <cfdump var="#form#"> to see the form variables as the are posted to the action page. maybe that will help visualize whats going on. CFDUMP should be your best friend next to the cfml reference, and tek-tips















Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[susanh]

Man...I thought I was doing good

Just kidding. I appreciate the help more than you know.

Kevin - most of your stuff cut off so I was unable to read some of it.

I am using the checkboxes as a way to flag tasks to be billed. I thought using a value of "Yes" and updating that column in the DB would be a great way to do that.

I will never be looping more than one projectID.

 

[imstillatwork]

I thought using a value of "Yes" and updating that column in the DB would be a great way to do that."

It is the right idea.

Is there more than one task per projectID?






Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[susanh]

Yes...could be several