Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-07
1
Medium Priority
?
450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

721 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