Insert in Access DB

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="">
  </cflock>
  <cfset MM_logoutRedirectPage="../Index.cfm">
  <cfif MM_logoutRedirectPage EQ "">
    <cfset MM_logoutRedirectPage=CGI.SCRIPT_NAME>
  </cfif>
  <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>
      <cfelse>
      <cfset MM_logoutRedirectPage=MM_logoutRedirectPage & "&" & MM_logoutQuery>
    </cfif>
  </cfif>
  <cflocation url="#MM_logoutRedirectPage#" addtoken="no">
</cfif>
<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(""))>
</cflock>
<cfif MM_Username EQ "">
  <cfset MM_referer=CGI.SCRIPT_NAME>
  <cfif CGI.QUERY_STRING NEQ "">
    <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>
  </cfif>
  <cfset MM_failureURL="../Index.cfm?accessdenied=" & URLEncodedFormat(MM_referer)>
  <cflocation url="#MM_failureURL#" addtoken="no">
</cfif>
<cfparam name="URL.ID" default="1">
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfquery name="Recordset1" datasource="Garden">
SELECT *
FROM Plant
WHERE ID = #URL.ID#
</cfquery>
<html>
<cfset today =
      DateFormat(now(), "dddd, mmmm d, yyyy")>


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

<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">
  <tr>
    <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>
    </td>
  </tr>
</table>
<table width="75%" border="0" align="center" cellpadding="1" cellspacing="0">
  <tr>
    <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>
  </tr>
</table>
<table width="75%" border="0" align="center" cellpadding="4" cellspacing="0">
  <tr>
    <td bgcolor="#545136"><div align="center"><font color="#FFFFFF" size="4"><strong><em>Watering
              Notes</em></strong></font></div></td>
  </tr>
</table>
<table width="75%" border="0" align="center" cellpadding="2" cellspacing="1">
  <tr>
    <td bgcolor="#A8A377"><cfoutput></cfoutput></td>
    <td width="50%" bgcolor="#A8A377"><cfoutput>
      <div align="left"></div>
    </cfoutput></td>
  </tr>
  <tr>
    <td bgcolor="#A8A377">&nbsp;</td>
    <td bgcolor="#A8A377"><cfoutput><font color="##FFFFFF"></font></cfoutput></td>
  </tr>
  <tr>
    <td bgcolor="#A8A377">&nbsp;</td>
    <td bgcolor="#A8A377"><cfoutput><font color="##FFFFFF"></font></cfoutput></td>
  </tr>
  <tr>
    <td bgcolor="#A8A377">&nbsp;</td>
    <td bgcolor="#A8A377"><cfoutput><font color="##FFFFFF"></font></cfoutput></td>
  </tr>
  <tr>
    <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>
    </select></td>
  </tr>
  <tr>
    <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>
  </tr>
  <tr>
    <td bgcolor="#A8A377"><font color="#FFFFFF">&nbsp;</font></td>
    <td bgcolor="#A8A377">&nbsp;</td>
  </tr>
</table>
<table width="75%" border="0" align="center" cellpadding="2" cellspacing="1">
  <tr>
    <td bgcolor="#A8A377"><div align="center">
      <input type="image" name="Submit" value="Submit" src="../Add.gif">
    </div></td>
  </tr>
</table>
<table width="75%" border="0" align="center" cellpadding="8" cellspacing="0">
  <tr>
    <td bgcolor="#545136"><div align="center">
        <p>&nbsp;</p>
      </div>
    </td>
  </tr>
</table>
</cfform>
</body>
</html>


Here is my code for NewWaterAction.cfm:

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<CFQUERY name="qUpload" datasource="Garden">
INSERT INTO Watering (Zone,
                                    Date)
      VALUES('#Form.ZoneList#',
               '#Form.Date1#')
</CFQUERY>  
<cflocation url="../Plants/PlantIndexMain.cfm">
<body>

</body>
</html>


What am I doing wrong?

-AJ
aj10101Asked:
Who is Participating?
 
mrichmonCommented:
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')#">)
</CFQUERY>  
0
 
aj10101Author Commented:
Thanks mrichmon,

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

-AJ
0
 
mrichmonCommented:
no problem

good luck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here