Hi Everyone I'm quite new to coldfusion and this may seem like a very basic query but I'm needing a little bit of help.. Basically I need to set a Variable (shopID) dependant on the users input (addressPostCode) it needs to compare the first four characters in the form input with the existing data in my shops table, select the shopID that matches and then input it into another table (customers) and if the input does not match any the shop list table i want it to redirect to another page... Probably very simple for you guys out there but as I said - am very new to coldfusion the code I have so far is... <CFTRANSACTION> <CFQUERY NAME="selectshop" DATASOURCE="981516028"> SELECT * FROM shops </cfquery> <CFQUERY NAME="setshop" DATASOURCE="981516028"> <cfoutput query="selectshop"> <Cfif left(form.addressPostCode,4) EQ left(addressPostCode,4)> <cfset shopID = shopID> </cfif> </cfoutput> <cfif NOT isdefined('shopID')> <cflocation url = "http://www.google.com"> </cfif> </CFQUERY> <CFQUERY NAME="insertdetails" DATASOURCE="database"> INSERT INTO orders (customerID,orderID,shopID,[date],ordervalue) VALUES ('#Form.customerID#','#Form.orderID#','#shopID#','#Form.date#',#Form.ordervalue#) </CFQUERY> Code (markup): The rest of the code works fine - if I use <cfset shopID=1> it will happily continue through the rest of the details but always set the shopID to 1... Oh this is NOT for a commercial use site... Hoping anyone can help ... I feel like I'm very much stabbing in the dark and I'm guessing I'm totally wrong on this ... Error is ..... Error Executing Database Query. [DataDirect][SequeLink JDBC Driver]Syntax error at token 0, line 0 offset 0. The error occurred in F:\users\981516028\httpdocs\subgrub\pages\ordering\input.cfm: line 39 37 : 38 : <cfif NOT isdefined('shopID')> 39 : <cflocation url = "http://www.google.com"> 40 : </cfif> 41 : </cfoutput>
OK I may have progressed - been using the good old trial and error method.. Code is now <CFQUERY NAME="selectshop" DATASOURCE="981516028"> SELECT * FROM shops <CFoutput query="selectshop"> <Cfif left(form.addressPostCode,4) EQ left(addressPostCode,4)> <cfset shopID = shopID> </cfif> </CFoutput> <CFif NOT isdefined('shopID')> <cflocation url = "http://www.google.com"> </cfIF> </CFQUERY> Code (markup): And the page goes to google if the details don't match... However the page goes to another error page .. which does show that the shopID is now set... but opens another can of worms I think code first <CFTRANSACTION> <CFQUERY NAME="selectshop" DATASOURCE="981516028"> SELECT * FROM shops <CFoutput query="selectshop"> <Cfif left(form.addressPostCode,4) EQ left(addressPostCode,4)> <cfset shopID = shopID> </cfif> </CFoutput> <CFif NOT isdefined('shopID')> <cflocation url = "http://www.google.com"> </cfIF> </CFQUERY> <CFQUERY NAME="insertcustomers" DATASOURCE="981516028"> INSERT INTO customers (customerID,firstname,lastname,addressNumber,addressStreet,addressArea,addressTown,addressPostCode,phone,email) VALUES ('#Form.customerID#','#Form.firstname#','#Form.lastname#','#Form.addressNumber#','#Form.addressStreet#','#Form.addressArea#','#Form.addressTown#','#Form.addressPostCode#','#Form.phone#','#Form.email#') </CFQUERY> <CFQUERY NAME="insertorders" DATASOURCE="981516028"> INSERT INTO orders (customerID,orderID,shopID,[date],ordervalue) VALUES ('#Form.customerID#','#Form.orderID#','#shopID#','#Form.date#',#Form.ordervalue#) </CFQUERY> <CFQUERY NAME="insertorderdetails" DATASOURCE="981516028"> INSERT INTO orderdetails (orderID, sandwichID, snackID, drinkID) VALUES (#Form.orderID#,999999,999999,999999) </CFQUERY> </CFTRANSACTION> Code (markup): And the error message... Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'customers'. The error occurred in F:\users\981516028\httpdocs\subgrub\pages\ordering\input.cfm: line 46 44 : <CFQUERY NAME="insertdetails" DATASOURCE="981516028"> 45 : INSERT INTO orders (customerID,orderID,shopID,[date],ordervalue) 46 : VALUES ('#Form.customerID#','#Form.orderID#','#shopID#','#Form.date#',#Form.ordervalue#) 47 : </CFQUERY> 48 : SQLSTATE 23000 SQL INSERT INTO orders (customerID,orderID,shopID,[date],ordervalue) VALUES ('719615','897410','1','06 12 2009',0) VENDORERRORCODE -1613 DATASOURCE 981516028 Code (markup):
Sussed it... Needed to insert the customers details before the orders details I think I've solved this one through trial and error - I'll have to check the database to be sure... but if so - I'll probably be back soon with more questions!