Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error with inserting a date and a time value into MySQL with Coldfusion

Posted on 2011-03-07
1
Medium Priority
?
453 Views
Last Modified: 2012-05-11
I have a form where the user picks a time and a date. When trying to insert, I keep getting an error, and I can't figure out what the problem is.

Insert syntax is:
<cfquery datasource="residentsminot" name="insertdata">
INSERT INTO emarsetup (ID, medication, dosage, when, datestart, monthly, notes)
values (#Who#, '#Med#', '#Dose#', #CreateODBCTime(TheTime)#, #CreateODBCDate(Start)#, #EOD#, '#Notes#')
</cfquery>

Where Who and EOD are integers and the others are text.
The database items are: ID is int, medication is varchar, dosage is varchar, when is TIME, datestart is DATE, monthly is tinyint, notes is longtext.  There are other fields in the table, but they are set up to take null values, or have a default value.

The error is:

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 'when, datestart, monthly, notes)
 values (89, 'pppppppp', 'none', '18:00:00', '2' at line 1
 
The error occurred in D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 703
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 700
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 508
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 36
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 1
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 703
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 700
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 508
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 36
Called from D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm: line 1
701 : <cfquery datasource="residentsminot" name="insertdata">
702 : INSERT INTO emarsetup (ID, medication, dosage, when, datestart, monthly, notes)
703 : values (#Who#, '#Med#', '#Dose#', #CreateODBCTime(TheTime)#, #CreateODBCDate(Start)#, #EOD#, '#Notes#')
704 : </cfquery>
705 : <cfif isdefined("Form.addtoday#Pcount#")>
VENDORERRORCODE        1064
SQLSTATE        42000
SQL         INSERT INTO emarsetup (ID, medication, dosage, when, datestart, monthly, notes) values (89, 'pppppppp', 'none', {t '18:00:00'}, {d '2010-11-15'}, 1, 'No notes.')
DATASOURCE        residentsminot
Resources:
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 5.1; en-US) AppleWebKit/534.13 (KHTML, like Gecko) Chrome/9.0.597.107 Safari/534.13
Remote Address        216.221.114.114
Referrer        http://www.visionmanagementservices.net/Minot/addmedication2.cfm
Date/Time        07-Mar-11 11:56 AM
Stack Trace
at cfaddmedication32ecfm1237771749._factor29(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:703) at cfaddmedication32ecfm1237771749._factor34(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:700) at cfaddmedication32ecfm1237771749._factor37(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:508) at cfaddmedication32ecfm1237771749._factor38(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:36) at cfaddmedication32ecfm1237771749.runPage(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:1)
 at cfaddmedication32ecfm1237771749._factor29(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:703) at cfaddmedication32ecfm1237771749._factor34(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:700) at cfaddmedication32ecfm1237771749._factor37(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:508) at cfaddmedication32ecfm1237771749._factor38(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:36) at cfaddmedication32ecfm1237771749.runPage(D:/home/visionmanagementservices.net/wwwroot/Minot/addmedication3.cfm:1)

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'when, datestart, monthly, notes)
values (89, 'pppppppp', 'none', '18:00:00', '2' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
      at com.mysql.jdbc.Util.getInstance(Util.java:381)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
      at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:741)
      at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:838)
      at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:348)
      at coldfusion.sql.Executive.executeQuery(Executive.java:1288)
      at coldfusion.sql.Executive.executeQuery(Executive.java:1051)
      at coldfusion.sql.Executive.executeQuery(Executive.java:982)
      at coldfusion.sql.SqlImpl.execute(SqlImpl.java:341)
      at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:843)
      at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:533)
      at cfaddmedication32ecfm1237771749._factor29(D:\home\visionmanagementservices.net\wwwroot\Minot\addmedication3.cfm:703)
      at cfaddmedication32ecfm1237771749._factor34(D:\home\visionmanagementservices.net\wwwroot\Minot\addmedication3.cfm:700)
      at cfaddmedication32ecfm1237771749._factor37(D:\home\visionmanagementservices.net\wwwroot\Minot\addmedication3.cfm:508)
      at cfaddmedication32ecfm1237771749._factor38(D:\home\visionmanagementservices.net\wwwroot\Minot\addmedication3.cfm:36)
      at cfaddmedication32ecfm1237771749.runPage(D:\home\visionmanagementservices.net\wwwroot\Minot\addmedication3.cfm:1)
      at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)
      at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416)
      at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
      at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:363)
      at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
      at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)
      at coldfusion.filter.PathFilter.invoke(PathFilter.java:87)
      at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)
      at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
      at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
      at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)
      at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
      at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
      at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:53)
      at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:126)
      at coldfusion.CfmServlet.service(CfmServlet.java:200)
      at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)
      at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)
      at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42)
      at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)
      at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
      at com.seefusion.Filter.doFilter(Filter.java:49)
      at com.seefusion.SeeFusion.doFilter(SeeFusion.java:1500)
      at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)
      at jrun.servlet.FilterChain.service(FilterChain.java:101)
      at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
      at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
      at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)
      at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)
      at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203)
      at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)
      at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)
      at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)
      at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)
0
Comment
Question by:spectrumcare
1 Comment
 

Accepted Solution

by:
spectrumcare earned 0 total points
ID: 35059596
Figured it out. When is a reserved word. Ooops!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question