Syntax error with dates

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.

Re: Syntax error with dates

"AndyK" wrote in message
news:420c88c2$0$25097$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> 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.

http://www.aspfaq.com/5003

Re: Syntax error with dates

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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%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"

Re: Syntax error with dates

Thanks very much.


"Bob Barrows [MVP]" 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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%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"
>

Re: Syntax error with dates

Here is the page I am developing. I am using a date picker to select the
date.


<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>

<%
// *** 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);
}
}

}
%>

Re: Syntax error with dates

AndyK wrote:
> Here is the page I am developing.

That was quick. You've already read and comprehended all the articles I
provided links for? I guess this means they didn't help?

> I am using a date picker to select
> the date.

Not sure why that's relevant. A date is a date. You just have to format it
correctly when using it in sql statements (see the articles I linked to in
my first reply)

>
>
> <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
>
> <%
> // *** Edit Operations: declare variables
>
> // set the form action variable
> var MM_editAction = Request.ServerVariables("SCRIPT_NAME");

Ughhh! Dreamweaver! No wonder you're having problems.

> if (Request.QueryString) {
> MM_editAction += "?" + Request.QueryString;

Time to start snipping. You really did not have to show us all this stuff
....



That's enough. I've got my own job to do. I'm not plowing through all this.
Hopefully somebody else will be able to help. In the meantime. You have a
choice:
a. dig in and make an effort to understand every line of this bloated code
b. scrap it all and start from scratch - without the Dreamweaver crutch.

Sorry,
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"

Re: Syntax error with dates

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" wrote in message
news:420ca708$0$25079$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> Here is the page I am developing. I am using a date picker to select the
> date.
>
>
> <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
>
> <%
> // *** 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);
> }
> }
>
> }
> %>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

Re: Syntax error with dates

that's ok. thanks for your help

"Bob Barrows [MVP]" wrote in message
news:O6BC6pDEFHA.2632@TK2MSFTNGP12.phx.gbl...
> AndyK wrote:
>> Here is the page I am developing.
>
> That was quick. You've already read and comprehended all the articles I
> provided links for? I guess this means they didn't help?
>
>> I am using a date picker to select
>> the date.
>
> Not sure why that's relevant. A date is a date. You just have to format it
> correctly when using it in sql statements (see the articles I linked to in
> my first reply)
>
>>
>>
>> <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
>>
>> <%
>> // *** Edit Operations: declare variables
>>
>> // set the form action variable
>> var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
>
> Ughhh! Dreamweaver! No wonder you're having problems.
>
>> if (Request.QueryString) {
>> MM_editAction += "?" + Request.QueryString;
>
> Time to start snipping. You really did not have to show us all this stuff
> ...
>
>
>
> That's enough. I've got my own job to do. I'm not plowing through all
> this. Hopefully somebody else will be able to help. In the meantime. You
> have a choice:
> a. dig in and make an effort to understand every line of this bloated code
> b. scrap it all and start from scratch - without the Dreamweaver crutch.
>
> Sorry,
> 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"
>

Re: Syntax error with dates

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.



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" 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" wrote in message
> news:420ca708$0$25079$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
>> Here is the page I am developing. I am using a date picker to select the
>> date.
>>
>>
>> <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
>>
>> <%
>> // *** 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);
>> }
>> }
>>
>> }
>> %>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>