SQL problems using a form page 2

bizbul · Dec 13, 2000

Let me say I am a beginner ColdFusion programmer. I'm having trouble with a form & actionpage due to SQL statements. I've canvassed them a 1,00 times and can't see the error of my ways. Could someone help? Here is the current problem.

Form:

<!HwebPg3.cfm -- calls actionpage3.cfm>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<cfinsert datasource="Bizbul" TABLENAME="TRANSACTION">
<html>
<head>
<title>Hashemi Project Page 3</title>
<SCRIPT LANGUAGE="JavaScript">
document.write(Date());
</SCRIPT>

</head>

<body BGCOLOR="#BFA0D3" TEXT=Black>
<form action="actionpage3a.cfm" method="post">

<STRONG><center>DR. HASHEMI'S DATABASE: INSERT TRANSACTIONS</center></strong>
<br><br><br>
<table width=100% border=0 cellspacing=15 cellpadding=5>
<tr>1. The transaction number is: <INPUT TYPE="text" NAME="T_id" SIZE="8" MAXLENGTH="8"><br><br>
</tr>
<tr>2. The transaction type is

return or purchase) <INPUT TYPE="text" NAME="trans_type" SIZE="8" MAXLENGTH="8"><br><br>
</tr>
<tr>3. The customer's ID# is: <INPUT TYPE="text" NAME="id" SIZE="8" MAXLENGTH="8"><br><br>
</tr>
<tr>4. The item # is: <INPUT TYPE="text" NAME="item_num" SIZE="8" MAXLENGTH="8"><br><br>
</tr>
<tr>5. The model number is: <INPUT TYPE="text" NAME="model_num" SIZE="10" MAXLENGTH="10"><br><br>
</tr>
<tr>6. The date is: <INPUT TYPE="text" NAME="date" SIZE=""15 MAXLENGTH="15"><br><br>
</tr>
<tr>7. The quantity is: <INPUT TYPE="text" NAME="qty" SIZE="8" MAXLENGTH="8"><br><br>
</tr>
<tr>8. The price is: <INPUT TYPE="text" NAME="price" SIZE="15" MAXLENGTH="15"><br><br>
</tr>
<tr>9. The total cost is: <INPUT TYPE="text" NAME="total_cost" SIZE="15" MAXLENGTH="15"><br>
</tr>
<tr>
<th><IMG LENGTH=100% SRC="lines_pp_022.gif">
</th>
</tr>
<tr>
<th align="center"><INPUT TYPE="submit" NAME="SubmitForm" VALUE="Submit"> <INPUT TYPE="reset" NAME="ResetForm" VALUE="Clear Form">
</th>
</tr>
</TABLE>
</form>

</body>
</html>
-----------------------------------------------------------
ACTION PAGE:

<html>
<head>
<title>Updated Database</title>
</header>

<body BGCOLOR="#ccccff">
<CFQUERY NAME="AddTrans" DATASOURCE="bizbul">
INSERT INTO TRANSACTION ('#Form.T_id#', '#Form.trans_type#', '#Form.id#',
'#Form.item_num#', '#Form.model_num#', '#Form.date#', '#Form.qty#', '#Form.price#', '#Form.total_cost#')
</CFQUERY>

<h1>Transaction Added</h1><br><br>

<b>You have added the transaction/s:</b><br>
<cfoutput query="AddTrans">
<table BORDER="5" CELLPADDING="5" CELLSPACING="5">
<tr>
<td>Transaction ID Num</td>
<td>Transaction type</td>
<td>ID Num</td>
<td>Item Num</td>
<td>Model Num</td>
<td>Date</td>
<td>Quantity</td>
<td>Price</td>
<td>Total Cost</td>
</tr>
<tr>
<td>#T_id#</td>
<td>#trans_type#</td>
<td>#id#</td>
<td>#item_num#</td>
<td>#model_num#</td>
<td>#date#</td>
<td>#qty#</td>
<td>#price#</td>
<td>#total_cost#</td>
</tr>
</table>
</cfoutput><br><br>

<CFQUERY NAME="ItemUpdate" DATASOURCE="bizbul">
<CFIF Form.trans_type IS "return">
UPDATE Item
SET UnitsInStock = UnitsInStock + #Form.qty#
WHERE item_no = '#Form.item_num#'
<CFELSEIF Form.trans_type IS "purchase">
UPDATE ITEM
SET UnitsInStock = UnitsInStock - #Form.qty#
WHERE item_no = '#Form.item_num#'
</CFIF>
</CFQUERY>

<CFQUERY NAME="CustUpdate" DATASOURCE="bizbul">
<CFIF Form.trans_type IS "return">
UPDATE CUSTOMER
SET balance = balance + #Form.total_cost#
WHERE id='#Form.id#'
<CFELSEIF Form.trans_type IS "purchase">
UPDATE CUSTOMER
SET balance = balance - #Form.total_cost#
WHERE id = '#Form.id#'
</CFIF>
</CFQUERY>

<CFQUERY NAME="PrintOut" DATASOURCE="bizbul">
SELECT *
FROM Item
WHERE item_no = '#Form.item_num#'
</CFQUERY>

<CFQUERY NAME="CustomerOut" DATASOURCE="bizbul">
SELECT id, name, city, state, zip, balance
FROM Customer
WHERE id = '#Form.id#'
</cfquery>

<b>You have also updated the inventory:</b>
<cfoutput query="PrintOut">
<table BORDER="5" CELLPADDING="5" CELLSPACING="5">
<tr>
<td>Item Num</td>
<td>Model Num</td>
<td>Units In Stock</td>
<td>Unit Price</td>
</tr>
<tr>
<td>#item_no#</td>
<td>#model_no#</td>
<td>#UnitsInStock#</td>
<td>#UnitPrice#</td>
</tr>
</table>
</cfoutput><br><br>
<b>You have also updated the customer's balance:</b>
<cfoutput query="CustomerOut">
<table BORDER="5" CELLPADDING="5" CELLSPACING="5">
<tr>
<td>Id</td>
<td>Name</td>
<td>City</td>
<td>State</td>
<td>Zip</td>
<td>Balance</td>
</tr>
<tr>
<td>#id#</td>
<td>#name#</td>
<td>#city#</td>
<td>#state#</td>
<td>#zip#</td>
<td>#balance#</td>
</tr>
</table>
</cfoutput><br><br>

</body>
</html>
------------------------------------------------------------
I really really appreciate your help and time
Jennifer

onirike · Dec 13, 2000

Hi Jennifer!

And what's the error you are getting? Or what's exactly the problem?

Thx,
Chris ;-)

bizbul · Dec 13, 2000

I'm getting this:
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (8:1) to (8:51).

onirike · Dec 13, 2000

Hi Jennifer!

That's not helping much but you are getting a query error. You should check your queries to verify if they are all ok.

This query however sound strange:
<CFQUERY NAME="AddTrans" DATASOURCE="bizbul">
INSERT INTO TRANSACTION ('#Form.T_id#', '#Form.trans_type#', '#Form.id#',
'#Form.item_num#', '#Form.model_num#', '#Form.date#', '#Form.qty#', '#Form.price#', '#Form.total_cost#')
</CFQUERY>

You sure all there field are text? If they are number field you must remove the ''.
Would give something like this:
<CFQUERY NAME="AddTrans" DATASOURCE="bizbul">
INSERT INTO TRANSACTION (#Form.T_id#, #Form.trans_type#, #Form.id#,
#Form.item_num#, #Form.model_num#, '#Form.date#', #Form.qty#, #Form.price#, #Form.total_cost#)
</CFQUERY>

Check every field and be sure it's a text field before putting quotes. Numbers and date field values should not have quotes around them.

And if you are really using text field for all these values then I'm not really sure what the problem could be...

Trying to help,
Chris ;-)

DarkMan · Dec 13, 2000

In your insert statement:

Code:

<CFQUERY NAME="AddTrans" DATASOURCE="bizbul"> INSERT INTO TRANSACTION ('#Form.T_id#', '#Form.trans_type#', '#Form.id#', '#Form.item_num#', '#Form.model_num#', '#Form.date#', '#Form.qty#', '#Form.price#', '#Form.total_cost#') </CFQUERY>

you're not calling the columns you're inserting to...
Also, you have single quotes around all of your values.. Anything that is a numerical value should NOT be quoted...
Try something like:

Code:

<CFQUERY NAME="AddTrans" DATASOURCE="bizbul"> INSERT INTO TRANSACTION (T_ID, Trans_Type, ID, Item_Num, Model_Num, Date, Qty, Price, Total_Cost) VALUES (#Form.T_id#, '#Form.trans_type#', #Form.id#, #Form.item_num#, #Form.model_num#, '#Form.date#', #Form.qty#, #Form.price#, #Form.total_cost#) </CFQUERY>

of course, I'm just guessing as to what your column names are and whether they are defined as numerical or not...

Hope this helps. DarkMan

bizbul · Dec 13, 2000

Yes Chris, I am using all text fields (probably not the best way to go) but I was trying to simplify things while
I learn about this. Darkman, I tried that as well to no avail. I'm just not sure what to do next. Any thoughts are profoundly appreciated.

Jennifer s-)

bizbul · Dec 13, 2000

I've also tried the <cfinsert datasource="Bizbul" TABLENAME="TRANSACTION">. This might have worked except my submit button was seen as an attribute and it gave me an error. Any way around that??

Jennifer s-)

onirike · Dec 13, 2000

Hi!

First let's say to darkman that you dont need to specify the fields name when you are inserting in every fields of a table. I dont know if it's true for every type of db but I dont have to with Oracle.

Ok try this:
<CFQUERY NAME="AddTrans" DATASOURCE="bizbul">
INSERT INTO TRANSACTION VALUES('#Form.T_id#', '#Form.trans_type#', '#Form.id#',
'#Form.item_num#', '#Form.model_num#', '#Form.date#', '#Form.qty#', '#Form.price#', '#Form.total_cost#')
</CFQUERY>

Should be better.
And by the way this wont work either:

<cfoutput query="AddTrans">
<table BORDER="5" CELLPADDING="5" CELLSPACING="5">
<tr>
<td>Transaction ID Num</td>
<td>Transaction type</td>
<td>ID Num</td>
<td>Item Num</td>
<td>Model Num</td>
<td>Date</td>
<td>Quantity</td>
<td>Price</td>
<td>Total Cost</td>
</tr>
<tr>
<td>#T_id#</td>
<td>#trans_type#</td>
<td>#id#</td>
<td>#item_num#</td>
<td>#model_num#</td>
<td>#date#</td>
<td>#qty#</td>
<td>#price#</td>
<td>#total_cost#</td>
</tr>
</table>
</cfoutput><br><br>

you cant loop on an INSERT query. Use your submitted form field to display the desired info in your table:

<cfoutput>
<table BORDER="5" CELLPADDING="5" CELLSPACING="5">
<tr>
<td>Transaction ID Num</td>
<td>Transaction type</td>
<td>ID Num</td>
<td>Item Num</td>
<td>Model Num</td>
<td>Date</td>
<td>Quantity</td>
<td>Price</td>
<td>Total Cost</td>
</tr>
<tr>
<td>#Form.T_id#</td>
<td>#Form.trans_type#</td>
<td>#Form.id#</td>
<td>#Form.item_num#</td>
<td>#Form.model_num#</td>
<td>#Form.date#</td>
<td>#Form.qty#</td>
<td>#Form.price#</td>
<td>#Form.total_cost#</td>
</tr>
</table>
</cfoutput><br><br>

I hope it helps,
Chris ;-)

bizbul · Dec 13, 2000

YES, YES, YES, YES....THANK YOU SOOOOO MUCH!!!
I've been driving myself and everyone I know crazy, I can't tell you how much this means to me. You've all been the best!!!

Jennifer *:->*

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL problems using a form page 2

bizbul

Programmer

onirike

Programmer

bizbul

Programmer

onirike

Programmer

DarkMan

Programmer

bizbul

Programmer

bizbul

Programmer

onirike

Programmer

bizbul

Programmer

Similar threads

Part and Inventory Search

Sponsor