Inserting data into tables with SQL

Right i have made a simple orderform whereby an individual will choose from a product from a drop-menu, they will also provide details about the quantity needed of the product ordered. When the order is submitted then the data will go into the necessary fields and an ordernumber automatically generated.

I need to insert data into two tables. Into one of the tables is a field called OrderNo, this is an autonumber field so i have been told that for the SQL INSERT command if i put 0 then this will be okay and the order will be assigned a number. The other field is this table is called RecipeCode which is passed through when the Pizza is chosen i.e. RecipeCode idtentifies the PizzaName.

For the 2nd table again there is the field OrderNo, this is the key that links upto the ORDERDETAIL table so will the value be automatically passed through? In addition, when the order is placed i need to create an OrderDate, I've been told that the command Now() will insert the current date to the required field, is this correct?
Finally I also need to obtain a price for the order that has been placed. Now I'm using the RecipeCode field that tells me the PizzaName, this will also give me the cost for a pizza but i'm not too sure how to calculate this figure...
could it be something like <variable> = PizzaQuantity * PizzaPrice

so far the commands that i have are as follows:
<CFQUERY NAME="InsertPizzaDetails" DATASOURCE="******">
INSERT INTO ORDERDETAIL (OrderNo, RecipeCode)
VALUES (0,form.PizzaName)
<!-- when passing data through use keywords such as form.PizzaName (i.e SELECT NAME option on order form, this will uniquely store only one pizza name value to process onto the form, same goes with other fields too -->
<CFQUERY NAME="InsertDatePrice">
INSERT INTO ORDERS (OrderNo, OrderDate,TotalOrder)
VALUES (0,Now())

How does coldfusion make use out of autonumbers? Also when using the INSERT INTO command how is it best to input todays date?
would it just be
INSERT INTO table_name
VALUES (?,Date(),?)

This seems to be a recurring theme lately, with people trying to figure out "what ColdFusion does with SQL", or "does CF support X in my SQL statement..."

ColdFusion does nothing with SQL except process any CFML functions or tags in the statement, format it, and forward it to a database. So, a SQL statement in CF will support whatever the database it is being forwarded to supports.

So, for an autonumber field, you don't even have to include it in the SQL statement at all, the database will automatically insert a unique value in that column at the time of the insert (that is what an autonumber column does, it is basically a trigger under the hood, but that's going off on a tangent).

The CFML Now() function will be replaced by a date/time value before the SQL is forwarded to the database. So in the end, your statement should be something like:

<CFQUERY NAME="InsertDatePrice">
INSERT INTO ORDERS (OrderDate,TotalOrder)
VALUES (#now()#,#form.totalOrder#)
</CFQUERY>

(Now might need quotes around it like '#now()#' depending on your database. Note that a huge part of your problem is that you are not qualifying the ColdFusion variables and functions with pound signs in your SQL statement.)

good job, kiteless, except i would recommend not using coldfusion's now() function, which, in the context of a query, will be translated into a string

this causes the database needless extra processing (okay, it's only a few micro-nano-pico-seconds, but the point still stands) to parse the string, validate the date, and convert it to internal datetime format

if, on the other hand, you use a builtin database function for this purpose, it will go much more slickly

thus,

<CFQUERY NAME="InsertDatePrice">
INSERT INTO ORDERS (OrderDate,TotalOrder)
VALUES (current_date,#form.totalOrder#)
</CFQUERY>

use current_date or sysdate or getdate(), or even -- as for access, what a coincidence, eh? -- now()

Spot on, r937. Always get the database to do as much work as possible!

thank you

but i think i was pointing out how to let the database do as little work as possible

heh...I think we're calling the same thing 2 different names. Every time I can offload a function call, some conditional logic, or number crunching from CF to the database, I try to do it. That's what I mean when I say make the database do as much work as possible.

oh, i understand that, and i totally agree

it's just that this was a different scenario

using ColdFusion's #now()# function makes the database actually do more work than if you let the database use its own current date function, so i was suggesting offloading the function to the database because that's actually less work for the database, not "make the database do as much work as possible"