Solved

Insert in Access DB

Posted on 2004-08-27
3
212 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Time Sheet Help 6 46
Internal DNS Zone Issue 13 60
.htacess file 301 redirects that are strange 4 47
Configure a Local PHP Interpreter for Phpstorm 2 194
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now