-
1. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
First question are you reading data or writing data?
I think you are writing data, if so you have to write the orderID to all the tables that need it. Just having the fields linked in the Access database properties and relationships is not going cause the value to propergate in some magical manner.
-
2. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Thanks for your reply Ian, and yes I am trying to write data. Bear with me, I'm teaching myself this stuff and know enough to frustrate the crap out of me and not much else. I don't understand how to instruct the database to enter the Orders.ID into all corresponding ProductOrders.OrderID. Since the Orders.ID generates on submission how do I carry it over to the other table?
-
3. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
To get the order id of the record you just entered, do something like this:
select max(order_id) orderid
from your_table
where field1 = the value you just entered
and field2 = the value you just entered
etc
-
4. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Where would I place this code?
-
5. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Perhaps this will help, here is my code:
This is the input form (I stripped out the unecessary stuff and yes I realize the cfinclude appears after the submit button but it is within cfform tag and is submitting everything but the ID number):
<cfform action="mp_order_form2.cfm" method="post">
<cfoutput query="CheckUser">
<input type="hidden" name="UserID" value="#UserID#">
</cfoutput>
<p align="center"><img src="../images/mp_order_header.jpg" width="308" height="91" /></p>
<table width="650" border="0" align="center">
<tr>
<td align="left" bgcolor="#FFFFFF"><font face="Tahoma"><strong>Sale Date*:</strong></font></td>
<td align="left"><cfoutput>
<input name="SaleDate" readonly="readonly" value="#DateFormat(Now(), "mm/dd/yyyy")#" />
</cfoutput></td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>TM Name*: </strong></font></td>
<td align="left">
<cfoutput query="CheckUser">
<cfinput name="TerritoryManager" readonly="readonly" type="text" id="TerritoryManager" value="#UserFirstName# #UserLastName#" />
</cfoutput></td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>Distributor*:</strong></font></td>
<td align="left"><cfselect size="1" name="Distributor" required="Yes" message="Please select a distributor">
<cfoutput query="distributors">
<option value="#distributors.Field2#">
#distributors.Field2# </option>
</cfoutput>
</cfselect></td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>Dealer:</strong></font></td>
<td align="left"> </td>
</tr>
<tr>
<td align="left"><cfoutput query="dealer_info"><input type="hidden" name="DealerID" value="#dealer_info.ID#"></td></tr>
<tr>
<td colspan="2" align="center" bgcolor="##CCCCCC"><font face="Tahoma" size="+1"><strong>#DealerName#</strong></font><br>
<font face="Tahoma">#DealerAddress#</font><br>
<font face="Tahoma">#DealerCity#, #DealerState# #DealerZIPCode#</font><br>
<br><br>
<font face="Tahoma">(#Left(DealerPhone,3)#) #Mid(DealerPhone,4,3)#-#Right(DealerPhone,4)#</font></td></tr></cfoutput>
<tr>
<td align="left" class="style8"> </td>
<td align="left"> </td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>PO Number: </strong></font></td>
<td align="left"><cfinput type="text" name="PONumber" /></td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>Promotion:</strong></font></td>
<td align="left">
<cfselect name="Variable" size="1" id="Variable">
<option value="No promotion" selected="selected">No promotion</option>
<option value="A+ Promotion (Send Kit)">A+ Promotion (Send Kit)</option>
<option value="A+ Promotion (Do Not Send Kit)">A+ Promotion (Do Not Send Kit)</option>
<option value="Chick Days Pre-book">Chick Days Pre-book</option>
<option value="Fly Spray Pre-book">Fly Spray Pre-book</option>
<option value="ISO Program">ISO Program</option>
<option value="Promotion plus additional products">Promotion plus additional products</option>
</cfselect></td>
</tr>
<tr>
<td align="left"> </td>
<td align="left"></td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>ISO Check: </strong></font></td>
<td align="left">
<cfselect name="ISOCheck">
<option value="Retail ISO Placement">Retail ISO Placement</option>
<option value="DSR Spiff Check">DSR Spiff Check</option>
</cfselect>
</td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>ISO Check Number: </strong></font></td>
<td align="left"><cfinput type="text" name="ISOCheckNumber" /></td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>ISO Check Amount: </strong></font></td>
<td align="left">
<cfinput type="text" name="ISOCheckAmount" /> </td>
</tr>
<tr>
<td align="left"> </td>
<td align="left"> </td>
</tr>
<tr>
<td align="left"><font face="Tahoma"><strong>Customer Service Call Back: </strong></font></td>
<td align="left">
<cfselect name="CallBack">
<option value=" " selected="selected"> </option>
<option value="Yes">Yes</option>
<option value="No">No</option>
</cfselect> </td>
</tr>
<tr>
<td align="left"> </td>
<td align="left"> </td>
</tr>
</table>
<hr align="center" width="65%" color="#999999">
<table width="677" align="center">
<tr>
<td height="22" colspan="4"><font face="Tahoma"><strong>Product Orders:</strong> </font></td>
</tr>
<tr>
<td width="314" bgcolor="#CCCCCC"><div align="center"><font face="Tahoma" size="-1"><strong>Product</strong></font></div></td>
<td width="62" bgcolor="#CCCCCC"><div align="center"><font face="Tahoma" size="-1"><strong>Qty</strong></font></div></td>
<td width="124" bgcolor="#CCCCCC"><div align="center"><font face="Tahoma" size="-1"><strong>Size</strong></font></div></td>
<td width="157" bgcolor="#CCCCCC"><div align="center"><font face="Tahoma" size="-1"><strong>New or Existing at Dealer</strong></font></div></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td colspan="4"><font face="Tahoma" size="+1"><strong>Comments:</strong></font></td>
</tr>
<tr>
<td colspan="4"> </td>
</tr>
<tr>
<td colspan="4"><font face="Tahoma">* - indicates a required field that must be filled in to submit form</font> </td>
</tr>
<tr>
<td colspan="4">
<div align="center">
<input type="submit" value="Submit">
<input type="reset" value="Reset">
</div> </td>
</tr>
</table>
<cfinclude template="product_input.cfm" >
</cfform>
</table>Here is the cfinclude template:
<table width="579">
<tr>
<td>
<cfselect name="Product">
<option value="" selected="selected"> </option>
<cfoutput query="products">
<option value="#products.Product#">#products.Product#</option>
</cfoutput>
</cfselect>
</td>
<td>
<cfselect name="Quantity">
<option value="" selected="selected"> </option>
<cfoutput query="quantity">
<option value="#quantity.Quantity#">#quantity.Quantity#</option>
</cfoutput>
</cfselect>
</td>
<td>
<cfselect name="BagsCases" id="BagsCases">
<option value=" " selected="selected"> </option>
<option value="Bags / Eaches">Bags / Eaches</option>
<option value="Cases">Cases</option>
<option value="Pallet">Pallet</option>
</cfselect>
</td>
<td>
<cfselect name="NewExisting" id="NewExisting">
<option value=" " selected="selected"> </option>
<option value="New">New</option>
<option value="Existing">Existing</option>
</cfselect>
</td>
<td><label>
<input type="text" name="OrderComments" />
</label> </tr>
</table>And finally, the action page:
<cftransaction>
<cfinsert datasource="manna_premier" formfields="SaleDate,TerritoryManager,Distributor,DealerID,PONumber,Variable,ISOCheck,ISO CheckNumber,ISOCheckAmount,CallBack,Comments,UserID" tablename="Orders">
<cfinsert datasource="manna_premier" formfields="OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments" tablename="ProductOrders">
</cftransaction><cflocation url="mp_order_form3.cfm?ID=#FORM.DealerID#">
-
6. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
The usual process for this is something like this:
1) Insert data into parent table.
2) Query generated record ID out of parent table
3) Insert data into related child table(s) using that ID.
There are some dangers with the select max(id) with heavily utilized, enterprise database systems. But as access does not qualify as one of those, you should be ok to use this method.
-
7. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
So I would have to abandon the cfinclude and present the child table form separately with the previous mentioned query?
-
8. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
NO
You would just need to add a SELECT query between your two inserts that will retreive the ID from the first insert and then use that ID in the second insert.
-
9. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
You would just need to add a SELECT query between your two inserts that will retreive the ID from the first insert and then use that ID in the second insert.
And do the whole lot as a transaction, so that the select is definitely querying for the record inserted by the first insert for it's request, not some other one that occurs at a similar time.
--
Adam
-
10. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
ianskinner wrote:
There are some dangers with the select max(id) with heavily utilized, enterprise database systems. But as access does not qualify as one of those, you should be ok to use this method.
... though since you are using MS Access AND cftransaction, you can also use @@IDENTITY to get the new OrderID IIRC.
-
11. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
-==cfSearching==- wrote:
ianskinner wrote:
There are some dangers with the select max(id) with heavily utilized, enterprise database systems. But as access does not qualify as one of those, you should be ok to use this method.
... though since you are using MS Access AND cftransaction, you can also use @@IDENTITY to get the new OrderID IIRC.
Yikes.
I didn't notice they said they're using Access.
OK, so now the first advice should be "if possible, stop using Access to serve data for a website. It's not fit for that purpose".
--
Adam
-
12. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
I'm a little unclear on the SELECT statement, specifically the WHERE clause. Also, would the select statement be between query tags?
<cftransaction>
<cfinsert datasource="manna_premier" formfields="SaleDate,TerritoryManager,Distributor,DealerID,PONumber,Variable,ISOCheck,ISO CheckNumber,ISOCheckAmount,CallBack,Comments,UserID" tablename="Orders">
SELECT MAX(ID) AS OrderID
FROM Orderswhere field1 = the value you just entered
and field2 = the value you just entered
<cfinsert datasource="manna_premier" formfields="OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments" tablename="ProductOrders">
</cftransaction> -
13. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Yes the sql has to be in a cfquery tag. You'll also have to convert your 2nd cfinsert to a cfquery because the orderid is not coming from a form.
This:
where field1 = the value you just entered
and field2 = the value you just entered
would resemble something like
where saledate = <cfqueryparam value = "#form.saledate#">
and TerritoryManager = <cfqueryparam value = "#form.TerritoryManager#">
etc
If this is a school assignment, you might be ok. If it's for real, it needs a lot of work to userproof it.
-
14. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Okay...now I'm even more confused. Although the OrderID is not coming from a form all the other fields are. Can't I just address the OrderID somehow? I tried this but it doesn't work. What am missing here?
<cftransaction>
<cfinsert datasource="manna_premier" formfields="SaleDate,TerritoryManager,Distributor,DealerID,PONumber,Variable,ISOCheck,ISO CheckNumber,ISOCheckAmount,CallBack,Comments,UserID" tablename="Orders">
<cfquery name="qGetID" datasource="manna_premier">
SELECT MAX(ID) AS OrderID
FROM Orders
</cfquery><cfinsert datasource="manna_premier" formfields="OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments" tablename="ProductOrders">
</cftransaction> -
15. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Thank you Dan and everyone else who contributed on this...YOU GUYS ARE DA BOMB!!!! This is the code that cured the problem. Thanks again1
<cftransaction>
<cfinsert datasource="manna_premier" formfields="SaleDate,TerritoryManager,Distributor,DealerID,PONumber,Variable,ISOCheck,ISO CheckNumber,ISOCheckAmount,CallBack,Comments,UserID" tablename="Orders">
<cfquery name="qGetID" datasource="manna_premier">
SELECT MAX(ID) AS OrderID
FROM Orders
</cfquery><cfquery name="AddProduct" datasource="manna_premier">
INSERT INTO ProductOrders
VALUES ('#qGetID.OrderID#', '#Form.Product#','#Form.Quantity#', '#Form.BagsCases#','#Form.NewExisting#', '#Form.OrderComments#')
</cfquery></cftransaction>
-
16. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
<cfquery name="qGetID" datasource="manna_premier">
SELECT MAX(ID) AS OrderID
FROM Orders
</cfquery>
You may want to read the comments again. That is not the query that was suggested.
-
17. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Yeah I know. The query I wrote worked until I added more than one record at a time...then it forced all the records I entered into one line. I now realize that the query is generating only one number. I only want one number, but I need multiple instances of that number. Any thoughts?
-
18. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
then it forced all the records I entered into one line.
I am not sure what you mean by that. My comment was that this query:
SELECT MAX(ID) AS OrderID
FROM Orders
Is not the proper way to get the new Autonumber value created by the previous insert statement. That query would return the maximum record ID in that table, regardless of when it was entered.
-
19. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
What is the proper way to retrieve the new Autonumber? What I meant was that now when I enter multiple products I get this result in my database;
OrderID Products Quantity BagsCases NewExisting OrderComments 1 Product, Product, Product, Product 10,12,6,8 Bags, Bags, Cases, Bags Existing,New,New,New test,test,test,test I need it to look like this;
OrderID Products Quantity BagsCases NewExisting OrderComments 1 Product 10 Existing test 1 Product 12 New test 1 Product 6 New test 1 Product 8 New test How do I do this? What am I doing wrong?
-
20. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Is this for a live application or a school assignment? MS Access is not really recommended for live applications. Plus, as Dan already mentioned your code needs work to make it user proof.
As far as grabbing the Autonumber value, there are a few options for Access. It is important to grab the correct ID, or you will end up linking information under the wrong record totally corrupting your data.
1. One option for MS Access is to use @@IDENTITY. SELECT this value directly after the first insert to retrieve the new Autonumber created in the previous statement
2) Dan described another option for grabbing the inserted Autonumber value. While I am not a fan of this method, it can work in some situations.
Your current query does not properly implement that approach. It simply grabs the MAX id in the entire table. That is not necessarYou do not want the MAX id in the entire table. You want the ID of the record you just inserted. To identify that record, you need to filter on the key values you just entered.
ie
SELECT MAX(ID) AS OrderID FROM SomeTable
WHERE SaleDate = '#form.SaleDate#'
AND TerritoryManager = '#form.TerritoryManager#'
.... etcetera ...
3. Another option is to use a UUID value:
http://mysecretbase.com/get_the_last_id.cfm
What I meant was that now when I enter multiple products I get
this result in my database;
That sounds like it has more to do with your FORM, than the query. When you give multiple form fields the same name, the values will be sent to the action page as a comma delimited list. On your action page, dump the FORM scope so you can see what values are submitted. If the fields contain multiple values like "10,12,6,8", the problem is your form.
<cfdump var="#FORM#"
-
21. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Where could I find an example of the type of form I need to use? My form is returning a comma delimited list but I don't know how to work around this. How would I structure the input form to accept multiple products with the same OrderID number? Any help on this would be greatly appreciated.
-
22. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
You can use cfloop list ="#form.something#" to handle your lists. If the list comes from checkboxes, you'll need to ensure that at least one of the checkboxes was checked.
-
23. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Dan Bracuk wrote:
You can use cfloop list ="#form.something#" to handle your lists.
The only problem with that method is it breaks down if the form field values contain commas. With forms that add/edit multiple entries, I prefer using separate field names. Fields generated by a query loop can be named dynamically using the query objects #currentRow# variable.
<cfoutput query="yourQuery">
<input name="FirstName#currentRow#" ....>
<input name="LastName#currentRow#" ....>
</cfoutput>That will produces field names like FirstName1, FirstName2, etcetera. The total number of fields can be stored in a hidden field _outside_ the query loop:
<cfoutput query="yourQuery">
<input type="hidden" name="numOfFields" value="#yourQuery.recordCount#">
</cfoutput>On the action page, retrieve the submitted field values individually using a loop and associative array notation:
<cfparam name="form.numOfFields" default="0">
<cfloop from="1" to="#form.numOfFields#" index="counter">
<cfset firstNameValue = FORM["FirstName"& counter]>
<cfset lastNameValue = FORM["LastName"& counter]>
... use the values in a query ...
</cfloop> -
24. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Okay...I now fully understand the issues surrounding the query to retrieve the ID number. I want to do this right and it looks like @@IDENTITY is the way to go. Problem is I have been unsuccessful finding any examples or documentation of this as it relates to ColdFusion. I have a hard enough time deciphering CF queries much less those written in other programming languages. Is this the correct way to write and use this;
<cfquery name="qGetID" datasource="manna_premier">
SELECT @@IDENTITY AS Identity
FROM Orders
</cfquery>When I run this, and am entering only one single product it appears to perform correctly. I just want to make sure that this part is correct before I move on to tackle the issue of multiple entries. Thanks for all your help guys, especially '-==cfSearching==-', I'd love to pick your brain for a week or two! LOL!!
-
25. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
emartek1 wrote:
<cfquery name="qGetID" datasource="manna_premier">SELECT @@IDENTITY AS Identity
FROM Orders
</cfquery>@@Identity is an independent variable, so you do not need the FROM clause. Just select the variable and give it whatever column alias you want.
<cfquery name="qGetID" datasource="manna_premier">
SELECT @@IDENTITY AS NewOrderID
</cfquery> -
26. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
How does @@IDENTITY know what it is supposed to retrieve? Is it because of its location (sandwiched between my insert queries) that it knows what table to retrieve the record from?
Also, I think I understand the base concept you described for the next step in the process...the input form. The one area I don't quite get is the query for inputting the field data. My current form uses 4 drop-down lists and 1 text box. I want a maximum of 20 entries. Would I repeat the query 20 times to make the input form or wrap all 20 in one query, or is their a way to dynamically generate this?
-
27. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
How does @@IDENTITY know what it is supposed to retrieve? Is it because of its location (sandwiched between my insert queries)
that it knows what table to retrieve the record from?
Yes, location does play a key role. But think of it more like a connection level function, rather than being "table aware". Whenever you call @@IDENTITY it will return the last identity/autonumber value created on the current database connection, regardless of the source table . So say you inserted two separate records to the Orders table. One after the other. Then called @@IDENTITY. It would return the id from the insert 2 because it was the _last_ statement that generated a new autonumber value. Of course I am more familiar with MS SQL than Access, but they are similar with regards to @@IDENTITY.
<!--- insert 1 --->
INSERT INTO Orders (....) VALUES (....)
<!--- insert 2 --->
INSERT INTO Orders (....) VALUES (....)
<!--- would return the autonumber from "insert 2" --->
SELECT @@IDENTITY AS OrderID
Though having said all that, MS Access is really a desktop database. Databases like MS SQL, MySQL, etcetera. are far more robust and are better suited for use in web applications, where concurrent access is needed.
Would I repeat the query 20 times to make the input form or wrap all 20
It all depends on the form and how you are generating the fields. Truthfully, I only skimmed your form. But usually if you are populating multiple lists with the same data, you only run the query once. Then create some sort of loop that generates X number of fields, but reuses the query to populate each list. -
28. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
<cftransaction>
<cfinsert datasource="manna_premier" formfields="SaleDate,TerritoryManager,Distributor,DealerID,PONumber,Variable,ISOCheck,ISO CheckNumber,ISOCheckAmount,CallBack,Comments,UserID" tablename="Orders">
Here is my action page...
<cfquery name="qGetID" datasource="manna_premier">
SELECT @@IDENTITY AS Identity
</cfquery>
<cfset ordercount = 0>
<!-- Start Loop -->
<cfloop index="Add" from="1" to="#form.howmany#" step="1">
<cfset ordercount = ordercount + 1>
<cfoutput><cfset OrderID = "qGetID.Identity"></cfoutput>
<cfset Product = "Form.Product_#ordercount#">
<cfset Quantity = "Form.Quantity_#ordercount#">
<cfset BagsCases = "Form.BagsCases_#ordercount#">
<cfset NewExisting = "Form.NewExisting_#ordercount#">
<cfset OrderComments = "Form.OrderComments_#ordercount#">
<cfquery datasource="manna_premier" name="InsertData">
Insert into ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments)
values (#OrderID#,#Product#,#Quantity#,#BagsCases#,#NewExisting#,#OrderComments#)
</cfquery>
</cfloop></cftransaction>
<cflocation url="mp_order_form3.cfm?ID=#FORM.DealerID#">
And I am getting this error...
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 6. The error occurred in D:\Inetpub\mannapremier\mp_order_form2.cfm: line 24 22 : <cfquery datasource="manna_premier" name="InsertData"> 23 : Insert into ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments) 24 : values (#OrderID#,#Product#,#Quantity#,#BagsCases#,#NewExisting#,#OrderComments#) 25 : </cfquery> 26 : </cfloop>
SQLSTATE 07002 SQL Insert into ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments) values (qGetID.Identity,Form.Product_1,Form.Quantity_1,Form.BagsCases_1,Form.NewExisting_1,Form. OrderComments_1) VENDORERRORCODE -3010 DATASOURCE manna_premier Resources:
Any ideas why? -
29. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
INSERT INTO ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments)
VALUES (qGetID.Identity, Form.Product_1,Form.Quantity_1,Form.BagsCases_1,Form.NewExisti ng_1,Form.OrderComments_1)Silly as it may sound, the best place to start with database errors is by looking at the error message. If you look at the SQL statement in yours, you should immediately notice it is inserting the literal names of your variables instead of their values. That is due to how you're setting the variables in the code.
<cfset OrderID = "qGetID.Identity">
1) You accidentally used quotes around the query variable: "qGetID.Identity". So CF has no idea it even is a variable. As a result the value of #OrderID# becomes the literal string "qGetID.Identity", not the value of #qGetID.Identity#. Remove the quotes so CF will treat it as a variable!
<cfset Product = "Form.Product_#ordercount#">
2) Similar problem here. All that statement does is sets the value of #Product# to a literal string like "form.Product_1", "form.Product_2", etcetera. The FORM scope can be used like a structure. So you just need to use array notation to extract the values of those form fields:
<cfset Product = FORM["Product"& orderCount]>
Once you get that straightened out, obviously you need to fix up the datatypes, add cfqueryparam, etcetera.
<cfloop index="Add" from="1" to="#form.howmany#" step="1">
<cfset ordercount = ordercount + 1>
<cfoutput><cfset OrderID = "qGetID.Identity"></cfoutput>BTW: The whole point of using a FROM/TO loop is to track of the loop number. So the extra. "orderCount" variable is redundant. Just make that the name of your cfloop index variable instead of "Add", and obviously the cfoutput can go as well.
-
30. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
What do you mean by fix up the datatypes, add cfqueryparam, etcetera. I eliminated the quotations from the <cfset OrderID = "qGetID.Identity"> and that solved that problem. But when I made the change to the next line <cfset Product = FORM["Product"& orderCount]> and ran the query ( I know the other lines need to be changed too) I get a new error message:
The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.The following information is meant for the website developer for debugging purposes. Error Occurred While Processing Request Element Product1 is undefined in a Java object of type class coldfusion.filter.FormScope.
The error occurred in D:\Inetpub\mannapremier\mp_order_form2.cfm: line 17 15 : <cfset ordercount = ordercount + 1> 16 : <cfset OrderID = qGetID.Identity> 17 : <cfset Product = FORM["Product"& ordercount]> 18 : <cfset Quantity = "Form.Quantity_#ordercount#"> 19 : <cfset BagsCases = "Form.BagsCases_#ordercount#">
I am assuming that I am failing to add something else...please help! -
31. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
I get a new error message:
| | h1. Element Product1 is undefined in a Java object of
type class coldfusion.filter.FormScope.
It just means the CF cannot find a form field named "FORM.Product1". The code I posted was to demonstrate the concept. You may have to modify it match your actual form field names, like add missing underscores, etcetera...
Base syntax:
FORM["BaseFieldName"& dynamicCounter]
FORM["Product"& ordercount] => ie form.Product1, form.Product2, ..
FORM["Product_"& ordercount] => ie form.Product_1, form.Product_2, ..
...etcetera ..
What do you mean by fix up the datatypes, add cfqueryparam,
etcetera.
Well I am guessing not all of the fields are numeric. So I doubt the query would run successfully, even after fixing the errors above.
Also, you should be using cfqueryparam on all query values. It has a number of benefits, but a by-product is sql injection protection.
Right now you are passing the form values directly into the SQL code. User supplied values (FORM, URL, etcetera) should never be used directly in sql. The biggest reason is that it exposes your database to SQL injection. While that is not as applicable to MS Access databases, it will become a problem when you upgrade to a better database.
http://www.adobe.com/devnet/coldfusion/articles/sql_injection.html
-
32. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
OK, label me an idiot but I'm not understanding what I'm doing here. Now I'm getting this error message:
Element Product_1 is undefined in a Java object of type class coldfusion.filter.FormScope.
The error occurred in D:\Inetpub\mannapremier\mp_order_form2.cfm: line 17 15 : <cfset ordercount = ordercount + 1> 16 : <cfset OrderID = qGetID.Identity> 17 : <cfset Product = FORM["Product_"& ordercount]> 18 : <cfqueryparam value = #FORM.Product_& ordercount#> 19 : <cfset Quantity = "Form.Quantity_#ordercount#">
Why is this not working? Am I not writing the cfqueryparam line correctly? Please help!
-
33. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
emartek1 wrote
OK, label me an idiot but I'm not understanding what I'm doing here. Now I'm getting this error message:
You definitely need to read up on cfqueryparam. But cfqueryparam is only for use inside .. cfquery's ;-) It will not work anywhere else.You have not made it to the cfquery yet. So get rid of that line and fix your variable problem first.
Take it one step at a time and approach it logically. If CF is still saying it cannot find a form field named form.Product_1, then CFDUMP all of the fields to see what _is_ being submitted. Most likely the field names do not match your action page code. Put the dump at the top of your action page, before any other code.
<cfdump var="#FORM#">
-
34. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
I finally figured out that you were referring to the input form when you suggested that I change the loop index name and drop the <cfoutput>. This seemed to do the trick...well, at least I could see the correct info coming through in the DUMP. Now I'm getting a new error that I can't figure out.
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3. The error occurred in D:\Inetpub\mannapremier\mp_order_form2.cfm: line 25 23 : <cfquery datasource="manna_premier" name="InsertData"> 24 : Insert into ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments) 25 : values (#OrderID#,#Product#,#Quantity#,#BagsCases#,#NewExisting#,#OrderComments#) 26 : </cfquery> 27 : </cfloop>
SQLSTATE 07002 SQL Insert into ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments) values (808,10029,4,Cases,New,test) VENDORERRORCODE -3010 DATASOURCE manna_premier Resources: The message says 'Too few parameters. Expected 3'. My take on this says there should be six parameters and according to the SQL six are provided.
Why won't this run? BTW...you're right, I need to read up on a lot of things...I have had no formal training in this and if you know of any good CF reference books I'm all ears.
-
35. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
1 person found this helpfulInsert into ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments) values (808,10029,4,Cases,New,test)
Now you are to the part I mentioned earlier ie That I doubt your query would work "as is". The "Too few parameters ..." error is the database's not so intutive way of saying it does not understand your sql statement or what to do with the VALUES.
Forgetting about cfqueryparam for a moment, when you insert values into a table the database needs to know what types of values they are: numbers, strings, dates, etcetera. Numeric values are easy to identify and can be inserted without quotes. But when you insert string values (like 'Cases', 'New', ...) those values must be enclosed in single quotes. Otherwise the database assumes they are object names (column, table, ...) or sql keywords and will not be able to process the statement properly.
BTW...you're right, I need to read up on a lot of things...I have had no formal training in this and if you know of any good CF reference books I'm all ears.
The online CF documentation is invaluable. Whenever I have a question about something, it is usually my first stop.
http://livedocs.adobe.com/coldfusion/8/index.html
For books, IMO the best reference is the CFWACK series (ColdFusion Web Application Construction Kit). Since you are working with databases, a good sql book or some tutorials are also a must. I do not know about intro books, but there are some very good introductory tutorials on w3schools.com
http://www.w3schools.com/SQL/sql_insert.asp
Also, when troubleshooting database errors it often helps to copy the generated SQL and run it directly in your databases. If it does not work there, it will not work in CF. Plus, the error messages you will get there are almost always more informative than ones you will get from the CF database drivers.
-
36. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Forgetting about cfqueryparam for a moment
Of course my last comment was just to explain the error message. The proper way to write queries is with cfqueryparam and using the "cfsqltype" attribute to tell the database what type of values are being passed. Which type you use depends on the data type of the columns in your database table
You can find an example in livedocs.
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html
Since use of MS Access is obviously discouraged, the cfsqltypes for Access are not listed. But I wrote one up a long time ago:
http://cfsearching.blogspot.com/2007/12/cfqueryparam-matrix-for-ms-access.html
BTW: Out of curiousity, why not use MS SQL Express? It is free and is far more powerful than Access ;-)
-
37. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Thanks for the recommendations. I realized after looking at the error message that the SQL shows only 1 complete order. The input form submitted actually had 3 products. It seems that the OrderID is not repeating correctly in the loop. How would I write the OrderID line in my query to make it repeat for each order?
-
38. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
Do not borrow trouble ;-) Each successful iteration of the loop will execute a separate cfquery. If the very first one bombs out, that is it. The execution of the CF code stops, it breaks out of the loop, and that all you will see. Fix the query and it should work fine.
-
39. Re: Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!
I am assuming that placing ' in the query is not the answer, unless I am placing them in the wrong spots. So I guess this is where cfqueryparam comes into play. I don't understand the tag obviously and most of the examples I see show it in a WHERE clause. I know the data types for all my fields but I just can't figure out how to write it. Hints??