DateTime Error :: Implicit Conversion

I'm seem to have an erratic problem with inserting dates into the database.
I am picking up this new error:

][SQL Server]Implicit conversion from data type datetime to int is not
allowed. Use the CONVERT function to run this query.


DateAvailability = "05/05/06"
DateDelivery= "05/05/06"


//.......connection etc----
oCmd.Parameters.append oCmd.CreateParameter("@DateAvailability",
adDBTimeStamp, adParamInput, 8, DateAvailability)
oCmd.Parameters.append oCmd.CreateParameter("@DateDelivery",
adDBTimeStamp, adParamInput, 8, DateDelivery)

I don't understand this error message.....both paramters are going into
datetime fields....what is the solution?

Thanks
J

Re: DateTime Error :: Implicit Conversion

jason@catamaranco.com wrote:
> I'm seem to have an erratic problem with inserting dates into the
> database. I am picking up this new error:
>
> ][SQL Server]Implicit conversion from data type datetime to int is not
> allowed. Use the CONVERT function to run this query.
>
>
> DateAvailability = "05/05/06"
> DateDelivery= "05/05/06"
>
Awful. Just awful. Don't you remember y2k? Always provide dates in an
unabiguous format: yyyy-mm-dd
>
> //.......connection etc----
> oCmd.Parameters.append oCmd.CreateParameter("@DateAvailability",
> adDBTimeStamp, adParamInput, 8, DateAvailability)
> oCmd.Parameters.append oCmd.CreateParameter("@DateDelivery",
> adDBTimeStamp, adParamInput, 8, DateDelivery)
>
> I don't understand this error message.....both paramters are going
> into datetime fields....what is the solution?
>
> Thanks
> J


Make sure your variables contain dates by using CDate before assigning them
to the parameter objects:

DateAvailability = CDate(DateAvailability )

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: DateTime Error :: Implicit Conversion

= "05/05/06"
>> DateDelivery= "05/05/06"
>>
> Awful. Just awful. Don't you remember y2k? Always provide dates in an
> unabiguous format: yyyy-mm-dd
>
> Make sure your variables contain dates by using CDate before assigning
> them to the parameter objects:
>
> DateAvailability = CDate(DateAvailability )

Thanks, Bot, but neither suggestions seem to solve the problem:

DateAvailability = "20050608"
DateDelivery = "20070608"
DateAvailability = cdate(dateavailability)
DateDelivery = cdate(datedelivery)

....
oCmd.Parameters.append oCmd.CreateParameter("@DateAvailability",
adDBTimeStamp, adParamInput, 0, DateAvailability)
oCmd.Parameters.append oCmd.CreateParameter("@DateDelivery",
adDBTimeStamp, adParamInput, 0, DateDelivery)
set oRS = oCmd.execute() '//line 229

I still pick up this damn error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data
type datetime to int is not allowed. Use the CONVERT function to run this
query.
/catamaranco/changestatus/Process.asp, line 229

Re: DateTime Error :: Implicit Conversion

jason@catamaranco.com wrote:
> = "05/05/06"
>>> DateDelivery= "05/05/06"
>>>
>> Awful. Just awful. Don't you remember y2k? Always provide dates in
>> an unabiguous format: yyyy-mm-dd
>>
>> Make sure your variables contain dates by using CDate before
>> assigning them to the parameter objects:
>>
>> DateAvailability = CDate(DateAvailability )
>
> Thanks, Bot, but neither suggestions seem to solve the problem:
>
> DateAvailability = "20050608"
> DateDelivery = "20070608"
> DateAvailability = cdate(dateavailability)
> DateDelivery = cdate(datedelivery)
>
> ...
> oCmd.Parameters.append oCmd.CreateParameter("@DateAvailability",
> adDBTimeStamp, adParamInput, 0, DateAvailability)
> oCmd.Parameters.append oCmd.CreateParameter("@DateDelivery",
> adDBTimeStamp, adParamInput, 0, DateDelivery)
> set oRS = oCmd.execute() '//line 229
>
> I still pick up this damn error:
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion
> from data type datetime to int is not allowed. Use the CONVERT
> function to run this query.
> /catamaranco/changestatus/Process.asp, line 229


You don't get any errors from the CDate statements?

Can you run the procedure successfully in Query Analyzer with the same
arguments?
Use SQL Profiler to see what is actually getting sent to your sql server.
Maybe that will provide the key.

You might want to try my code generator:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear


Bob Barrows
PS. Nothing to do with your problem but why are you using the obsolete ODBC
driver instead of the native OLE DB provider for sql server?
http://www.aspfaq.com/show.asp?id=2126

--
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: DateTime Error :: Implicit Conversion

> Canyou run the procedure successfully in Query Analyzer with the same
> arguments?

Yip. It runs perfectly inside the QA.

> Use SQL Profiler to see what is actually getting sent to your sql server.
> Maybe that will provide the key.
>
> You might want to try my code generator:
> http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear

Yes, I have been using your generator for a while to spit out the correct
parameters and datatypes.


ob Barrows
> PS. Nothing to do with your problem but why are you using the obsolete
> ODBC driver instead of the native OLE DB provider for sql server?
> http://www.aspfaq.com/show.asp?id=2126

I have tried this suggestion but it still also does not alleviate this
error.

I find this error very frustrating. The only thing I have not done that you
suggested is use the profiler as I am still trying to figure out to use the
utitlity correclty..... why should such a simple Date insertion cause so
many problems?

Re: DateTime Error :: Implicit Conversion

jason@catamaranco.com wrote:
>> Canyou run the procedure successfully in Query Analyzer with the same
>> arguments?
>
> Yip. It runs perfectly inside the QA.
>
>> Use SQL Profiler to see what is actually getting sent to your sql
>> server. Maybe that will provide the key.
>>
>> You might want to try my code generator:
>> http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear
>
> Yes, I have been using your generator for a while to spit out the
> correct parameters and datatypes.
>
>
> ob Barrows
>> PS. Nothing to do with your problem but why are you using the
>> obsolete ODBC driver instead of the native OLE DB provider for sql
>> server? http://www.aspfaq.com/show.asp?id=2126
>
> I have tried this suggestion but it still also does not alleviate this
> error.
>
> I find this error very frustrating. The only thing I have not done
> that you suggested is use the profiler as I am still trying to figure
> out to use the utitlity correclty..... why should such a simple Date
> insertion cause so many problems?

How can I tell? I don't know what your procedure looks like, I don't know
what your table looks like.

If you provide your CREATE TABLE script (just the relevant columns) and the
CREATE PROCEDURE script I will try to reproduce your symptoms,

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: DateTime Error :: Implicit Conversion

> If you provide your CREATE TABLE script (just the relevant columns) and
> the
> CREATE PROCEDURE script I will try to reproduce your symptoms,

Thanks Bob. Let me know if I have done this correctly. One thing: Is it
possible that one of the values higher up in the stored prodecure is getting
pulled into the Date parameter...that would explain why the error message
indicateds an INT value is being passed to the DT field?

TABLE
---------
CREATE TABLE [dbo].[StatusRequest] (
[StatusRequestID] [int] IDENTITY (1, 1) NOT NULL ,
[BoatID] [int] NULL ,
[StatusID] [int] NULL ,
[CompID] [int] NULL ,
[DeptID] [int] NULL ,
[CatID] [int] NULL ,
[ConID] [int] NULL ,
[CurrID] [int] NULL ,
[ListingTypeID] [int] NULL ,
[BrokerTypeID] [int] NULL ,
[BrokerID] [int] NULL ,
[BrokerOutside] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Price] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NULL ,
[EmailBuyer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailSeller] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAuthor] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL4] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FTP1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FTP2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FTP3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FTP4] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marina] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RealmAccessID] [int] NULL ,
[Comments] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CommListing] [int] NULL ,
[CommSelling] [int] NULL ,
[StatReqBLN] [int] NULL ,
[DateAvailability] [datetime] NULL ,
[DateDelivery] [datetime] NULL ,
[Document1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Document2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL



STORED PROCEDURE
---------------------------

CREATE Procedure spr_AddStatusRequest
@BoatID int,
@StatusID int,
@CompID int,
@DeptID int,
@CatID int,
@ConID int,
@BrokerTypeID int,
@BrokerID int,
@CurrID int,
@Price varchar (20),
@CreateDate datetime,
@EmailBuyer varchar (100),
@EmailSeller varchar (100),
@EmailAuthor varchar (100),
@URL1 varchar (150),
@URL2 varchar (150),
@URL3 varchar (150),
@URL4 varchar (150),
@FTP1 varchar (150),
@FTP2 varchar (150),
@FTP3 varchar (150),
@FTP4 varchar (150),
@Marina varchar (100),
@StateID varchar (100),
@CountryID varchar (100),
@Comments varchar (3000),
@CommListing int,
@CommSelling int,
@StatReqBLN int,
@ListingTypeID int,
@BrokerOutside varchar (150),
@RealmAccessID int ,
@DateAvailability datetime,
@DateDelivery datetime,
@Document1 varchar (150) ,
@Document2 varchar (150)



BB SP GENERATOR:

Dim cmd, param

Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "spr_AddStatusRequest"
set .ActiveConnection=cnSQL
set param = .createparameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@BoatID", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
set param = .createparameter("@StatusID", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
set param = .createparameter("@CompID", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
set param = .createparameter("@DeptID", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
set param = .createparameter("@CatID", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
set param = .createparameter("@ConID", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
set param = .createparameter("@BrokerTypeID", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@BrokerID", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
set param = .createparameter("@CurrID", adInteger, adParamInput, 0, [put
value here])
.parameters.append param
set param = .createparameter("@Price", adVarChar, adParamInput, 20, [put
value here])
.parameters.append param
set param = .createparameter("@CreateDate", adDBTimeStamp, adParamInput,
0, [put value here])
.parameters.append param
set param = .createparameter("@EmailBuyer", adVarChar, adParamInput, 100,
[put value here])
.parameters.append param
set param = .createparameter("@EmailSeller", adVarChar, adParamInput, 100,
[put value here])
.parameters.append param
set param = .createparameter("@EmailAuthor", adVarChar, adParamInput, 100,
[put value here])
.parameters.append param
set param = .createparameter("@URL1", adVarChar, adParamInput, 150, [put
value here])
.parameters.append param
set param = .createparameter("@URL2", adVarChar, adParamInput, 150, [put
value here])
.parameters.append param
set param = .createparameter("@URL3", adVarChar, adParamInput, 150, [put
value here])
.parameters.append param
set param = .createparameter("@URL4", adVarChar, adParamInput, 150, [put
value here])
.parameters.append param
set param = .createparameter("@FTP1", adVarChar, adParamInput, 150, [put
value here])
.parameters.append param
set param = .createparameter("@FTP2", adVarChar, adParamInput, 150, [put
value here])
.parameters.append param
set param = .createparameter("@FTP3", adVarChar, adParamInput, 150, [put
value here])
.parameters.append param
set param = .createparameter("@FTP4", adVarChar, adParamInput, 150, [put
value here])
.parameters.append param
set param = .createparameter("@Marina", adVarChar, adParamInput, 100, [put
value here])
.parameters.append param
set param = .createparameter("@StateID", adVarChar, adParamInput, 100,
[put value here])
.parameters.append param
set param = .createparameter("@CountryID", adVarChar, adParamInput, 100,
[put value here])
.parameters.append param
set param = .createparameter("@Comments", adVarChar, adParamInput, 3000,
[put value here])
.parameters.append param
set param = .createparameter("@CommListing", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@CommSelling", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@StatReqBLN", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@ListingTypeID", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@BrokerOutside", adVarChar, adParamInput,
150, [put value here])
.parameters.append param
set param = .createparameter("@RealmAccessID", adInteger, adParamInput, 0,
[put value here])
.parameters.append param
set param = .createparameter("@DateAvailability", adDBTimeStamp,
adParamInput, 0, [put value here])
.parameters.append param
set param = .createparameter("@DateDelivery", adDBTimeStamp, adParamInput,
0, [put value here])
.parameters.append param
set param = .createparameter("@Document1", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
set param = .createparameter("@Document2", adVarChar, adParamInput, 150,
[put value here])
.parameters.append param
.execute ,,adexecutenorecords
end with

Re: DateTime Error :: Implicit Conversion

wrote in message
news:eaT$kYwsFHA.3264@TK2MSFTNGP12.phx.gbl...
>
>> If you provide your CREATE TABLE script (just the relevant columns) and
>> the
>> CREATE PROCEDURE script I will try to reproduce your symptoms,
>
> Thanks Bob. Let me know if I have done this correctly. One thing: Is it
> possible that one of the values higher up in the stored prodecure is
> getting pulled into the Date parameter...that would explain why the error
> message indicateds an INT value is being passed to the DT field?
[snip]
> BB SP GENERATOR:
>
> Dim cmd, param
>
> Set cmd=server.CreateObject("ADODB.Command")
> With cmd
> .CommandType=adcmdstoredproc
> .CommandText = "spr_AddStatusRequest"
> set .ActiveConnection=cnSQL
> set param = .createparameter("@RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> .parameters.append param
> set param = .createparameter("@BoatID", adInteger, adParamInput, 0, [put
> value here])
> .parameters.append param
[snip]

Can you provide the actual code you are using instead of the template
returned by Bob's generator? Specifically, I'm interested to see what's
being put into all those "[put value here]" place holders. I suspect you are
mis-assigning one of those values, possibly assigning a local date variable
to an integer parameter.

--
May you be touched by His noodly appendage. RAmen.
http://venganza.org

Re: DateTime Error :: Implicit Conversion

I needed the actual body of the procedure so I can see what it is doing and
run it for myself.. Your initial post mentioned "inserting". Is that the
only action performed by the procedure?

Oh! BTW, you never answered my question about the results of testing this
procedure in QA ... does it run from QA when supplied with the same
arguments being provided from ASP? You will probably need to use Profiler to
see what the actual values being passed to SQL Server are.

Would it be possible for you to cut this down to the minimum required to
produce your error? Create a procedure that just uses the date columns you
showed in your initial post (of course it has to perform the same activity
that the original procedure perfoms)?

Oh! And I don't see any output parameters. Is your procedure returning a
value via a RETURN statement? Is that why you are using an explicit Command
object rather than the "procedure-as-connection-method" technique described
in this post? http://tinyurl.com/jyy0

Bob Barrows
jason@catamaranco.com wrote:
>> If you provide your CREATE TABLE script (just the relevant columns)
>> and the
>> CREATE PROCEDURE script I will try to reproduce your symptoms,
>
> Thanks Bob. Let me know if I have done this correctly. One thing:
> Is it possible that one of the values higher up in the stored
> prodecure is getting pulled into the Date parameter...that would
> explain why the error message indicateds an INT value is being passed
> to the DT field?
>
> TABLE

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: DateTime Error :: Implicit Conversion

Bob, I am embarrassed to say the damn thing is now working.

Yes, the QA did work with the same arguments. I think the problem was
related to duplicate field that was renamed.

I killed that duplicate line in the procedure and it seemed to work. It was
obviously passing an int to the date field.....

I really appreciate your help here I hope I have not wasted your time!


"Bob Barrows [MVP]" wrote in message
news:eZ04IvwsFHA.2968@TK2MSFTNGP10.phx.gbl...

Re: DateTime Error :: Implicit Conversion

jason@catamaranco.com wrote:
> Bob, I am embarrassed to say the damn thing is now working.
>
> Yes, the QA did work with the same arguments. I think the problem was
> related to duplicate field that was renamed.
>
> I killed that duplicate line in the procedure and it seemed to work.
> It was obviously passing an int to the date field.....
>
> I really appreciate your help here I hope I have not wasted your time!
>
>
I'm not sure I understand what you're talking about here but I'm glad you
got it working.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: DateTime Error :: Implicit Conversion

--
May you be touched by His noodly appendage. RAmen.
http://venganza.org
"Bob Barrows [MVP]" wrote in message
news:%239c0aCxsFHA.4028@TK2MSFTNGP10.phx.gbl...
> jason@catamaranco.com wrote:
>> Bob, I am embarrassed to say the damn thing is now working.
>>
>> Yes, the QA did work with the same arguments. I think the problem was
>> related to duplicate field that was renamed.
>>
>> I killed that duplicate line in the procedure and it seemed to work.
>> It was obviously passing an int to the date field.....
>>
>> I really appreciate your help here I hope I have not wasted your time!
>>
>>
> I'm not sure I understand what you're talking about here but I'm glad you
> got it working.

I for one would like to see the actual mistake so others could learn from
it. :)