Date problems with SQL (previously Access code)

[Dave Navarro]

I have the following code:

biddate = request("close_month") & "/" & request("close_day") & "/" &
request("close_year")
bidtime = request("close_time") & " " & request("close_ampm")

userRS.Fields("bid_date") = biddate & " " & bidtime

And I'm getting the following error:

Provider error '80020005'
Type mismatch.

I've tried swapping the month and day values, with no luck. I checked
ASPFAQ.COM, but all the code I found used "INSERT" to insert the record.
This code is being ported over from using an Access database and if I
have to change all these records over to using INSERT, it's going to add
MANY hours to the conversion time and we're past our original deadline
already.

Thanks for any help anyone can provide.

--Dave

[Bob Barrows]

Well, whoever wrote this code originally should have done it correctly in
the first place. You really should be rewriting it to use DML SQL instead of
recordsets, but I understand if you just want to get this running now, and
hopefully rewrite it later.

You say you are porting, does that mean you are now going to be using SQL
Server? I realize that you mention "SQL" in the subject, but Access uses the
SQL language also. It always helps to specify the database type AND version.
Granted, the version isn't really relevant here, but it may be in a future
question, so please get into the habit of telling us both upffront, OK? :-)

I'm going to assume you meant SQL Server.

Let's start with some basic debugging: response.write the biddate and
bidtime variables to make sure they contain what you think they contain.
Show us the results of the response.writes.

It's been awhile since I updated a recordset, but I would expect this to
work (passing the time in 24hr format rather than AM/PM):

userRS.Fields("bid_date") = #20030630 07:45:00#

So try getting your expression to result in something that looks the above -
you may have to use CDate("20030630 07:45:00"). Let us know what works.

Bob Barrows

Dave Navarro wrote:

> I have the following code:
>
> biddate = request("close_month") & "/" & request("close_day") & "/" &
> request("close_year")
> bidtime = request("close_time") & " " & request("close_ampm")
>
> userRS.Fields("bid_date") = biddate & " " & bidtime
>
> And I'm getting the following error:
>
> Provider error '80020005'
> Type mismatch.
>
> I've tried swapping the month and day values, with no luck. I checked
> ASPFAQ.COM, but all the code I found used "INSERT" to insert the
> record. This code is being ported over from using an Access database
> and if I have to change all these records over to using INSERT, it's
> going to add MANY hours to the conversion time and we're past our
> original deadline already.
>
> Thanks for any help anyone can provide.
>
> --Dave

[Bob Barrows]

Yes, you're probably right. As I said, it's been a while. The best point you
made is that a "variant of subtype datetime" is what is needed in this case.

Bob

Mads Holm wrote:

> Bob Barrows wrote:

>> userRS.Fields("bid_date") = #20030630 07:45:00#
>>
>> So try getting your expression to result in something that looks the
>> above - you may have to use CDate("20030630 07:45:00"). Let us know
>> what works.

>
> It seems to me that the following code:
>
> userRS.Fields("bid_date") = TimeSerial(request("close_time"),
> request("close_ampm"),0 ) _
> +DateSerial(request("close_year"),
> request("close_month") ,request("close_day") )
>
> would be a better solution as it constructs a variant of subtype
> datetime in a very controlled manner.
>
> Regards
> Mads Holm