here it is, I have no idea why it wont recognice the value passed from the form in the previous page: UPDATE Suppliers SET <cfif IsDefined (form.newsuppname)> SupplierName = '#form.newsuppname#', </cfif> <cfif IsDefined ('form.newsuppaddress1')> Address1 = #newhousenumb# </cfif> <cfif IsDefined ('form.newsuppaddress2')> Address2 = '#form.newsuppaddress2#', </cfif> <cfif IsDefined('form.newsuppcity')> City = '#form.newsuppcity#', </cfif> <cfif IsDefined('form.newpostcode')> PostCode = '#form.newpostcode#', </cfif> <cfif IsDefined('form.newsalesrepname')> SalesRepName = '#form.newsalesrepname#', </cfif> <cfif IsDefined('form.newsalesrepphone')> SalesRepPhone = '#form.newsalesrepphone#', </cfif> <cfif IsDefined('form.newsalesrepemail')> SalesRepEmail = '#form.newsalesrepemail#', </cfif> <cfif IsDefined(form.newterm)> PaymentTerms = #form.newterm#, </cfif> <cfif IsDefined('form.newnotes')> Notes = '#form.newnotes#' </cfif> WHERE SupplierCode = '#form.suppcode#'<!--- This is the bit it WONT recognise it says there is an error ---> </cfquery> Code (markup): they bit that does not work is the 'WHERE' statement at the end If any one can help I would be gratefull (PS I tried it with and without single quotes (WHERE SupplierCode =#form.suppcode#) too) Many thanks! Jimmy
What is the exact error you are getting? Are you sure it is form. and not url.? Depends on if the form is passing it or the url is passing it.
This is the error code i get back (BTW the server is running Coldfusion 6 if this makes a difference...) Error Executing Database Query. 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 'Ambleside Park - The dark End (wooo) ' at line 6 The error occurred in /home/jc22454/web/testing/suppliers/update_data.cfm: line 51 49 : </cfif> 50 : 51 : WHERE SupplierCode = <cfoutput>#formsuppliercode#</cfoutput> 52 : </cfquery> 53 : <cfquery name="qNewSupplierData" datasource="tt380eca"> SQL UPDATE Suppliers SET Address1 = 55 Ambleside Park - The dark End (wooo) Address2 = 'Aylesbury', City = 'London', PostCode = 'MK14 7BA', SalesRepName = 'Ivor Biggun', SalesRepPhone = '01908200909', SalesRepEmail = 'ivor@idonthaveaclue.com', WHERE SupplierCode = C17596EB-4798-423C-86FA6131204DA6A9 DATASOURCE tt380eca VENDORERRORCODE 1064 SQLSTATE 42000 Please try the following: * Check the ColdFusion documentation to verify that you are using the correct syntax. * Search the Knowledge Base to find a solution to your problem. Browser Mozilla/5.0 (Windows; U; Windows NT 6.0; en-GB; rv:1.9.0.4) Gecko/2008102920 Firefox/3.0.4 (.NET CLR 3.5.30729) Remote Address 89.242.104.156 Referrer **********/**********/********* Date/Time 01-Dec-08 04:05 PM Stack Trace at cfupdate_data2ecfm270764234.runPage(/****/****/****/****/****/update_data.cfm:51) at cfupdate_data2ecfm270764234.runPage(/****/****/****/****/****/update_data.cfm:51) java.sql.SQLException: 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 'Ambleside Park - The dark End (wooo) ' at line 6 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695) at com.mysql.jdbc.Connection.execSQL(Connection.java:3020) at com.mysql.jdbc.Connection.execSQL(Connection.java:2949) at com.mysql.jdbc.Statement.execute(Statement.java:538) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:212) at coldfusion.sql.Executive.executeQuery(Executive.java:974) at coldfusion.sql.Executive.executeQuery(Executive.java:886) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:236) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:447) at cfupdate_data2ecfm270764234.runPage(/****/****/****/****/****/update_data.cfm:51) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:147) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:357) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:62) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:107) at coldfusion.filter.PathFilter.invoke(PathFilter.java:80) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:47) at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:52) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:35) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:43) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.CfmServlet.service(CfmServlet.java:105) at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91) at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42) at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:249) at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527) at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:192) at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348) at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451) at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294) at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66) Code (markup):
Address1 = #newhousenumb# should be Address1 = '#newhousenumb#' Also make sure you are using <cfqueryparam> and you wont have these issues ie Address1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#newhousenumb#"> Same with the SupplierCode. If SupplierCode is erroring make sure the column name is correct both in name and the data you are trying to insert matches the type in the database.
Paul_K already addressed the main problem, but a few other points you should consider: 1. <cfif IsDefined (form.newsuppname)> That syntax is incorrect and may cause an error if the field does not exist. It should be: <cfif IsDefined ("form.newsuppname")> 2. The code does not always produce a valid sql statement. For example, if form.newnotes is not defined, the generated sql would have trailing comma before the FROM clause and will generate an error: UPDATE Suppliers SET Address1 = '55 Ambleside Park - The dark End (wooo)', Address2 = 'Aylesbury', City = 'London', PostCode = 'MK14 7BA', SalesRepName = 'Ivor Biggun', SalesRepPhone = '01908200909', SalesRepEmail = 'ivor@idonthaveaclue.com', <<<---- EXTRA COMMA WHERE SupplierCode = 'C17596EB-4798-423C-86FA6131204DA6A9' Code (markup): 3. Is IsDefined really necessary? Unless your form fields are generated dynamically or they are checkboxes or radio buttons, they should always exist.
Thanks I will rty out each of these and see where I get, The reason I have used 'IsDefined' is because the form fields may not be updated at the same time for instance sales rep name and email may be updated but the rest may not. Also I have changed the final 'Notes' section to: <cfif IsDefined('form.newnotes')> Notes = '#form.newnotes#' <cfelse> Notes = 'No Notes Available' </cfif> Code (markup): To see if this works (and prevents the trailing comma error) Thanks Jimmy
AAAAAAAAAAAAAAAAAAARRRRRRRRRRRRRRRRRRRRRRRGGGGGGGGGGGGGGGGHHHHHHHHHHHHHHHH!!!!!!!!!!!!!!! |After all that it was a stupid stupid bloody missing comma! after the address1 query statement there was a missing comma which ruined it all! Thanks for the help people I fot it working now! jimmy
If they are text fields they will always exist. Unless they are added dynamically via javascript. You can check for empty values using len(trim(form.yourFieldName)) instead.
Thanks for the tip, I have it all set up now using 'IsDefined' I suppose its swings and roundabouts with them both and down to preference.
Yes, some things are purely a matter of preference. But this isn't one of them ;-) Earlier you mentioned: That implies sometimes the form fields are left empty. They may be empty, but because they are text fields they still exist. So the IsDefined test would always return true. So this code would always update the PostCode column. Even if #form.newpostcode# was left blank <cfif IsDefined('form.newpostcode')> PostCode = '#form.newpostcode#', </cfif> However, this code would only update the column if the form field was not left empty <cfif len(trim(form.fieldName))> PostCode = '#form.newpostcode#', </cfif> See the difference? That said, I don't know your form so use whichever one produces the correct results