Help with inserting date field into database!!! 4

[JoJoH]

Hi,

Could anyone please help me with this? I am trying to insert a date value into my database.

In my code there is a line that requests the date:

Request("payment_date&quot

And I am trying to insert that value into my database, the code is something like this:

strSQL="INSERT INTO Sold (SoldDate) VALUES ( #" & payment_date & "#)"

But I kept getting errors.

I have tried to switch the #" & payment_date & "# to

#' & payment_date & '#

"# & payment_date #"

'" & payment_date "'

'# & payment_date #'

'"# & payment_date #"'

But they all don't work, either I get a datatype mismatch error or expected end error.

I am pretty sure the error is at #" & payment_date & "#, but just don't know how to fix it. Please advice.

Thanks in advance.

 

[Mun]

Can you try this #" & payment_date & "# .

 

[MrGreed]

try:

strSQL="INSERT INTO Sold (SoldDate) VALUES ( #" & cdate(payment_date) & "#)"

* I'm assuming you left of your "WHERE" clause. Otherwise every record will get updated.

Also if your field "SoldDate" setup as a date/time field? "did you just say Minkey?, yes that's what I said."

MrGreed

http://www.pastame.com

 

[JoJoH]

Thanks for your reply, but that is how I've had it in my codes(see above), but error persisted.
Please advice.

Thanks in advance.

 

[JoJoH]

Hi MrGreed,

I was talking to Mun in the above reply, I haven't tried out your codes yet when I was writing that reply, sorry for any confusion. Now I have just tried your codes, but it is giving me this error:

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'cdate'

What did I do wrong? Please advice.

Also what do you mean by "* I'm assuming you left of your "WHERE" clause. Otherwise every record will get updated." ?

Thanks in advance.

 

[shaddow]

As i see from your code you try to get date into a Access database so there is 2 diferent way to deal with the queryes
1. It's the Access himself with uses #date here# in his internal queryes to compare 2 dates also * char for searching whatever letter or word in the database.
All this from MS Access

2. It's the SQL statements who acces an Access database.
All this querryes are SQL compliant so instead of using #date here# for comparing 2 dates you use quotes(') and for searching either 1 letter or more(words,phrases) percentage(%)

So inserting a date in either type of DB's(Access or SQL) you have to use SQL like suerryes

and suposed that you have SoldDate as Date/Time field you have to use this querry

strSQL="INSERT INTO Sold (SoldDate) VALUES('"&payment_date&"')"

i hope i explained well up there(not good at explanations in english) but 1 more thing try to get inside a valid date value for your locals or for your server locals sometimes this is problem if not the querry
 ________
George, M

 

[MrGreed]

ok,

I would do a

response.write cdate(request("payment_date&quot)
response.end

and see if your even getting a date.

what I mean is, in your SQL statement you are including a where clause so you don't update every record in your table "Sold".

Example: sql=&quot;INSERT INTO Sold (SoldDate) VALUES ( #&quot; & cdate(payment_date) & &quot;#) where Sold_ID=&quot; & <YourIDValue>
&quot;did you just say Minkey?, yes that's what I said.&quot;

MrGreed

http://www.pastame.com

 

[Mun]

Use one space in between.
like #&quot;space & space StDate space & space &quot;#.

 

[JoJoH]

Hi George,

Thanks for your reply, I've used your SQL statement above:
strSQL=&quot;INSERT INTO Sold (SoldDate) VALUES('&quot;&payment_date&&quot;')&quot;
but it is giving me this error:

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression

Please advice.

Thanks in advance.

 

[shaddow]

Well that's a perfect SQL unless your date isnt a date actually. try to post the field in page and see if it's same as a normal date from your DB.
It might be that you made the Database on other Date format but Server opens it in someother also.
Try to use the FormatDateTime(date,vbShortDate) it might be helpfull but 1'st try to see the value of date. and post here if you can. ________
George, M

 

[JoJoH]

Hi all,

Thanks for all your replies! Now I need to talk to you all one at a time.

Mun: I have tried your suggestion, I got a date but I am getting this error:

Microsoft JET Database Engine error '80040e14'

Syntax error in date in query expression '#11:15:05 Mar 12'


MrGreed: I did a response.write on cdate(request(&quot;payment_date&quot), I am getting this error:

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'cdate'


George: I did a response.write(request(&quot;payment_date&quot), it gives me the desirable date, now how do I insert that date into my database?

 

[shaddow]

eSo as i suspected it's a Date error

[red]'#[/red]11:15:05 Mar 12'

try to see how date it's passed from Request. ________
George, M

 

[MrGreed]

if u use George's &quot;FormatDateTime(date,vbShortDate)&quot; it should work, the date being passed in is not the right format that is why it errors on cdate() function. &quot;did you just say Minkey?, yes that's what I said.&quot;

MrGreed

http://www.pastame.com

 

[shaddow]

Yep i encountered this kind of error cuz Server got other locale setings then i used to create the DB but i saw an # inside there and i think that is the error if you didnt missed that when posted it. ________
George, M

 

[JoJoH]

George,

Could you please give me an example of FormatDateTime(date,vbShortDate)?

Thanks in advance

 

[JoJoH]

Now when I do just a response.write(request(&quot;payment_date&quot) I get a desirable result, for example 11:46:21 Mar 12, 2003 PST, how do I put that data into the database?

Thanks

 

[MrGreed]

ok dude, change your database field to a text field and then use:

strSQL=&quot;INSERT INTO Sold (SoldDate) VALUES ( '&quot; & payment_date & &quot;')&quot;

rememebr the single quotes.


&quot;did you just say Minkey?, yes that's what I said.&quot;

MrGreed

http://www.pastame.com

 

[shaddow]

Ok try the FormatDateTime(payment_date,vbShortDate) first then add to DB ________
George, M

 

[JoJoH]

I did a response.write FormatDateTime(now(),vbShortDate), I did get the current date no problem, now how do I insert that into my database? Please advice.

Thanks in advance

 

[palbano]

see FAQ faq333-3048

reference 8

please before you post again

-pete