Solved

Insert in Access DB

Posted on 2004-08-27
3
1 solution
217 Views
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="">
  </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
0
Comment
Question by:aj10101
[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
Join The Community
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
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')#">)
</CFQUERY>  
0
 

Author Comment

by:aj10101
ID: 11918203
Thanks mrichmon,

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

-AJ
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11918211
no problem

good luck
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Access It Now
Question has a verified solution.

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

Find a Broken Link - Coldfusion
Article by: srikanthmadishetti
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Track your sent Emails using Coldfusion.
Article by: srikanthmadishetti
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
How to see a live map of your network in NetCrunch network monitor
Video by: AdRem
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Bar Graphs in an Access Query
Video by: crystal
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Advertise Here

724 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

Advertise Here