Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!

[Guest]

I have two tables. The first is called Orders and contains a autonumber field named ID. This field is linked to a field named OrderID in another table named ProductOrders. I am using a submission form with a cfinclude to submit to both tables. There should only be one Orders.ID and numerous ProductOrders.OrderID (but they should be the same number). When I submit the form there are no error messages but the ProductOrders.OrderID is blank and therefore not linked to Orders.ID. What should I be looking for? I have linked the fields in Access in properties and relationships. What else?

[-__cfSearching__-]

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.

[Guest]

<cftransaction>

<cfinsert datasource="manna_premier" formfields="SaleDate,TerritoryManager,Distributor,DealerID,PONumber,Variable,ISOCheck,ISOCheckNumber,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?

[-__cfSearching__-]

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.

[Guest]

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!

[-__cfSearching__-]

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

http://en.wikipedia.org/wiki/Sql_injection

[Guest]

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!

[-__cfSearching__-]

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#">

[Guest]

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.

[-__cfSearching__-]

Insert 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

http://cfquickdocs.com/

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.

[-__cfSearching__-]

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 😉

[Guest]

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?

[-__cfSearching__-]

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.

[Guest]

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

[-__cfSearching__-]

You can use single quotes, but like I said that is a bad query style IMO.

Do a quick goole on:  ColdFusion cfqueryparam INSERT.  There are tons of examples out there.  I found one in seconds. Try and adapt it and post back if you have problems.

http://livedocs.adobe.com/coldfusion/7/htmldocs/00001371.htm

[Guest]

Okay...this is what I came up with:

  <cfquery datasource="manna_premier" name="InsertData">         Insert into ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments)         values (#OrderID#,#Product#,#Quantity#,#BagsCases#,#NewExisting#,#OrderComments#)   WHERE (BagsCases = <cfqueryPARAM value = "#BagsCases#" CFSQLType = "CF_SQL_VARCHAR">) AND (NewExisting = <cfqueryPARAM value = "#NewExisting#" CFSQLType = "CF_SQL_VARCHAR">)   AND (OrderComments = <cfqueryPARAM value = "#OrderComments#" CFSQLType = "CF_SQL_VARCHAR">)     </cfquery>

When I run the query the error message says " Missing semicolon ( at end of SQL statement. ". I tried to place one at the end of the where statement and I get the same error message. If I place it after the INSERT statement it says there are characters detected after the SQL. What am I doing wrong here?

[-__cfSearching__-]

Can you post the code again? The forum garbled it.

[Guest]

  <cfquery datasource="manna_premier" name="InsertData">
        Insert into ProductOrders (OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments)
        values (#OrderID#,#Product#,#Quantity#,#BagsCases#,#NewExisting#,#OrderComments#)
  WHERE (BagsCases = <cfqueryPARAM value = "#BagsCases#" CFSQLType = "CF_SQL_VARCHAR">) AND (NewExisting = <cfqueryPARAM value = "#NewExisting#" CFSQLType = "CF_SQL_VARCHAR">)
  AND (OrderComments = <cfqueryPARAM value = "#OrderComments#" CFSQLType = "CF_SQL_VARCHAR">);
    </cfquery>

[-__cfSearching__-]

I think you overlooked the INSERT example in the link I posted.  The INSERT syntax remains exactly the same. You just wrap the #variables# in cfqueryparam tags.  In other words, the SQL for insert statements does not change, just how you supply the values via CF.

ie Instead of  writing

    VALUES (  #OrderComments#       , .....other variables  )

You use ...

    VALUES (  <cfqueryparam value = "#OrderComments#" CFSQLType = "CF_SQL_VARCHAR">    ,.....other variables   )

[Guest]

Here is my revised code:

FYI Data types are as follows

    OrderID = Number

    Product = Number

    Quantity = Number

    BagsCases = Text

    NewExisting = Text

    OrderComments = Text

<cfloop index="Add" from="1" to="#form.howmany#" step="1">
    <cfset ordercount = ordercount + 1>
    <cfset OrderID = qGetID.Identity>
    <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 (<cfqueryPARAM value = "#OrderID#" CFSQLType = "CF_SQL_INTEGER">,
          <cfqueryPARAM value = "#Product#" CFSQLType = "CF_SQL_INTEGER">,
    <cfqueryPARAM value = "#Quantity#" CFSQLType = "CF_SQL_INTEGER">,
    <cfqueryPARAM value = '#BagsCases#' CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryPARAM value = '#NewExisting#' CFSQLType = "CF_SQL_VARCHAR">,
    <cfqueryPARAM value = '#OrderComments#' CFSQLType = "CF_SQL_VARCHAR">)
</cfquery>
</cfloop>

When I run it I get this error:

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.

[-__cfSearching__-]

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.

One of the things cfqueryparam does is verifies that the "value" supplied is valid for the given "cfsqltype".  In this case, the error is telling you that one of the values contains empty string "", and that is obviously not a valid integer/number. So an error occurs.

Depending on whether your column allows NULLs, you have a few choices:

1)  Convert empty strings to a valid number like 0.  The VAL() function is good for that.

2)  Insert NULL instead whenver the form field value is not numeric

[Guest]

That raises a question. My input form allows for a maximum of 20 entries. There is no minimum. If this, or any other column stores a Null or a Zero will it store it as a record? I want the database to only store those rows that have real data.

[-__cfSearching__-]

If this, or any other column stores a Null or a Zero will it store it as a record?

Databases are not concerned with business rules.  If you create an INSERT statement, and the VALUES are valid (according to your table definition), then of course the information will be stored.  Whether or not that information is considered "valid" is totally separate issue.

Only you can determine what is "real data" in terms of your application. If certain conditions should not be allowed, like users must enter a number for FIELD X instead of an empty string, you must create code that enforces those rules.  The only thing your database will do is verify the values are valid for the given column types and do not validate any existing database constraints, like the  "OrderID" value must be a valid ID from the Orders table.

Proper validation is important. Some of it (like constraints) can be done at a database level.  But applications also need well thought out pre-entry validation, or you can end up with a table of garbage data. It is not something you are going to pick up in a single forum post. You need to give it some thought.  Every application is different and thus may have different needs.

[-__cfSearching__-]

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.

[Adam Cameron.]

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

[Nonamez]

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 http://www.karanlikdunya.net OrderID number? Any help on this would be greatly appreciated.