Solved

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

Posted on 2011-03-07
1
443 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now