Error Executing Database Query!

Hi.
I am new to coldfusion and would appreciate some help with this query.

After filling out the new purchases form i receive the following msg....

The error occurred in /home/gs4492/web/eca/new_purchase.cfm: line 44

42 : (NetValue,TotalVAT,PurchaseDate,PlannedDelivery,PaymentDue)
43 : VALUES
44 : (#UnitCost#,#NewVATTotal#,#NOW()#,#PlannedDeliveryDate#,#NewPaymentDue#);
45 : </cfquery>
46 :

Looking at the stored values in the summary i notice that the values are all of the correct type/format. Therefore i am stuggling to understand why there is an error.

The format of my variables should be...
NetValue N-Decimal(13,2)
TotalVat N-Decimal(13,2)
PurchasesDate T-Date & Time
PlannedDelivery T-Date & Time
PaymentDue T-Date & Time

The values stored are....
INSERT INTO PurchaseOrders (NetValue,TotalVAT,PurchaseDate,PlannedDelivery,PaymentDue) VALUES (10.2, 2.,{ts '2007-12-15 03:33:32'},{ts '2007-12-27 03:33:32'},{ts '1900-02-03 00:00:00'});

Although the value stored in the PaymentDue variable is not the correct value, it is still the correct datatype therefore it should work!

Your help would be greatly appreciated.
----------------------------------------------------------------------------------
The complete code looks like this....

<cfset appDSN = "tt380eca">

<!--- Test for primary Key to ensure user is navigating through the form. I the primary key is not defined then redirect user to form --->
<CFIF NOT IsDefined ("FORM.SupplierCode")>
<CFLOCATION URL="admin_index.cfm">
</CFIF>

<cfquery name="qryGetSuppliers" datasource="#appDSN#">

<!--- Insert the form entries for the new purchase transaction --->
INSERT INTO PurchaseLines
(Quantity,UnitCost,VATRate,Description)
VALUES
(#FORM.Quantity#,#FORM.UnitCost#,#FORM.VATRate#,
'#FORM.Description#');
<!--- The single quotes are used for the desciption variable as the entry is a character string --->

<!--Add PlannedDelivery entered as an integer and add this value, representing calender days, to the current date (purchase date).-->
<cfset PlannedDeliveryDate = #dateadd("D", + FORM.PlannedDelivery, NOW())#>
<!-- insert into database as datetime format-->
INSERT INTO PurchaseOrders
(PlannedDelivery)
VALUES
(#PlannedDeliveryDate#);

<!--- Calculate the amount of VAT payable by dividing the UnitCost by 100 and then multiplying by the VATRate. The resulting value will be stored in VATTotal --->
<cfset VATTotal = (UnitCost/100)*VATRate>
<!-- Format VATTotal to specified format. i.e. 13,2-->
<cfset TotalVAT = #LSNumberFormat(VATTotal, "-------------.--")#>


SELECT PaymentTerms,PlannedDelivery
FROM Suppliers,PurchaseOrders;

<cfset PaymentTerms = "23"><!---This is a temporary variable. Please remove later-->

<!--Calculate PaymentDue by adding the numeric (integer) value of PaymentTerms to the date stored in PlannedDelivery --->
<cfset NewPaymentDue = #dateadd("D", + PaymentTerms, PlannedDelivery)#>


<!-- Insert resulting values into PurchaseOders table --->
INSERT INTO PurchaseOrders
(NetValue,TotalVAT,PurchaseDate,PlannedDelivery,PaymentDue)
VALUES
(#UnitCost#,#TotalVAT#,#NOW()#,#PlannedDeliveryDate#,#NewPaymentDue#);
</cfquery>

 

Hi glens1234,

The line numbers in the error messages aren't always exact. What is the actual error description? It should have displayed right above " The error occurred in /home/gs4492/web/eca/new_purchase.cfm: line 44"

 

Since my last post i have noticed two semi-colons which i have missed out.
Although still get the same error.

Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<!--Add PlannedDelivery entered as an integer and add this value, representing c' at line 9

There doesnt appear to be anything i can deduct from this.

 

You can't run multiple statements in cfquery against mysql by default. Have you actually enabled this setting in your datasource?

http://www.sumoc.com/blog/index.cfm?mode=entry&entry=7F6F7314-5004-2066-B7A8BDFB26843712

I strongly recommend you use cfqueryparam, especially if you're going to enable multiple queries. Its dangerous to use form values directly in a query. A sql injection attack could drop your tables, database, etc.
http://livedocs.adobe.com/coldfusion/7/htmldocs/00000317.htm

 

eek!
i dont know to be honest. Im using the datasource provided by the open university.

So can i overcome this by simply enclosing my statements in separate cfquery tags?

 

Two other things.

You've got some HTML comments mixed in with CF comment tags. There is a difference and that may cause errors. The CF comments won't appear in the sql sent to the db, whereas html comments might.

HTML comments have two dashes
<!-- two dashes -->

CF comments have three dashes
<!--- three dashes --->

You don't need # signs there and I would recommend putting all your cfset statements before the <cfquery> tags, not inside them. Though these things should not cause an error.

 

Yes.

I'm entering this line because apparently the word "yes" is too short for a message reply on this forum ;-)

 

cool! ill give that a try.
also... i was wandering about the wheather it mattered how many dashes were used in the comments. So thanks for clearing that up.

 

worked a treat! thanks for your help! hopefully ill be able to get the rest of my forms working now

 

Best of luck !

 

Normally it doesn't matter.. much. Unless you care about people seeing your code comments when they click "view source" in their browser. The only reason it may matter here is because the comments are in the middle of a sql query. If those comments are sent to the db, it will probably cause an error.