How to INSERT INTO a specific row?

[gwarmonger]

I have a form with a calendar at the top, below it is a list of items each with a check box.

Here is a wonderful visual of it -

Calendar

Checkbox | Col | Col | Col

Checkbox | Col | Col | Col

Checkbox | Col | Col | Col

I want it so you select the date and it gets passed into the database in the same row as the one (definitely one, multiple is better) that has been checked by the user.

I was trying an INSERT INTO with a WHERE statement but apparently you can't do that. So if I can use a WHERE with INSERT INTO, how can I identify which row to insert the info?

Currently it is passing the dates but it creates a new row.

I didn't think posting the code is neccisary for this question but if anyone wants to see it let me know and I will post it.

THANKS!

[Carl Von Stetten · Accepted solution]

An INSERT operation will *always* do just that, insert rows into a table.  Is sounds like maybe you should be doing an UPDATE operation instead.  Can you give a bit more explanation on the database table design and what each row in the table represents?  Maybe walk through the user interaction more too?  And including relevant code is usually helpful.

-Carl V.

[gwarmonger]

Ah, ok, I will try UPDATE.

Right now my form looks like this...

<cfform name="requestform" role="form"action="resources/requestform.cfm" method="post"  width="375" height="350" > 
    <label for="startdate">Start Date:</label>
      <label for="enddate">Finish Date:</label> 
    <cfcalendar name="selectedDate"  
        selectedDate="#Form.selectdate#" 
        startRange="#Form.startdate#" 
        endRange="#Form.enddate#"  
        mask="mmm dd, yyyy"  
        dayNames="SU,MO,TU,WE,TH,FR,SA" 
        firstDayOfWeek="1" 
        monthNames="JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC" 
        style="rollOverColor:##FF0000" 
        width="200" height="150"> 
            <cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#Form.startdate#">
            <cfinput type="dateField" name="enddate" label="End Date" width="100" value="#Form.enddate#"> 
    </div>
<cfinclude 
    template = "resources/query.cfm">
    <span class="label label-default">Equipment List</span>
    <table class="table table-bordered table-striped">
        <tr>
            <td><b>Select</b></td>    
            <td><b>Name</b></td>
            <td><b>Description</b></td>
            <td><b>Status</b></td>
        </tr>
    <cfloop query = "equiplist">
        <cfoutput>
        <tr>
            <td>
                <cfinput name="status" type="checkbox" value="#serial#">Select:</cfinput>
                <cfinput name="serial"type="text"style="display:none" value="#serial#">
            </td>    
            <td>#name#</td>
            <td>#descrip#</td>
            <td>#status#</td>
        </tr>
        </cfoutput>
</cfloop>
</table>
      <cfinput class="btn btn-default" type="Submit" value="Submit" name="addsubmit"></cfinput>
</cfform>

And the action page looks like this...

<cfquery
        name = "requestform"
        dataSource = "db_cie">
        UPDATE equip
        SET STATUS="Out",
        WHERE serial='#status#'
</cfquery>
<cflocation
    url = "../request.cfm">

But I am getting an error "invalid.user.table.column, table.column, or table specification. But the error message shows the SQL output and it looks correct...UPDATE equip SET STATUS="Out" WHERE serial="325255"... it IS getting the serial number variable passed from the form. ...No update though. Is my action form written wrong?

[gwarmonger]

Ah..fixed it. Needed single quotes instead of double quotes. Update was the ticket. Thanks.