Insert in Access DB

Posted on 2004-08-27
Last Modified: 2013-12-24
Dear Experts,

I can not figure out what I am doing wrong.

I have two pages  WateringNote.cfm & NewWaterAction. The first page WateringNote.cfm allows the user to select a zone and to insert the date. The second page NewWaterAction inserts the information from the form into my Access database.   Now if I remove the date field from the two pages it works. Here is my code for WateringNote.cfm

<cfif IsDefined("URL.MM_logout") AND URL.MM_logout EQ "1">
  <cflock scope="Session" type="Exclusive" timeout="30" throwontimeout="no">
    <cfset Session.MM_Username="">
    <cfset Session.MM_UserAuthorization="">
  <cfset MM_logoutRedirectPage="../Index.cfm">
  <cfif MM_logoutRedirectPage EQ "">
    <cfset MM_logoutRedirectPage=CGI.SCRIPT_NAME>
  <cfset MM_logoutQuery=ListDeleteAt(CGI.QUERY_STRING,ListContainsNoCase(CGI.QUERY_STRING,"MM_logout=","&"),"&")>
  <cfif MM_logoutQuery NEQ "">
    <cfif Find("?",MM_logoutRedirectPage) EQ 0>
      <cfset MM_logoutRedirectPage=MM_logoutRedirectPage & "?" & MM_logoutQuery>
      <cfset MM_logoutRedirectPage=MM_logoutRedirectPage & "&" & MM_logoutQuery>
  <cflocation url="#MM_logoutRedirectPage#" addtoken="no">
<cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">
  <cfset MM_Username=Iif(IsDefined("Session.MM_Username"),"Session.MM_Username",DE(""))>
  <cfset MM_UserAuthorization=Iif(IsDefined("Session.MM_UserAuthorization"),"Session.MM_UserAuthorization",DE(""))>
<cfif MM_Username EQ "">
  <cfset MM_referer=CGI.SCRIPT_NAME>
    <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>
  <cfset MM_failureURL="../Index.cfm?accessdenied=" & URLEncodedFormat(MM_referer)>
  <cflocation url="#MM_failureURL#" addtoken="no">
<cfparam name="URL.ID" default="1">
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfquery name="Recordset1" datasource="Garden">
FROM Plant
<cfset today =
      DateFormat(now(), "dddd, mmmm d, yyyy")>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<body link="#FFFFFF" vlink="#FFFFFF" alink="#FFFFFF">
<cfform name="form1" method="post" action="NewWaterAction.cfm">
<table width="75%" border="0" align="center" cellpadding="10" cellspacing="0">
    <td bgcolor="#545136"><font color="#FFFFFF" size="2"><strong><em>Welcome, <spacer> </em></strong></font><em><strong><font size="2"> <spacer> <font color="#FFFFFF"><cfoutput>#Session.MM_username#</cfoutput></font></font></strong></em><strong><spacer> </strong></td>
    <td width="50%" bgcolor="#545136"><div align="right"><font color="#FFFFFF" size="2"><strong><em><cfoutput>#today#</cfoutput></em></strong></font></div>
<table width="75%" border="0" align="center" cellpadding="1" cellspacing="0">
    <td bgcolor="#A8A377"><div align="right"><font color="#FFFFFF"><a href="<cfoutput>#CurrentPage#?MM_logout=1</cfoutput>"><img src="../Logout_sm.gif" width="93" height="37" border="0"></a></font></div></td>
<table width="75%" border="0" align="center" cellpadding="4" cellspacing="0">
    <td bgcolor="#545136"><div align="center"><font color="#FFFFFF" size="4"><strong><em>Watering
<table width="75%" border="0" align="center" cellpadding="2" cellspacing="1">
    <td bgcolor="#A8A377"><cfoutput></cfoutput></td>
    <td width="50%" bgcolor="#A8A377"><cfoutput>
      <div align="left"></div>
    <td bgcolor="#A8A377">&nbsp;</td>
    <td bgcolor="#A8A377"><cfoutput><font color="##FFFFFF"></font></cfoutput></td>
    <td bgcolor="#A8A377">&nbsp;</td>
    <td bgcolor="#A8A377"><cfoutput><font color="##FFFFFF"></font></cfoutput></td>
    <td bgcolor="#A8A377">&nbsp;</td>
    <td bgcolor="#A8A377"><cfoutput><font color="##FFFFFF"></font></cfoutput></td>
    <td bgcolor="#A8A377"><font color="#FFFFFF"><strong>Zone:</strong></font></td>
    <td bgcolor="#A8A377"><select name="ZoneList" id="ZoneList" value="">
      <option value="A1">A1</option>
      <option value="A2">A2</option>
      <option value="A3">A3</option>
      <option value="A4">A4</option>
      <option value="A5">A5</option>
      <option value="A6">A6</option>
      <option value="A7">A7</option>
      <option value="A8">A8</option>
      <option value="A9">A9</option>
      <option value="A10">A10</option>
      <option value="A11">A11</option>
      <option value="A12">A12</option>
      <option value="A13">A13</option>
      <option value="A14">A14</option>
      <option value="A15">A15</option>
      <option value="A16">A16</option>
      <option value="B1">B1</option>
      <option value="B2">B2</option>
      <option value="B3">B3</option>
      <option value="B4">B4</option>
      <option value="B5">B5</option>
      <option value="B6">B6</option>
      <option value="B7">B7</option>
      <option value="B8">B8</option>
      <option value="B9">B9</option>
      <option value="B10">B10</option>
      <option value="B11">B11</option>
      <option value="B12">B12</option>
      <option value="B13">B13</option>
      <option value="B14">B14</option>
      <option value="B15">B15</option>
      <option value="B16">B17</option>
      <option value="B17">B17</option>
      <option value="B18">B18</option>
      <option value="B19">B19</option>
      <option value="B20">B20</option>
      <option value="B21">B21</option>
      <option value="B22">B22</option>
      <option value="B23">B23</option>
      <option value="C1">C1</option>
      <option value="C2">C2</option>
      <option value="C3">C3</option>
      <option value="C4">C4</option>
      <option value="C5">C5</option>
      <option value="C6">C6</option>
      <option value="C7">C7</option>
      <option value="C8">C8</option>
      <option value="C9">C9</option>
      <option value="C10">C10</option>
      <option value="C11">C11</option>
      <option value="C12">C12</option>
      <option value="C13">C13</option>
      <option value="C14">C14</option>
      <option value="C15">C15</option>
      <option value="C16">C16</option>
      <option value="C17">C17</option>
      <option value="C18">C18</option>
      <option value="C19">C19</option>
      <option value="C20">C20</option>
      <option value="C21">C21</option>
      <option value="C22">C22</option>
      <option value="C23">C23</option>
      <option value="C24">C24</option>
      <option value="C25">C25</option>
      <option value="C26">C26</option>
      <option value="C27">C27</option>
      <option value="C28">C28</option>
      <option value="C29">C29</option>
      <option value="C30">C30</option>
      <option value="C31">C31</option>
      <option value="C32">C32</option>
      <option value="C33">C33</option>
      <option value="C34">C34</option>
      <option value="C35">C35</option>
      <option value="D1">D1</option>
      <option value="D2">D2</option>
      <option value="D3">D3</option>
      <option value="D4">D4</option>
      <option value="D5">D5</option>
      <option value="D6">D6</option>
      <option value="D7">D7</option>
      <option value="D8">D8</option>
      <option value="D9">D9</option>
      <option value="D10">D10</option>
      <option value="D11">D11</option>
      <option value="D12">D12</option>
      <option value="D13">D13</option>
      <option value="D14">D14</option>
      <option value="D15">D15</option>
      <option value="D16">D16</option>
      <option value="D17">D17</option>
      <option value="E1">E1</option>
      <option value="E2">E2</option>
      <option value="E3">E3</option>
      <option value="E4">E4</option>
      <option value="E5">E5</option>
      <option value="E6">E6</option>
      <option value="E7">E7</option>
      <option value="E8">E8</option>
    <td bgcolor="#A8A377"><font color="#FFFFFF"><strong>Date:</strong></font></td>
    <td bgcolor="#A8A377"><cfinput name="Date1" type="text" id="Date1" required="yes" message="Im sorry, but the Date is required">
      <font color="#FFFFFF">    (mm/dd/yyyy)</font></td>
    <td bgcolor="#A8A377"><font color="#FFFFFF">&nbsp;</font></td>
    <td bgcolor="#A8A377">&nbsp;</td>
<table width="75%" border="0" align="center" cellpadding="2" cellspacing="1">
    <td bgcolor="#A8A377"><div align="center">
      <input type="image" name="Submit" value="Submit" src="../Add.gif">
<table width="75%" border="0" align="center" cellpadding="8" cellspacing="0">
    <td bgcolor="#545136"><div align="center">

Here is my code for NewWaterAction.cfm:

<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<CFQUERY name="qUpload" datasource="Garden">
INSERT INTO Watering (Zone,
<cflocation url="../Plants/PlantIndexMain.cfm">


What am I doing wrong?

Question by:aj10101
  • 2
LVL 35

Accepted Solution

mrichmon earned 500 total points
ID: 11918166
One problem is that date is a reserved word.

Second problem would be if it was not in a valid format.

Use this :

<CFQUERY datasource="Garden">
INSERT INTO Watering (Zone,DateCol)
     VALUES(<cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.ZoneList#">,
             <cfqueryparam cfsqltype="cf_sql_varchar" value="#DAteFormat(Form.Date1, 'MM/DD/YYYY)# #TimeFormat(Form.Date1, 'h:mm tt')#">)

Author Comment

ID: 11918203
Thanks mrichmon,

That solved the problem. I will look up the reserved words so I do not make that mistake again.

LVL 35

Expert Comment

ID: 11918211
no problem

good luck

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

803 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