Application uses a value of the wrong type for the current operation. (With Dates)

[Ben Weeks]

Dear All,

I am receiving an error in my ASP application on one server with IIS 5.0 on
Windows 2000 Server, and not on Windows NT Server. Although they were
previously both working fine.

The error message I get is:

Application uses a value of the wrong type for the current operation.

On the line:

cmd("@date_received") = date_received

Where:

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = Application("BSSSQL")
cmd.CommandText = "sp_update_quote"
cmd.Prepared = False cmd.CommandType = 4
cmd.CommandTimeout = 60

and

date_recieved = "13/11/2002 6:22:32 PM"

(i.e. it's the correct format, i.e. dd/mm/yyyy)

I have tried;

cmd("@date_received") = CDate(date_received)

In the stored procedure, sp_update_quote has the following:

@date_received char(20)

Anybody know why it should be working on one server, and not the other?

Regards,

Ben.

--
Business Advisor & IT Consultant
OPAR Ltd.
+44(0)7968 847 178

[Ben Weeks]

[Aaron Bertrand [MVP]]

Could be a difference in regional settings, different versions of MDAC, etc.
You didn't include all of your code, but how about simplifying a bit (in
addition to NOT using dd/mm/yyyy or mm/dd/yyyy format at all, but rather a
universal format which doesn't care what country you're in):

set conn = server.createobject("ADODB.Connection")
conn.open Application("BSSSQL")
conn.execute("EXEC sp_update_quote @date_received='20021113 18:22:32'")

--
Aaron Bertrand, MVP
http://www.aspfaq.com/

FREE server diagnostics component:
http://www.perfhound.com/

"Ben Weeks" <newsg...@itrackersystems.com> wrote in message
news:D9AA9.4171$Uh4....@newsfep1-gui.server.ntli.net...

[Ben Weeks]

[Chip C]

On Wed, 13 Nov 2002 22:25:55 -0000, Ben Weeks allegedly wrote...

> Dear All,
>
> I am receiving an error in my ASP application on one server with IIS 5.0 on
> Windows 2000 Server, and not on Windows NT Server. Although they were
> previously both working fine.
>
> The error message I get is:
>
> Application uses a value of the wrong type for the current operation.
>
> On the line:
>
> cmd("@date_received") = date_received
>
> Where:
>
> Set cmd = Server.CreateObject("ADODB.Command")
> cmd.ActiveConnection = Application("BSSSQL")
> cmd.CommandText = "sp_update_quote"
> cmd.Prepared = False cmd.CommandType = 4
> cmd.CommandTimeout = 60
>
> and
>
> date_recieved = "13/11/2002 6:22:32 PM"
>
> (i.e. it's the correct format, i.e. dd/mm/yyyy)
>
> I have tried;
>
> cmd("@date_received") = CDate(date_received)
>
> In the stored procedure, sp_update_quote has the following:
>
> @date_received char(20)
>

Hmm, did you happen to notice what size that date_received string is?

--
Chip C
Personal site: http://www.chipcom.net/
Christmas Stories: http://www.christmas-stories.com/

[Ben Weeks]

The complete code is quite a lot. Are there any other bits I should have
specifically included?

Unfortunately changing to a format that doesn't matter what format the date
is would mean a lot of development time, although I do agree this would
reduce the chances of errors.

I shall check the MDAC versions and get back to you ... actually how do I
find out what version of MDAC they have installed?

Thanks for the help.

Regards,

Ben Weeks.

"Aaron Bertrand [MVP]" <aaronATaspfaq.com> wrote in message
news:eLbZsV2iCHA.2600@tkmsftngp12...

[Ben Weeks]

"Chip C" <ch...@chipcom.net> wrote in message
news:MPG.183cfc472...@news-server.neo.rr.com...

Yeah, I just noticed that a minute ago. However, when I changed it to
char(21) is still unfortunately didn't work. And, why should it continue to
work on the other server?

PS Sorry about it posting a number of times (don't know why it did that).

Thanks for your feedback.

Regards,

Ben Weeks.

[Sam]

In stored proc make:
@date_received date

In script:
cmd("@date_received").Value = CDate(date_received)
or
date_recieved = CDate("13/11/2002 6:22:32 PM")
cmd("@date_received").Value = date_received

"Ben Weeks" <newsg...@itrackersystems.com> wrote in message

news:MhAA9.814$as.9...@newsfep3-gui.server.ntli.net...

[Ben Weeks]

Dear All,

I have narrowed the problem down quite a bit. I have also simplified the
problem to:

********** TestSQLDate.asp (ASP Page) **********

<%@ Language=VBScript %>
<body>

<%
Public Function GetDateFormat(sDate)
If sDate <> "" Then
GetDateFormat = Day(sDate) & "/" & Month(sDate) & "/" & Year(sDate)
End If
End Function

txtDateReceived = "13/11/2002"

date_received = GetDateFormat(txtDateReceived) & " " & Time()

Response.Write("<p>Date Received = " & date_received & "</p>" & chr(13))

Set cmd = Server.CreateObject("ADODB.Command")

cmd.ActiveConnection = "Driver={SQL
Server};Server=***;Uid=***;Pwd=***;Database=***"
cmd.CommandText = "sp_test"

cmd.Prepared = False
cmd.CommandType = 4
cmd.CommandTimeout = 60

cmd.Parameters.Append cmd.CreateParameter("@date_received", 129, , 20)

cmd("@date_received") = CDate(date_received)

Set rs = cmd.Execute()

If rs.EOF and rs.BOF Then
'No records returned.
Else
Response.Write("<p>Response = " & rs.Fields("test") & "</p>" & chr(13))
End If
%>

</body>

********** sp_test (Stored Procedure) **********

CREATE STORED PROCEDURE sp_test
@date_received char(20)
AS

SELECT test = Convert(datetime, @date_received,103)

******************

I then tested this page on my development machine (using local IIS) and it
worked perfectly, getting:

Date Received = 13/11/2002 08:13:22

Response = 13/11/2002 08:13:22

However, I then uploaded the ASP page to the main server, and I got an
Internal Error 500 message.

If I look closely at the time, I see it is of the format hh:mm:ss, whereas
before when I was getting a sensible error message come back, it was of the
form h:mm:ss PM (i.e. 6:22:32 PM). Does anyone know how to set it so that
IIS sends the time out as 24hr hh:mm:ss?

When I look at the Regional Settings on both IIS servers (development and
main) the format is:

Time Format: HH:mm:ss
Time separator: ":"
AM Symbol: AM
PM Symbol: PM

Can you tell IIS not to use the regional settings somehow as it seems not to
be doing this?

Many thanks.

Regards,

Ben Weeks.

"Aaron Bertrand [MVP]" <aaronATaspfaq.com> wrote in message
news:eLbZsV2iCHA.2600@tkmsftngp12...

[Ben Weeks]

OK, once I had the time formats the same, I was surprised to still have the
error.

However, if I comment out the line:

cmd.Parameters.Append cmd.CreateParameter("@date_received", 129, , 20)

It then works on both servers. Does it need to be in if I have declared it
in the stored procedure?

However, in the actual application, taking out this line results in a
separate error (Item cannot be found in the collection corresponding to the
requested name or ordinal).

Any ideas?

Cheers,

Ben.

"Ben Weeks" <newsg...@itrackersystems.com> wrote in message

news:I6JA9.161$Mi4....@newsfep3-gui.server.ntli.net...

[Aaron Bertrand [MVP]]

> Any ideas?

Yes, instead of using the command object, try the syntax I posted.

[Aaron Bertrand [MVP]]

> Unfortunately changing to a format that doesn't matter what format the
date
> is would mean a lot of development time

Yep, and adding safety features to vehicles adds time to the production
cycle. But I'm sure glad they do it.