Improve company productivity with a Business Account.Sign Up

x
?
Solved

Insert in Access DB

Posted on 2004-08-27
3
1 solution
Medium Priority
?
237 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
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
mrichmon earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Sign Up
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.

Join & Write a Comment

What’s a better deal: Lease to Own or Lease?
Article by: Christian
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Web Hosting: A Beginner's Guide
Article by: Levy
What You Need to Know when Searching for a Webhost Provider
How to resize images by Kernel Bulk Image Resizer tool?
Video by: Sandeep
In the video, one can understand the process of resizing images in single or bulk. Kernel Bulk Image Resizer is an easy to use tool for resizing large number of images. One can add and resize multiple images with this tool in single go. The video sh…
[Webinar] How to Kill Browser Tabs and Tickets Using PowerShell - The Secret Sauce to SaaS
Video by: Experts Exchange
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…
Advertise Here

589 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