Inserting multiple form fields into 1 DB table.

Report · Dec 09, 2010

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

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

<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?

Report · Dec 09, 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

Report · Dec 09, 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.

Processing page

process as required.

Report · Dec 09, 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.

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)#'

</cfif>

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

The BusinessID_1 column cannot be found in the Products table.

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>

Report · Dec 09, 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.

Report · Dec 11, 2010

I changed the process form to the following..

=======================================

<cfloop index="field" list="#Form.fieldnames#">
<cfif right(field, 2 ) is "_1">
<cfset ThisRecord = RemoveChars(field, 1, 2)>
<cfset ThisValue = form[field]>
</cfif>
<cfquery datasource="wyi2">
INSERT INTO Products(BusinessID, ProductDiscription, ProductPrice)
VALUES ('#Trim(ThisValue)#', '#Trim(ThisValue)#', '#Trim(ThisValue)#')
</cfquery>

</cfloop>

========================================

Still coming up with errors. The form page is still the same as i have posted previously.

========== Error ====================

Error Executing Database Query.

Data type mismatch in criteria expression.

The error occurred in C:\ColdFusion9\wwwroot\wyi\events_process.cfm: line 8

6 :   <cfquery datasource="wyi2">
7 :   INSERT INTO Products(BusinessID, ProductDiscription, ProductPrice)
8 :   VALUES ('#Trim(ThisValue)#', '#Trim(ThisValue)#', '#Trim(ThisValue)#')
9 :   </cfquery>
10 :

VENDORERRORCODE	3079
SQLSTATE
SQL	INSERT INTO Products(BusinessID, ProductDiscription, ProductPrice) VALUES ('Chips Ahoy Cookies (Rainbows)', 'Chips Ahoy Cookies (Rainbows)', 'Chips Ahoy Cookies (Rainbows)')

As you can see in the sql error message it's not displaying the BusinessID and the ProductPrice fields, also i entered in 2 different rows / 2 diferent products with prices with same BusinessID.

Report · Dec 11, 2010

The error message has all the info you need. Is there part of it you are having trouble understanding?

Report · Dec 11, 2010

You're trying to insert the same form field into three database columns - two of which presumably require numeric values. You'll need three form fields for each row of data you want to insert.

So, let's say you have fields like this in your form: BusinessID_1, ProductDescription_1, ProductPrice_1, BusinessID_2, ProductDescription_2, ProductPrice_2, etc. It would make things easier for you if you had one additional field to track the number of rows in your form, and use that for your loop counter rather than looping over form fields indiscriminately.

INSERT INTO ...

VALUES (<cfqueryparam cfsqltype="cf_sql_integer" value="#thisBusinessID#">,

<cfqueryparam cfsqltype="cf_sql_varchar" value="#thisProductDescription#">,

<cfqueryparam cfsqltype="cf_sql_numeric" value="#thisProductPrice#">)

</cfquery>

</cfloop>

I used CFQUERYPARAM to sanitize the form inputs - depending on your specific database, you might have to use "cf_sql_money" instead of "cf_sql_numeric".

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

Dave Watts, Eidolon LLC

Report · Dec 11, 2010

Thanks Dave for your post. Was very detailed and easy for me to understand and thats important for a newbie like me. I followed what you said and got this message when i tried to insert the BusinessID, ProductDescription, and ProductPrice...

======== ERROR =======

Element BusinessID_1 is undefined in a Java object of type class coldfusion.filter.FormScope.


The error occurred in C:\ColdFusion9\wwwroot\wyi\events_process.cfm: line 3
1 : <cfloop from="1" to="#Form.NumberOfRows#" index="i"> 2 : 3 : <cfset thisBusinessID = Form["BusinessID_" & i]> 4 : <cfset thisProductDescription = Form["ProductDescription_" & i]> 5 : <cfset thisProductPrice = Form["ProductPrice_" & i]>

Don't understand the error message. It's safe to say it beyond my "Programming" knowledge.

Again thanks.

Report · Dec 12, 2010

Well, you'll need to change your form accordingly as well:

business id 1: <input type="text" name="businessid_1">

product description 1: <input type="text" name="productdescription_1">

product price 1: <input type="text" name="productprice_1">

<br/>

business id 2: <input type="text" name="businessid_2">

product description 2: <input type="text" name="productdescription_2">

product price 2: <input type="text" name="productprice_2">

<br/>

...

business id 10: <input type="text" name="businessid_10">

product description 10: <input type="text" name="productdescription_10">

product price 10: <input type="text" name="productprice_10">

</form>

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

Dave Watts, Eidolon LLC

Report · Dec 12, 2010

the hidden field works fine and it inserts whatever i input into the different fields, but i get this error even though it inserts.

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.


The error occurred in C:\ColdFusion9\wwwroot\wyi\events_process.cfm: line 10
8 : VALUES (<cfqueryparam cfsqltype="cf_sql_integer" value="#thisBusinessID#">, 9 : <cfqueryparam cfsqltype="cf_sql_varchar" value="#thisProductDiscription#">, 10 : <cfqueryparam cfsqltype="cf_sql_numeric" value="#thisProductPrice#">) 11 : </cfquery> 12 :

I was wondering if the quotes in the error are the empty fields that is trying to insert?

Report · Dec 12, 2010

That would do it.

Cfqueryparam has a null attribute you can use. Assuming that you are willing to accept null values in that field, you want this sort of logic.

Before the query.

if the field is blank

isnull = true

else

isnull = false

in the queryparam tag,

null = "#isnull#"

Report · Dec 12, 2010

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

Report · Dec 12, 2010

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:

... 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

Dave Watts, Eidolon LLC

Report · Dec 12, 2010

That did it. Thanks for all the help.

Inserting multiple form fields into 1 DB table.

1 Correct answer

Error Executing Database Query.

The BusinessID_1 column cannot be found in the Products table.

Error Executing Database Query.

Element BusinessID_1 is undefined in a Java object of type class coldfusion.filter.FormScope.

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.