Skip to main content
A
Anonymous
December 9, 2010
Answered

Inserting multiple form fields into 1 DB table.

  • December 9, 2010
  • 2 replies
  • 2601 views

Hey everyone. I'm having this problem inserting multiple products into the products table using a form. here is the form.

===== Start of form ======

<cfparam name="URL.BusinessID" default="" />

<cfquery name="qBusiness" datasource="wyi2">
SELECT BusinessID, BusinessName
FROM Business
WHERE BusinessID = #URL.BusinessID#
</cfquery>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><cfoutput query="qBusiness">#BusinessName# Events</cfoutput></title>
<link href="css/wyi.css" rel="stylesheet" type="text/css" />
</head>
  <body>
    <cfform action="events_process.cfm?BusinessID=#BusinessID#" method="post">
        <table border="0" cellspacing="0" cellpadding="2">
          <tr>
            <td> </td>
            <td align="center">BusinessID</td>
            <td align="center">Discription</td>
            <td align="center">Price</td>
          </tr>
          <tr>
            <td>Sale 1</td>
            <td><cfinput type="text" name="BusinessID_1" size="5" value="#URL.BusinessID#"></td>
            <td><cfinput type="text" name="Discription_1" size="50"></td>
            <td><cfinput type="text" name="Price_1" size="5"></td>
          </tr>
          <tr>
            <td>Sale 2</td>
            <td><cfinput type="text" name="BusinessID_2" size="5"></td>
            <td><cfinput type="text" name="Discription_2" size="50"></td>
            <td><cfinput type="text" name="Price_2" size="5"></td>
          </tr>
          <tr>
            <td>Sale 3</td>
            <td><cfinput type="text" name="BusinessID_3" size="5"></td>
            <td><cfinput type="text" name="Discription_3" size="50"></td>
            <td><cfinput type="text" name="Price_3" size="5"></td>
          </tr>
          <tr>
            <td>Sale 4</td>
            <td><cfinput type="text" name="BusinessID_4" size="5"></td>
            <td><cfinput type="text" name="Discription_4" size="50"></td>
            <td><cfinput type="text" name="Price_4" size="5"></td>
          </tr>
          <tr>
            <td>Sale 5</td>
            <td><cfinput type="text" name="BusinessID_5" size="5"></td>
            <td><cfinput type="text" name="Discription_5" size="50"></td>
            <td><cfinput type="text" name="Price_5" size="5"></td>
          </tr>
          <tr>
            <td>Sale 6</td>
            <td><cfinput type="text" name="BusinessID_6" size="5"></td>
            <td><cfinput type="text" name="Discription_6" size="50"></td>
            <td><cfinput type="text" name="Price_6" size="5"></td>
          </tr>
          <tr>
            <td>Sale 7</td>
            <td><cfinput type="text" name="BusinessID_7" size="5"></td>
            <td><cfinput type="text" name="Discription_7" size="50"></td>
            <td><cfinput type="text" name="Price_7" size="5"></td>
          </tr>
          <tr>
            <td>Sale 8</td>
            <td><cfinput type="text" name="BusinessID_8" size="5"></td>
            <td><cfinput type="text" name="Discription_8" size="50"></td>
            <td><cfinput type="text" name="Price_8" size="5"></td>
          </tr>
          <tr>
            <td>Sale 9</td>
            <td><cfinput type="text" name="BusinessID_9" size="5"></td>
            <td><cfinput type="text" name="Discription_9" size="50"></td>
            <td><cfinput type="text" name="Price_9" size="5"></td>
          </tr>
          <tr>
            <td>Sale10</td>
            <td><cfinput type="text" name="BusinessID_10" size="5"></td>
            <td><cfinput type="text" name="Discription_10" size="50"></td>
            <td><cfinput type="text" name="Price_10" size="5"></td>
          </tr>
          <tr>
            <td> </td>
            <td colspan="3" align="center">
              <cfinput type="submit" name="eButton" value="Add all the events">
            </td>
          </tr>
        </table>
    </cfform>

  </body>
</html>

======== End of form ==========

The database table "Products" has ProductID (Primary Key, Auto number), BusinessID, ProductDiscription, ProductPrice.

I wanted to add the products based on how many form rows where filled. I have tried a bunch of cfif tag to only insert if a cert form field is defined and not blank, but no go. I have searched the forums to find someone with a same problem...no go. Can any see what I'm doing wrong or am I making more work then is needed as far as the CF goes?

    This topic has been closed for replies.
    Correct answer Dave Watts

    I dont want to insert any fields that are blank. So how would i use the <cfif> to ignore all blank fields?


    Presumably, if any one of the three fields for a given insert are blank, you won't want to insert that row, right?

    If so, you can simply wrap your insert query with some conditional logic:

    <cfif isNumeric(thisBusinessID) and len(trim(thisProductDescription)) and isNumeric(thisProductPrice)>

    ... your query goes here ...

    </cfif>

    Of course, you might be better served by also including form validation, either in the form itself using JavaScript and/or CFFORM, or in your action page prior to executing any of this code.

    Dave Watts, CTO, Fig Leaf Software

    http://www.figleaf.com/

    http://training.figleaf.com/

    Read this before you post:

    http://forums.adobe.com/thread/607238

    2 replies

    D
    Dan_Bracuk
    Inspiring
    December 9, 2010

    The form fields will always be defined so forget that.  If you checked to see if they were blank, and you did not trim the values, you may have been deked out by a space character.

    As a side note, you can use loops to reduce the amount of code on your pages.

    Form page.

    <cfoutput query="something">

    <cfinput name = "abc#field_from_query#>

    Processing page

    <cfloop list = "#form.fieldnames# index = "field">

    <cfif left(field, 3 ) is "abc">

    <cfset ThisRecord = RemoveChars(field, 1, 3)>

    <cfset ThisValue = form[field]>

    process as required.

    A
    Anonymous
    December 9, 2010

    This is the code i used for 2 rows. If i only use 1 row (BusinessID, Discription_1 and Price_1) it inserts perfectly, but not when trying to do more then one row.

    This is the cfif code i "tried" to use.

    ========Error for this set of code =============

    Error  Executing Database Query.

    Number of query values and  destination fields are not the same.

    VENDORERRORCODE  3092
    SQLSTATE
    SQL   INSERT INTO  Products(BusinessID, ProductDiscription, ProductPrice) VALUES ('33', 'Chips Ahoy  Cookies (Super Chunky)', '2.99' , '33', 'Squirel Peanut Butter (Extra Smooth)',  '4.89' )

    and if you see the sql in the vendorerrorcode the sql is exactly what i want and need, but it won't insert.

    <cfquery datasource="wyi2">

    INSERT INTO Products(BusinessID, ProductDiscription, ProductPrice)

    VALUES ('#Trim(FORM.BusinessID_1)#', '#Trim(FORM.Discription_1)#', '#Trim(FORM.Price_1)#'

    <cfif IsDefined('FORM.BusinessID_2') AND FORM.BusinessID_2 NEQ "">

    , '#Trim(FORM.BusinessID_2)#', '#Trim(FORM.Discription_2)#', '#Trim(FORM.Price_2)#'

    </cfif>

    ========= Error for this set of code =============

    The  BusinessID_1 column cannot be found in the Products table.

    <cfquery datasource="wyi2">

    INSERT INTO Products(BusinessID, ProductDiscription, ProductPrice)

    VALUES ('#Trim(FORM.BusinessID_1)#', '#Trim(FORM.Discription_1)#', '#Trim(FORM.Price_1)#', '#Trim(FORM.BusinessID_2)#', '#Trim(FORM.Discription_2)#', '#Trim(FORM.Price_2)#')

    </cfquery>

    M
    meensi
    Inspiring
    December 10, 2010

    Hi,

    I think, you need to loop your query to run all the rows.

    get the no of rows and loop it over. put your CFQUERY inside your CFLOOP.

    I hope it will work.

    I'm thinking that You are trying insert each row for every row in your application.

    Let me know if my understanding is correct.

    R
    Reed_Powell-ttnmOb
    Inspiring
    December 9, 2010

    We'll need to see the code that is failing in CF to help you out.  Since those are all text form fields, they should all be defined in the form scope of the action page, so you should just need to test to see which are blank.  Post your code, we'll try to help.

    -reed

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices