What I've programmed is a textbox were users are supposed to enter a date 

which in turn is supposed to be inserted into a field in the database.



I've heard there are some known problems in inserting datefields, so i've 

searched the web for info. on the subject, but nothing has helped me out 

so far. So I'm addressing you, to see if you can help me out.



The date-field in the database is stored in the intl' dateformat: 

dd.mm.yyyy. So here's my bit of code:



varFraDato = Request.Form("txtFraDato")

sqlStr = "INSERT INTO VervMedlem (VervId, MedlemId, AvdelingId, FraDato) " 

& _

 		  "VALUES(varVervId, varMedlemId, varAvdId, 

#varFraDato#) ;"

    dbconn.Execute (sqlStr)

	   dbconn.Close

        Set dbconn = Nothing



I've applied many functions trying to convert the date-variable. When I've 

printed it to the screen it looks fine, but when inserting I always end up 

with the same old error message:



Microsoft JET Database Engine error '80040e07' 

Syntax error in date in query expression '#vFradato#'. 

/adm/datoproblem3.asp, line 91 



I hope some of your are able to help me out here, I'd appreciate if you 

could post a correct insert into-statement.



Regards,



Kenneth

varFraDato is a VBScript variable right? So you need to concatenate this

with the string literal to form the entire SQL Statement:



<%

strSQL = _

    "INSERT INTO VervMedlem (VervId, MedlemId, AvdelingId, FraDato) " & _

    "VALUES(varVervId, varMedlemId, varAvdId, #" & varFraDato & "#)"

Response.Write(strSQL)

Response.End

%>



Now in terms of inserting dates into Access, I suggest you read this:

www.adopenstatic.com/faq/dateswithaccess.asp

as it explains what you need to do to insert dates into a database where you

are not using the default US date format.



Cheers

Ken





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Kenneth Ingar Olsen" <kiolse99@s...>

Subject: [access_asp] Inserting date to a date-field in an Access-db





: What I've programmed is a textbox were users are supposed to enter a date

: which in turn is supposed to be inserted into a field in the database.

:

: I've heard there are some known problems in inserting datefields, so i've

: searched the web for info. on the subject, but nothing has helped me out

: so far. So I'm addressing you, to see if you can help me out.

:

: The date-field in the database is stored in the intl' dateformat:

: dd.mm.yyyy. So here's my bit of code:

:

: varFraDato = Request.Form("txtFraDato")

: sqlStr = "INSERT INTO VervMedlem (VervId, MedlemId, AvdelingId, FraDato) "

: & _

:     "VALUES(varVervId, varMedlemId, varAvdId,

: #varFraDato#) ;"

:     dbconn.Execute (sqlStr)

:    dbconn.Close

:         Set dbconn = Nothing

:

: I've applied many functions trying to convert the date-variable. When I've

: printed it to the screen it looks fine, but when inserting I always end up

: with the same old error message:

:

: Microsoft JET Database Engine error '80040e07'

: Syntax error in date in query expression '#vFradato#'.

: /adm/datoproblem3.asp, line 91

:

: I hope some of your are able to help me out here, I'd appreciate if you

: could post a correct insert into-statement.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Yup varFraDato is a VBScript variable. 



I tried to do what you mentioned above/below. Here's the repsonse.write 

from the sql-statement:

INSERT INTO VervMedlem (VervId, MedlemId, AvdelingId, FraDato) VALUES(4, 

1243, 5, #2000.10.12#) 



I still receive the same old error:

Microsoft JET Database Engine error '80040e07' 

Syntax error in date in query expression '#2000.10.12#'. 



I changed the regional options so that my dateformat is: yyyy.mm.dd.

I left every format and masks in access open but nothing seems to help, 

any clues?



Regards,

Kenneth





> varFraDato is a VBScript variable right? So you need to concatenate this

> with the string literal to form the entire SQL Statement:

> 

> <%

> strSQL = _

>     "INSERT INTO VervMedlem (VervId, MedlemId, AvdelingId, FraDato) " & _

>     "VALUES(varVervId, varMedlemId, varAvdId, #" & varFraDato & "#)"

> Response.Write(strSQL)

> Response.End

> %>

> 

See if this works. I believe that a date literal must be in U.S. date

format, even if the regional options are set otherwise. If  the date is

supposed to be 2000 October 12, then the date literal would be:



    #10.12.2000#





The alternative is to use the DateValue() function, which is supposed to

work according to the regional setting:



    INSERT INTO VervMedlem (VervId, MedlemId, AvdelingId, FraDato)  _

                   VALUES(4, 1243, 5, DateValue('2000.10.12') )





-- Zee









----- Original Message -----

From: "Kenneth Ingar Olsen" <kiolse99@s...>

To: "Access ASP" <access_asp@p...>

Sent: Monday, March 11, 2002 9:34 PM

Subject: [access_asp] Re: Inserting date to a date-field in an Access-db





> Yup varFraDato is a VBScript variable.

>

> I tried to do what you mentioned above/below. Here's the repsonse.write

> from the sql-statement:

> INSERT INTO VervMedlem (VervId, MedlemId, AvdelingId, FraDato) VALUES(4,

> 1243, 5, #2000.10.12#)

>

> I still receive the same old error:

> Microsoft JET Database Engine error '80040e07'

> Syntax error in date in query expression '#2000.10.12#'.

>

> I changed the regional options so that my dateformat is: yyyy.mm.dd.

> I left every format and masks in access open but nothing seems to help,

> any clues?

>

> Regards,

> Kenneth

>

>

> > varFraDato is a VBScript variable right? So you need to concatenate this

> > with the string literal to form the entire SQL Statement:

> >

> > <%

> > strSQL = _

> >     "INSERT INTO VervMedlem (VervId, MedlemId, AvdelingId, FraDato) " &

_

> >     "VALUES(varVervId, varMedlemId, varAvdId, #" & varFraDato & "#)"

> > Response.Write(strSQL)

> > Response.End

> > %>

> >

>

>




$subst('Email.Unsub').

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Zee Computer Consulting" <zee@t...>

Subject: [access_asp] Re: Inserting date to a date-field in an Access-db





: See if this works. I believe that a date literal must be in U.S. date

: format, even if the regional options are set otherwise. If  the date is

: supposed to be 2000 October 12, then the date literal would be:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Any valid date format is acceptable.



That said, if the date can be construed as a US date, it will be. See:

www.adopenstatic.com/faq/dateswithaccess.asp



The solution (in my books) is to use ISO format with / delimiters:

yyyy/mm/dd



Cheers

Ken

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Zee Computer Consulting" <zee@t...>

> Subject: [access_asp] Re: Inserting date to a date-field in an Access-db

> 

> 

> : See if this works. I believe that a date literal must be in U.S. date

> : format, even if the regional options are set otherwise. If  the date is

> : supposed to be 2000 October 12, then the date literal would be:

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> 

> Any valid date format is acceptable.

> 

> That said, if the date can be construed as a US date, it will be. See:

> www.adopenstatic.com/faq/dateswithaccess.asp

> 

> The solution (in my books) is to use ISO format with / delimiters:

> yyyy/mm/dd

> 

> Cheers

> Ken

> 

I found out that The Webhosting company Telenor Business Solutions uses 

American Regional Settings. So end of story. Had to use M/d/YY format.



I tried inserting with a standard dd/mm/yy format and that even worked. 



So thanx for the help,



Kenneth I. Olsen