asked on

Microsoft OLE DB Provider for SQL Server error '80040e14'

Microsoft OLE DB Provider for SQL Server error '80040e14'

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

/ezservices/onlinebonds/dataprocessing.asp, line 96

Have above problems from the following asp code, and I do to know how to fix it. any idea?:

set objCmd = Server.CreateObject("Adodb.Command")
                         with objCmd
                              .ActiveConnection = con
                              .CommandText = "Insert_tblQuotes"
                              .CommandType = adCmdStoredProc
                              .Parameters.Append .CreateParameter("@BondNo" ,adInteger,adParamInput,1,bondno)
                          .Parameters.Append .CreateParameter("@Suffix" ,adVarChar,adParamInput,5,suffix)
                              .Parameters.Append .CreateParameter("@PowerNo" ,adVarChar,adParamInput,1,powerno)
                              .Parameters.Append .CreateParameter("@FromDate" ,adDBTimeStamp,adParamInput,10,cdate(fromdate))
                              .Parameters.Append .CreateParameter("@ToDate" ,adDBTimeStamp,adParamInput,10,cdate(todate))
                              .Parameters.Append .CreateParameter("@ExeDate" ,adDBTimeStamp,adParamInput,10,cdate(exedate))
                          .Parameters.Append .CreateParameter("@SuretyLine" ,adInteger,adParamInput,2,suretyline)
                              .Parameters.Append .CreateParameter("@Premium" ,adCurrency,adParamInput,10,premium)
                              .Parameters.Append .CreateParameter("@CommissionRate" ,adCurrency,adParamInput,10,commissionrate)
                              .Parameters.Append .CreateParameter("@Commission" ,adCurrency,adParamInput,10,commission)
                              .Parameters.Append .CreateParameter("@Surcharge" ,adCurrency,adParamInput,10,surcharge)
                              .Parameters.Append .CreateParameter("@NetPremium" ,adCurrency,adParamInput,10,netpremium)
                              .Parameters.Append .CreateParameter("@BondAmount" ,adCurrency,adParamInput,10,bondamount)
                              .Parameters.Append .CreateParameter("@StateCode"        ,adInteger,adParamInput,2,statecode)
                              .Parameters.Append .CreateParameter("@BondType" ,adVarChar,adParamInput,10,bondcode)
                              .Parameters.Append .CreateParameter("@PrincipalID" ,adInteger,adParamInput,4,pid)
                              .Parameters.Append .CreateParameter("@ObligeeID" ,adInteger,adParamInput,4,oid)
                              .Parameters.Append .CreateParameter("@AgentID" ,adInteger,adParamInput,4,aid)
                              .Parameters.Append .CreateParameter("@CreditTranID1" ,adInteger,adParamInput,2,creditTranID1)
                              .Parameters.Append .CreateParameter("@CreditTranID2" ,adInteger,adParamInput,2,creditTranID2)
                              .Parameters.Append .CreateParameter("@AttyInFactID" ,adInteger,adParamInput,10,attyinfactid)
                              .Parameters.Append .CreateParameter("@UserID"        ,adInteger,adParamInput,10,userID)
                              .Parameters.Append .CreateParameter("@SubmittedDate" ,adDBTimeStamp,adParamInput,10,cdate(submittedDate))
                              .Parameters.Append .CreateParameter("@StatusID"        ,adInteger,adParamInput,5,statusID)
                              .Parameters.Append .CreateParameter("@suretyCompanyID" ,adInteger,adParamInput,4,suretycoID)
                              .Parameters.Append .CreateParameter("@MisRefid" ,adInteger,adParamInput,2,MisRefid)
                              'output parameter
                              .Parameters.Append .CreateParameter("@quoteid",adInteger,adParamOutput,,0)
                              .Execute,,adExecuteNoRecords
                               quoteID = .Parameters("@quoteid")
                               session("quoteid") = quoteID
                   end with
'*****************************************************************************************************************
'here is the stored procedures:

CREATE PROCEDURE [dbo].[Insert_tblQuotes]
      @QuoteID int output,
      @BondNo int,
      @Suffix text,
      @PowerNo int,
      @FromDate datetime,
      @ToDate datetime,
      @ExeDate datetime,
      @SuretyLine int,
      @Premium money,
      @CommissionRate decimal(18, 0),
      @Commission money,
      @Surcharge money,
      @NetPremium money,
      @BondAmount money,
      @StateCode int,
      @BondType text,
      @PrincipalID int,
      @ObligeeID int,
      @AgentID int,
      @CreditTranID1 int,
      @CreditTranID2 int,
      @AttyInFactID int,
      @UserID int,
      @SubmittedDate datetime,
      @StatusID int,
      @SuretyCompanyID int,
      @MISrefid int
AS
SET NOCOUNT ON

INSERT INTO [tblQuotes]
(
                  [BondNo],
                  [Suffix],
                  [PowerNo],
                  [FromDate],
                  [ToDate],
                  [ExeDate],
                  [SuretyLine],
                  [Premium],
                  [CommissionRate],
                  [Commission],
                  [Surcharge],
                  [NetPremium],
                  [BondAmount],
                  [StateCode],
                  [BondType],
                  [PrincipalID],
                  [ObligeeID],
                  [AgentID],
                  [CreditTranID1],
                  [CreditTranID2],
                  [AttyInFactID],
                  [UserID],
                  [SubmittedDate],
                  [StatusID],
                  [SuretyCompanyID],
                  [MISrefid]
)
VALUES
(
                  @BondNo,
                  @Suffix,
                  @PowerNo,
                  @FromDate,
                  @ToDate,
                  @ExeDate,
                  @SuretyLine,
                  @Premium,
                  @CommissionRate,
                  @Commission,
                  @Surcharge,
                  @NetPremium,
                  @BondAmount,
                  @StateCode,
                  @BondType,
                  @PrincipalID,
                  @ObligeeID,
                  @AgentID,
                  @CreditTranID1,
                  @CreditTranID2,
                  @AttyInFactID,
                  @UserID,
                  @SubmittedDate,
                  @StatusID,
                  @SuretyCompanyID,
                  @MISrefid
)

SET @QuoteID = @@IDENTITY
GO

Duane Lawrence

I can tell you it is one of the 4 datetimes below that is being inserted into and integer field. I would have to see a script of the table.

@FromDate datetime,
@ToDate datetime,
@ExeDate datetime,
@SubmittedDate datetime,

Use a cast or a convert

convert( int, @FromDate)

This will allow you to experiment a little if you want
SELECT CONVERT(INT, GETDATE())
SELECT CONVERT(INT, '01-01-2000')

Duane

sukumar_diya

hi,
The problem may be in the date format. Try to send the date values as string and convert it as datetime in the stored procedure using the convert statement like convert(datetime,@fromdate,120).

Try this...
Hope it will help you,
Suku

riskyricky1972

ASKER

how to add convert funtion into my existing stored procedures?

Thank you

Duane Lawrence

CREATE PROCEDURE [dbo].[Insert_tblQuotes]
@QuoteID int output,
@BondNo int,
@Suffix text,
@PowerNo int,
@FromDate datetime,
@ToDate datetime,
@ExeDate datetime,
@SuretyLine int,
@Premium money,
@CommissionRate decimal(18, 0),
@Commission money,
@Surcharge money,
@NetPremium money,
@BondAmount money,
@StateCode int,
@BondType text,
@PrincipalID int,
@ObligeeID int,
@AgentID int,
@CreditTranID1 int,
@CreditTranID2 int,
@AttyInFactID int,
@UserID int,
@SubmittedDate datetime,
@StatusID int,
@SuretyCompanyID int,
@MISrefid int
AS
SET NOCOUNT ON

INSERT INTO [tblQuotes]
(
[BondNo],
[Suffix],
[PowerNo],
[FromDate],
[ToDate],
[ExeDate],
[SuretyLine],
[Premium],
[CommissionRate],
[Commission],
[Surcharge],
[NetPremium],
[BondAmount],
[StateCode],
[BondType],
[PrincipalID],
[ObligeeID],
[AgentID],
[CreditTranID1],
[CreditTranID2],
[AttyInFactID],
[UserID],
[SubmittedDate],
[StatusID],
[SuretyCompanyID],
[MISrefid]
)
VALUES
(
@BondNo,
@Suffix,
@PowerNo,
CONVERT(INT, @FromDate),
CONVERT(INT, @ToDate),
CONVERT(INT, @ExeDate),
@SuretyLine,
@Premium,
@CommissionRate,
@Commission,
@Surcharge,
@NetPremium,
@BondAmount,
@StateCode,
@BondType,
@PrincipalID,
@ObligeeID,
@AgentID,
@CreditTranID1,
@CreditTranID2,
@AttyInFactID,
@UserID,
CONVERT(INT, @SubmittedDate),
@StatusID,
@SuretyCompanyID,
@MISrefid
)

SET @QuoteID = @@IDENTITY
GO

riskyricky1972

ASKER

I edited the stored procedure as same as the one above. it should show me same error....

sukumar_diya

Hi,
Please tell me in which format you are sending the date.
Like 2004-11-10 (yyyy-mm-dd) or 11-30-2004(MM-dd-yyyy)

if ur date format from the front end is the first one ie.(yyyy-mm-dd) then try the following ....
if your date format style is different then please refer the CONVERT in Sql Books Online and change the style ..
example for mm-dd-yyyy format use convert(datetime,@format,101)....

Try this ..
Suku

CREATE PROCEDURE [dbo].[Insert_tblQuotes]
@QuoteID int output,
@BondNo int,
@Suffix text,
@PowerNo int,
@FromDate datetime,
@ToDate datetime,
@ExeDate datetime,
@SuretyLine int,
@Premium money,
@CommissionRate decimal(18, 0),
@Commission money,
@Surcharge money,
@NetPremium money,
@BondAmount money,
@StateCode int,
@BondType text,
@PrincipalID int,
@ObligeeID int,
@AgentID int,
@CreditTranID1 int,
@CreditTranID2 int,
@AttyInFactID int,
@UserID int,
@SubmittedDate datetime,
@StatusID int,
@SuretyCompanyID int,
@MISrefid int
AS
SET NOCOUNT ON

INSERT INTO [tblQuotes]
(
[BondNo],
[Suffix],
[PowerNo],
[FromDate],
[ToDate],
[ExeDate],
[SuretyLine],
[Premium],
[CommissionRate],
[Commission],
[Surcharge],
[NetPremium],
[BondAmount],
[StateCode],
[BondType],
[PrincipalID],
[ObligeeID],
[AgentID],
[CreditTranID1],
[CreditTranID2],
[AttyInFactID],
[UserID],
[SubmittedDate],
[StatusID],
[SuretyCompanyID],
[MISrefid]
)
VALUES
(
@BondNo,
@Suffix,
@PowerNo,
CONVERT(datetime, @FromDate,120),
CONVERT(datetime, @ToDate,120),
CONVERT(datetime, @ExeDate,120),
@SuretyLine,
@Premium,
@CommissionRate,
@Commission,
@Surcharge,
@NetPremium,
@BondAmount,
@StateCode,
@BondType,
@PrincipalID,
@ObligeeID,
@AgentID,
@CreditTranID1,
@CreditTranID2,
@AttyInFactID,
@UserID,
CONVERT(datetime, @SubmittedDate,120),
@StatusID,
@SuretyCompanyID,
@MISrefid
)

SET @QuoteID = @@IDENTITY
GO

riskyricky1972

ASKER

the date format is 11/15/04

which number i should use?

sukumar_diya

use 1 as date style

Ex:
convert(datetime,'11/15/04',1)

sukumar

riskyricky1972

ASKER

I have the following stored procedure for testing. but it still does not work.

CREATE PROCEDURE [dbo].[Insert_tblQuotes]
      @QuoteID int output,
      @BondNo int,
      @Suffix text,
      @PowerNo int,
      @FromDate datetime,
      @ToDate datetime,
      @ExeDate datetime,
      @SuretyLine int,
      @Premium money,
      @CommissionRate decimal(18, 0),
      @Commission money,
      @Surcharge money,
      @NetPremium money,
      @BondAmount money,
      @StateCode int,
      @BondType text,
      @PrincipalID int,
      @ObligeeID int,
      @AgentID int,
      @CreditTranID1 int,
      @CreditTranID2 int,
      @AttyInFactID int,
      @UserID int,
      @SubmittedDate datetime,
      @StatusID int,
      @SuretyCompanyID int,
      @MISrefid int

AS

SET NOCOUNT ON

INSERT INTO [tblQuotes]
(

                  [BondNo],
                  [Suffix],
                  [PowerNo],
                  [FromDate],
                  [ToDate],
                  [ExeDate],
                  [SuretyLine],
                  [Premium],
                  [CommissionRate],
                  [Commission],
                  [Surcharge],
                  [NetPremium],
                  [BondAmount],
                  [StateCode],
                  [BondType],
                  [PrincipalID],
                  [ObligeeID],
                  [AgentID],
                  [CreditTranID1],
                  [CreditTranID2],
                  [AttyInFactID],
                  [UserID],
                  [SubmittedDate],
                  [StatusID],
                  [SuretyCompanyID],
                  [MISrefid]
)
VALUES
(
                  @BondNo,
                  @Suffix,
                  @PowerNo,
                  convert(datetime , '01/01/04',1),
                  convert(datetime , '01/01/04',1),
                  convert(datetime , '01/01/04',1),
                  @SuretyLine,
                  @Premium,
                  @CommissionRate,
                  @Commission,
                  @Surcharge,
                  @NetPremium,
                  @BondAmount,
                  @StateCode,
                  @BondType,
                  @PrincipalID,
                  @ObligeeID,
                  @AgentID,
                  @CreditTranID1,
                  @CreditTranID2,
                  @AttyInFactID,
                  @UserID,
                  convert(datetime,'01/01/04',1),
                  @StatusID,
                  @SuretyCompanyID,
                  @MISrefid
)

SET @QuoteID = @@IDENTITY
GO

sukumar_diya

Friend if u have no problem please give me your table structure.
so that i can test...
Sukumar

riskyricky1972

ASKER

[QuoteID] - int primary key
[BondNo], int
[Suffix], text
[PowerNo] int
[FromDate], datetime
[ToDate], datetime
[ExeDate], datetime
[SuretyLine], int
[Premium], money
[CommissionRate], decimal
[Commission], money
[Surcharge], money
[NetPremium], money
[BondAmount], money
[StateCode], int
[BondType], text
[PrincipalID], int
[ObligeeID], int
[AgentID], int
[CreditTranID1], int
[CreditTranID2], int
[AttyInFactID], int
[UserID], int
[SubmittedDate], datetime
[StatusID], int
[SuretyCompanyID], int
[MISrefid] int

sukumar_diya

Hi ricky,
Your stored procedure is working fine. There is no problme with that. I hope the problem is in the asp code or value..
in the asp code i have seen the powerno datatype is advarchar i have changed it to adinteger... I have changed the parameter datatype datetime to varchar in the SP .. test this..

sukumar

Sp
+++++++++++

CREATE PROCEDURE [dbo].[Insert_tblQuotes]
@QuoteID int output,
@BondNo int,
@Suffix text,
@PowerNo int,
@FromDate varchar(8),
@ToDate varchar(8),
@ExeDate varchar(8),
@SuretyLine int,
@Premium money,
@CommissionRate decimal(18, 0),
@Commission money,
@Surcharge money,
@NetPremium money,
@BondAmount money,
@StateCode int,
@BondType text,
@PrincipalID int,
@ObligeeID int,
@AgentID int,
@CreditTranID1 int,
@CreditTranID2 int,
@AttyInFactID int,
@UserID int,
@SubmittedDate varchar(8),
@StatusID int,
@SuretyCompanyID int,
@MISrefid int

AS

SET NOCOUNT ON

INSERT INTO [tblQuotes]
(

[BondNo],
[Suffix],
[PowerNo],
[FromDate],
[ToDate],
[ExeDate],
[SuretyLine],
[Premium],
[CommissionRate],
[Commission],
[Surcharge],
[NetPremium],
[BondAmount],
[StateCode],
[BondType],
[PrincipalID],
[ObligeeID],
[AgentID],
[CreditTranID1],
[CreditTranID2],
[AttyInFactID],
[UserID],
[SubmittedDate],
[StatusID],
[SuretyCompanyID],
[MISrefid]
)
VALUES
(
@BondNo,
@Suffix,
@PowerNo,
convert(datetime , '01/01/04',1),
convert(datetime , '01/01/04',1),
convert(datetime , '01/01/04',1),
@SuretyLine,
@Premium,
@CommissionRate,
@Commission,
@Surcharge,
@NetPremium,
@BondAmount,
@StateCode,
@BondType,
@PrincipalID,
@ObligeeID,
@AgentID,
@CreditTranID1,
@CreditTranID2,
@AttyInFactID,
@UserID,
convert(datetime,'01/01/04',1),
@StatusID,
@SuretyCompanyID,
@MISrefid
)

SET @QuoteID = @@IDENTITY
GO

asp.code
++++++++++++++++

set objCmd = Server.CreateObject("Adodb.Command")
with objCmd
.ActiveConnection = con
.CommandText = "Insert_tblQuotes"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@BondNo" ,adInteger,adParamInput,1,bondno)
.Parameters.Append .CreateParameter("@Suffix" ,adVarWChar ,adParamInput,5,suffix)
.Parameters.Append .CreateParameter("@PowerNo" ,adInteger,adParamInput,1,powerno)
.Parameters.Append .CreateParameter("@FromDate" ,adVarWChar ,adParamInput,8,cdate(fromdate))
.Parameters.Append .CreateParameter("@ToDate" ,adVarWChar ,adParamInput,8,cdate(todate))
.Parameters.Append .CreateParameter("@ExeDate" ,adVarWChar ,adParamInput,8,cdate(exedate))
.Parameters.Append .CreateParameter("@SuretyLine" ,adInteger,adParamInput,2,suretyline)
.Parameters.Append .CreateParameter("@Premium" ,adCurrency,adParamInput,10,premium)
.Parameters.Append .CreateParameter("@CommissionRate" ,adCurrency,adParamInput,10,commissionrate)
.Parameters.Append .CreateParameter("@Commission" ,adCurrency,adParamInput,10,commission)
.Parameters.Append .CreateParameter("@Surcharge" ,adCurrency,adParamInput,10,surcharge)
.Parameters.Append .CreateParameter("@NetPremium" ,adCurrency,adParamInput,10,netpremium)
.Parameters.Append .CreateParameter("@BondAmount" ,adCurrency,adParamInput,10,bondamount)
.Parameters.Append .CreateParameter("@StateCode" ,adInteger,adParamInput,2,statecode)
.Parameters.Append .CreateParameter("@BondType" ,adVarWChar ,adParamInput,10,bondcode)
.Parameters.Append .CreateParameter("@PrincipalID" ,adInteger,adParamInput,4,pid)
.Parameters.Append .CreateParameter("@ObligeeID" ,adInteger,adParamInput,4,oid)
.Parameters.Append .CreateParameter("@AgentID" ,adInteger,adParamInput,4,aid)
.Parameters.Append .CreateParameter("@CreditTranID1" ,adInteger,adParamInput,2,creditTranID1)
.Parameters.Append .CreateParameter("@CreditTranID2" ,adInteger,adParamInput,2,creditTranID2)
.Parameters.Append .CreateParameter("@AttyInFactID" ,adInteger,adParamInput,10,attyinfactid)
.Parameters.Append .CreateParameter("@UserID" ,adInteger,adParamInput,10,userID)
.Parameters.Append .CreateParameter("@SubmittedDate" ,adVarWChar ,adParamInput,8,cdate(submittedDate))
.Parameters.Append .CreateParameter("@StatusID" ,adInteger,adParamInput,5,statusID)
.Parameters.Append .CreateParameter("@suretyCompanyID" ,adInteger,adParamInput,4,suretycoID)
.Parameters.Append .CreateParameter("@MisRefid" ,adInteger,adParamInput,2,MisRefid)
'output parameter
.Parameters.Append .CreateParameter("@quoteid",adInteger,adParamOutput,,0)
.Execute,,adExecuteNoRecords
quoteID = .Parameters("@quoteid")
session("quoteid") = quoteID
end with

riskyricky1972

ASKER

now i have the following error:
Operand type clash: int is incompatible with text

riskyricky1972

ASKER

I actually took out some of the unused field. and now the code look as following:
I tried to use static data to test, but still have the following error...

Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type nvarchar to int.

What data type should I use for test?

set objCmd = Server.CreateObject("Adodb.Command")
                         with objCmd
                              .ActiveConnection = con
                              .CommandText = "Insert_tblQuotes1"
                               .CommandType = adCmdStoredProc
                               .Parameters.Append .CreateParameter("@SuretyLine" ,adInteger,adParamInput,4,1000)
.Parameters.Append .CreateParameter("@Premium" ,adCurrency,adParamInput,9,1)
.Parameters.Append .CreateParameter("@CommissionRate" ,adDouble,adParamInput,9,0.1)
.Parameters.Append .CreateParameter("@Commission" ,adCurrency,adParamInput,8,1)
.Parameters.Append .CreateParameter("@Surcharge" ,adCurrency,adParamInput,8,1)
.Parameters.Append .CreateParameter("@NetPremium" ,adCurrency,adParamInput,8,1)
.Parameters.Append .CreateParameter("@BondAmount" ,adCurrency,adParamInput,8,1)
.Parameters.Append .CreateParameter("@StateCode" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@BondType" ,adVarWChar ,adParamInput,16,"908A")
.Parameters.Append .CreateParameter("@PrincipalID" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@ObligeeID" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@AgentID" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@CreditTranID1" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@CreditTranID2" ,adInteger,adParamInput,2,100)
.Parameters.Append .CreateParameter("@AttyInFactID" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@UserID" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@SubmittedDate" ,adDBTimeStamp ,adParamInput,8,date())
.Parameters.Append .CreateParameter("@StatusID" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@suretyCompanyID" ,adInteger,adParamInput,4,100)
.Parameters.Append .CreateParameter("@MisRefid" ,adInteger,adParamInput,4,100)

                              'output parameter
                              .Parameters.Append .CreateParameter("@quoteid",adInteger,adParamOutput,,0)
                              .Execute,,adExecuteNoRecords
                               quoteID = .Parameters("@quoteid")
                               session("quoteid") = quoteID
                   end with

sukumar_diya

yes Ricky just now i checked it with asp code. I got the error hope we will correct it soon

suku

riskyricky1972

ASKER

the asp codes look right..actaully, it is nothing mor than using command object, it creates error message because of data we provide into parameters...

Agree?

sukumar_diya

yes i agree. the problem is in data

riskyricky1972

ASKER

can you guide or teach me how to handle this/...

ASKER CERTIFIED SOLUTION

sukumar_diya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sukumar_diya

I hope the problem occured by the change of parameter order. Please test it and tell me result.

Sukumar