3 Replies Latest reply on Jan 28, 2014 7:08 AM by gwarmonger

    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!

        • 1. Re: How to INSERT INTO a specific row?
          Carl Von Stetten Adobe Community Professional & MVP

          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.

          • 2. Re: How to INSERT INTO a specific row?
            gwarmonger Level 1

            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?

            • 3. Re: How to INSERT INTO a specific row?
              gwarmonger Level 1

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