Update database records

[angflem]

Hello!
I need help with an update query on my CF page. I am creating a shift bid process, each agent will see a list of available shifts and will be able to bid for these shifts based on number preference, 1 is first shift they want, 2 is 2nd choice, etc.

When I enter the pick number in different available shift rows and hit the update button, those picks are all written into the first record of the dataset showing as 1,2,3,4,5.

I have created a unique ID code for each shift and assumed that field was the one that was used to make the update to the table, but it doesn't seem to. I'm attaching the code for the page and would appreciate any suggestions.
Thank you!!

 

[philhege]

My apologies for not being able to view your code from this location, but I assume that you have a dropdown or SELECT list for the shift preference rankings. You've named your shift list the same for every shift record in the form. You need some way to identify the rows uniquely, e.g. by appending the record's identity field to the picklist name:

<SELECT name="pick_#rec_identity#"><!--- where #rec_identity# is the ID of the shift record --->
<option value="1">
.
.
.

In the action form you can spin through the list of submitted form objects and parse the record ID from the SELECT name.



Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[angflem]

Hi! Not sure I understand? There is not drop down box or list of choices, it's an open text field. I have the unique id number hidden so it is not displayed, but for each unique id number I need to update the text to the database field.

Here is the first section of code:

<!---Get agent data--->
<cfquery name="agt" datasource="shifts" result="result">
SELECT Distinct AgentName, HireDate, SenNum
From tbl_Bid_Info
WHERE SSNo Like <!---'%#FORM.SSNo#%'--->'678912'
</cfquery>
<!---Get available shifts--->
<cfquery name="avail" datasource="shifts" result="result">
SELECT AgentName, BidDate, SchedNo, StartTime, EndTime, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, BidNum, Id
From tbl_Bid_Info
WHERE SSNo Like <!---'%#FORM.SSNo#%'--->'678912'
ORDER BY SchedNo
</cfquery>
<!---Update form--->
<cfform action="bid_mix_upd.cfm">
<!---Embed Id as hidden field--->
<cfoutput>
<input type="hidden" name="Id" value=#avail.Id#>
</cfoutput>
<h1><font size="2" face="Arial, Helvetica, sans-serif">Agent Data</font></h1>
<!---Display agent data table--->
<TABLE cellpadding="1" cellspacing="1" border="1" align="center">
<th width="304"><font size="2" face="Arial, Helvetica, sans-serif">AgentName</font></th>
<th width="114"><font size="2" face="Arial, Helvetica, sans-serif">HireDate</font></th>
<th width="69"><font size="2" face="Arial, Helvetica, sans-serif">Seniority Number</font></th>
<cfoutput query="agt">
<tr>
<td><div align="center"><font face="Arial, Helvetica, sans-serif">#AgentName#</font></div></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif">#DateFormat(HireDate, "MM/DD/YY")#</font></div></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif">#SenNum#</font></div></td>
</tr>
</cfoutput>
</table>

And here is the second section, table and input code:

<!---Display avail shift table--->
<TABLE border="0" align="center" cellpadding="1" cellspacing="3" bordercolor="#FFFFFF">
<th><font size="2" face="Arial, Helvetica, sans-serif">Sched#</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Start Time</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">End Time</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Sunday</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Monday</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Tuesday</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Wednesday</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Thursday</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Friday</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Saturday</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">ID Number</font></th>
<th><font size="2" face="Arial, Helvetica, sans-serif">Bid Number</font></th>
<cfoutput query="avail">
<!---Set alternating row color--->
<cfif CurrentRow MOD 2 IS 1>
<cfset bgcolor="##CCCCCC">
<cfelse>
<cfset bgcolor="White">
</cfif>
<tr bgcolor="#bgcolor#">
<td><div align="center"><font face="Arial, Helvetica, sans-serif">#SchedNo#</font></div></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif">#TimeFormat(StartTime, "h:mm tt")#</font></div></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif">#TimeFormat(EndTime, "h:mm tt")#</font></div></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif"><cfif #Sunday# IS "1"><strong> X </strong><cfelse></font></div></cfif></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif"><cfif #Monday# IS "1"><strong> X </strong><cfelse></font></div></cfif></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif"><cfif #Tuesday# IS "1"><strong> X </strong><cfelse></font></div></cfif></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif"><cfif #Wednesday# IS "1"><strong> X </strong><cfelse></font></div></cfif></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif"><cfif #Thursday# IS "1"><strong> X </strong><cfelse></font></div></cfif></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif"><cfif #Friday# IS "1"><strong> X </strong><cfelse></font></div></cfif></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif"><cfif #Saturday# IS "1"><strong> X </strong><cfelse></font></div></cfif></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif">#ID#</font></div></td>
<td><div align="center"><font face="Arial, Helvetica, sans-serif">
<cfinput type="text"
name="BidNum"
value=#Trim(avail.BidNum)#
required="No"
size="5"
maxlength="10"></font></div></td>
</tr>
</cfoutput>
<td colspan="2" align="center">
<input type="submit" value="Update">
</td> </tr>
</table>

I know it's probably very messy, I'm a beginner at coldfusion and am not sure I'm doing things in the best possible way.

 

[philhege]

Here's what I'm talking about:

<cfoutput>
<input type="hidden" name="Id" value=#avail.Id#>
</cfoutput>

If you have five records to display, your "Id" form field will contain the values for the five records in a comma-delimited list, just as you've seen before. Likewise, your "BidNum" field will have a comma-delimited list of the bid numbers entered by the user.

You need a method of uniquely identifying each row's information. One method I suggested was to name your bid field by incorporating the record ID. So,

<cfinput type="text"
name="BidNum_#avail.ID#"
value=#Trim(avail.BidNum)#
required="No"
size="5"
maxlength="10">



Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[angflem]

Good morning!
I tried this but am not sure on the update piece of it. This is what I originally had:

<cfquery datasource="shifts">
UPDATE tbl_Bid_Info
SET BidNum='#(Form.BidNum)#'
Where ID=#(Form.ID)#
</cfquery>

I've modified this to:
SET BidNum='#(Form.BidNum_#avail.id#)#'
and am getting this error:

Invalid CFML construct found on line 18 at column 27.
ColdFusion was looking at the following text:
#

The CFML compiler was processing:
An expression beginning with (, on line 18, column 14.This message is usually caused by a problem in the expressions structure.
The body of a cfquery tag beginning on line 16, column 2.

I've tried writing it different ways, removing the #'s and the 's and nothing seems to work.
I had assumed because this update query is matched on the ID field that it would update whatever was written into that row on the previous form, guess that's not the case?
Not sure what else to do?

Angie

 

[philhege]

You have nested pound signs. That doesn't work.

Search the forums for the Evaluate() function and discussions thereto. You have to construct the form field name and evaluate its contents before you can use it in the UPDATE statement.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.