Re: Syntax error with dates

From: AndyK (ak_subscribe_at_iinet.net.au)
Date: 02/11/05 

Date: Sat, 12 Feb 2005 09:18:06 +1100

I think the problem lies with the date format in the input field. The form
links to an access database. When I post a date I get an automated iis
response to the effect there is a syntax error in the date. I have tried
various permutations including adding the # required by access with no
result. The form will post OK if I change the field in the database to a
text field but that will not allow me to do what I want to do.

The input is a date picker. Here is the format setup.

<script type="text/javascript">
> Calendar.setup(
> {
> inputField : "mnWhen", // ID of the input field
> ifFormat : "%A, %e %B %Y", // format of the input field
> daFormat : "%A, %e %B %Y",// format of the displayed date
> button : "trigger" // ID of the button
> }
> );
> </script>

This is the error message
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query
expression '#Saturday'.
/calendarupdate/template.asp, line 78

I feel sure that I am missing something obvious. I am not in your league so
sorry if I am wasting your time.

Andy

"Mark Schupp" <notvalid@email.net> wrote in message
news:%23lYrg0EEFHA.1348@TK2MSFTNGP14.phx.gbl...
> just before you execute the statement:
>
> response.write MM_editQuery
> response.end
>
> Send us the output.
>
> --
> --Mark Schupp
> Head of Development
> Integrity eLearning
> www.ielearning.com
>
> "AndyK" <ak_subscribe@iinet.net.au> wrote in message
> news:420ca708$0$25079$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
>> Here is the page I am developing. I am using a date picker to select the
>> date.
>>
>>
>> <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
>> <!--#include file="Connections/connUpdate.asp" -->
>> <%
>> // *** Edit Operations: declare variables
>>
>> // set the form action variable
>> var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
>> if (Request.QueryString) {
>> MM_editAction += "?" + Request.QueryString;
>> }
>>
>> // boolean to abort record edit
>> var MM_abortEdit = false;
>>
>> // query string to execute
>> var MM_editQuery = "";
>> %>
>> <%
>> // *** Insert Record: set variables
>>
>> if (String(Request("MM_insert")) == "frBooking") {
>>
>> var MM_editConnection = MM_connUpdate_STRING;
>> var MM_editTable = "Results";
>> var MM_editRedirectUrl = "confirmation.htm";
>> var MM_fieldsStr = "mnWhen|value";
>> var MM_columnsStr = "When|#,none,NULL";
>>
>> // create the MM_fields and MM_columns arrays
>> var MM_fields = MM_fieldsStr.split("|");
>> var MM_columns = MM_columnsStr.split("|");
>>
>> // set the form values
>> for (var i=0; i+1 < MM_fields.length; i+=2) {
>> MM_fields[i+1] = String(Request.Form(MM_fields[i]));
>> }
>>
>> // append the query string to the redirect URL
>> if (MM_editRedirectUrl && Request.QueryString &&
>> Request.QueryString.Count
>> > 0) {
>> MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?')
>> == -1)?"?":"&")
>> + Request.QueryString;
>> }
>> }
>> %>
>> <%
>> // *** Insert Record: construct a sql insert statement and execute it
>>
>> if (String(Request("MM_insert")) != "undefined") {
>>
>> // create the sql insert statement
>> var MM_tableValues = "", MM_dbValues = "";
>> for (var i=0; i+1 < MM_fields.length; i+=2) {
>> var formVal = MM_fields[i+1];
>> var MM_typesArray = MM_columns[i+1].split(",");
>> var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";
>> var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";
>> var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";
>> if (formVal == "" || formVal == "undefined") {
>> formVal = emptyVal;
>> } else {
>> if (altVal != "") {
>> formVal = altVal;
>> } else if (delim == "'") { // escape quotes
>> formVal = "'" + formVal.replace(/'/g,"''") + "'";
>> } else {
>> formVal = delim + formVal + delim;
>> }
>> }
>> MM_tableValues += ((i != 0) ? "," : "") + MM_columns[i];
>> MM_dbValues += ((i != 0) ? "," : "") + formVal;
>> }
>> MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues +
>> ")
>> values (" + MM_dbValues + ")";
>>
>> if (!MM_abortEdit) {
>> // execute the insert
>> var MM_editCmd = Server.CreateObject('ADODB.Command');
>> MM_editCmd.ActiveConnection = MM_editConnection;
>> MM_editCmd.CommandText = MM_editQuery;
>> MM_editCmd.Execute();
>> MM_editCmd.ActiveConnection.Close();
>>
>> if (MM_editRedirectUrl) {
>> Response.Redirect(MM_editRedirectUrl);
>> }
>> }
>>
>> }
>> %>
>> <html>
>> <head>
>> <title>Template</title>
>> <!--This links the calendar files to the page -->
>> <style type="text/css">@import url(calendar-brown.css);</style>
>> <script type="text/javascript" src="calendar.js"></script>
>> <script type="text/javascript" src="lang/calendar-en.js"></script>
>> <script type="text/javascript" src="calendar-setup.js"></script>
>>
>> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
>> <link href="style.css" rel="style***" type="text/css">
>> </head>
>>
>> <body>
>>
>> <!--This is the form for the calendar -->
>> <form ACTION="<%=MM_editAction%>" METHOD="POST" name="frBooking"
>> id="frBooking" ...>
>> <input type="text" id="mnWhen" name="mnWhen" />
>> <button id="trigger">...</button>
>> <p> </p>
>> <p>
>> <input type="submit" name="Submit" value="Submit">
>> </p>
>> <input type="hidden" name="MM_insert" value="frBooking">
>> </form>
>> <!--This sets up the calendar -->
>> <script type="text/javascript">
>> Calendar.setup(
>> {
>> inputField : "mnWhen", // ID of the input field
>> ifFormat : "%A, %e %B %Y", // format of the input field
>> daFormat : "%A, %e %B %Y",// format of the displayed date
>> button : "trigger" // ID of the button
>> }
>> );
>>
>> </script>
>>
>> </body>
>> </html>
>>
>>
>>
>> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
>> news:esH7uDDEFHA.624@TK2MSFTNGP15.phx.gbl...
>>> AndyK wrote:
>>>> I am trying to update an Access database using an ASP form with a
>>>> date. All I return is a custom error telling me there is a syntax
>>>> error with the field I am trying to pass. Help.
>>>
>>> Since this is a database question I'll (attempt to) answer it here.
>>>
>>> I'm not sure how you expect us to help you: we're not clairvoyants or
>>> mind readers. So, read these articles:
>>>
>>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>>>
>>> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev=/groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN
>>>
>>> http://www.aspfaq.com/show.asp?id=2313 vbscript
>>> http://www.aspfaq.com/show.asp?id=2040 help with dates
>>> http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion
>>>
>>> And if they don't help, post back (to this newsgroup only) and provide
>>> some details.
>>>
>>> Bob Barrows
>>> --
>>> Microsoft MVP - ASP/ASP.NET
>>> Please reply to the newsgroup. This email account is my spam trap so I
>>> don't check it very often. If you must reply off-line, then remove the
>>> "NO SPAM"
>>>
>>
>>
>
>