Stored Procedures error

0
August 16, 2011

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/da

taprocessi

ng.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("@FromDat

e"        ,adDBTimeStamp,adParamInpu

t,10,cdate

(fromdate)

)

                         .Parameters.Append .CreateParameter("@ToDate"

         ,adDBTimeStamp,adParamInpu

t,10,cdate

(todate))

                         .Parameters.Append .CreateParameter("@ExeDate

"         ,adDBTimeStamp,adParamInpu

t,10,cdate

(exedate))

                        .Parameters.Append .CreateParameter("@SuretyL

ine"      ,adInteger,adParamInput,2,

suretyline

)

                         .Parameters.Append .CreateParameter("@Premium

"         ,adCurrency,adParamInput,1

0,premium)

                         .Parameters.Append .CreateParameter("@Commiss

ionRate"  ,adCurrency,adParamInput,1

0,commissi

onrate)

                         .Parameters.Append .CreateParameter("@Commiss

ion"      ,adCurrency,adParamInput,1

0,commissi

on)

                         .Parameters.Append .CreateParameter("@Surchar

ge"       ,adCurrency,adParamInput,1

0,surcharg

e)

                         .Parameters.Append .CreateParameter("@NetPrem

ium"      ,adCurrency,adParamInput,1

0,netpremi

um)

                         .Parameters.Append .CreateParameter("@BondAmo

unt"      ,adCurrency,adParamInput,1

0,bondamou

nt)

                         .Parameters.Append .CreateParameter("@StateCo

de"        ,adInteger,adParamInput,2,

statecode)

                         .Parameters.Append .CreateParameter("@BondTyp

e"        ,adVarChar,adParamInput,10

,bondcode)

                         .Parameters.Append .CreateParameter("@Princip

alID"     ,adInteger,adParamInput,4,

pid)

                         .Parameters.Append .CreateParameter("@Obligee

ID"       ,adInteger,adParamInput,4,

oid)

                         .Parameters.Append .CreateParameter("@AgentID

"         ,adInteger,adParamInput,4,

aid)

                         .Parameters.Append .CreateParameter("@CreditT

ranID1"   ,adInteger,adParamInput,2,

creditTran

ID1)

                         .Parameters.Append .CreateParameter("@CreditT

ranID2"   ,adInteger,adParamInput,2,

creditTran

ID2)

                         .Parameters.Append .CreateParameter("@AttyInF

actID"    ,adInteger,adParamInput,10

,attyinfac

tid)

                         .Parameters.Append .CreateParameter("@UserID"

           ,adInteger,adParamInput,10

,userID)

                         .Parameters.Append .CreateParameter("@Submitt

edDate"   ,adDBTimeStamp,adParamInpu

t,10,cdate

(submitted

Date))

                         .Parameters.Append .CreateParameter("@StatusI

D"            ,adInteger,adParamInput,5,

statusID)

                         .Parameters.Append .CreateParameter("@suretyC

ompanyID" ,adInteger,adParamInput,4,

suretycoID

)

                         .Parameters.Append .CreateParameter("@MisRefi

d"        ,adInteger,adParamInput,2,

MisRefid)

                         'output parameter

                         .Parameters.Append .CreateParameter("@quoteid

",adIntege

r,adParamO

utput,,0)

                         .Execute,,adExecuteNoRecor

ds

                          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

Comments See all(0)

Add comment
0
August 16, 2011

Isn't ToDate a reserved word?  I believe that is a function.  Try changing that name.  Also, that error generally occurs when you have defined your field as an integer and it should actually be a date.  Have you checked the field types?

0
August 16, 2011

One thing I noticed is you set @PowerNo to a adVarChar when it is an Integer in the database.

Not that would be your error.

It's definately an error happening on SQL Server.

I would first suggest you double check all the date columns and ensure they are all set as datetime - I would take a guess one is set as an Integer.

0
August 16, 2011

If you have query analyser - run the stored procedure through it - then you'll know where the error is - if it works it must be a problem in your asp. (Query Analyser will give you a better error description as well)

0
August 16, 2011

Hi Ricky

The size property of the CreateParameter method is critical to making this work Ricky, the size is not the size of data you are parsing, but rather the size of the field in the target table, the only exception to this is binary fields where reservation need not be made for the maximum size but can be set to the length of the binary data being passed.

Your adDBTimeStamps are defined as size 10, not sure if this is related to your error but they should be:

   datetime size 8

   smalldatetime size 4

You have adIntegers defined as many different sizes

   identity size 4

   int size 4

You have adCurrency defined as size 10

   money size 8

   smallmoney size 4

@Suffix text,    

     .Parameters.Append .CreateParameter("@Suffix"

,adVarChar,adParamInput,5,

suffix)

should be:

     .Parameters.Append .CreateParameter("@Suffix"

,adLongVarChar,adParamInpu

t,21474836

47,suffix)

     --- the size on this binary field can be the length of the variable suffix   --             Len(suffix)

     -- defined size of other types such as int and datetime and money are not negotiable

     -- varchars and chars  must/should be same defined size as in table design view

Really do suggest you take the advice offered by weddell and get it happening in Query Analyser first, then migrate the functionality to other platforms.

http://www.experts-exchang e.com/Data bases/ Q_21 194017.htm l#12490601

Take care...

Alan

Related Questions

exporting stored procedures

Good afternoon expert, Trying to move stored procedures from one database to another. Used the DTS Export Wizard in EM to export selected objects. Program looks like it is copying the stored procedure Read More

Views

50

Votes

0

Answers

6

August 15, 2011

New Install of SQL Express 2005 Alongside existing MSDE install produced  problems running stored procedures on the Express Instance.

I have just installed SQL Express 2005 alongside existing MSDE instance. I'm connecting to a newly created db on the Express 2005 install using MS Access 2000. I'm using SQL authetication &quo Read More

Views

21

Votes

0

Answers

14

August 16, 2011

Accessing SQL Stored Procedures that create temporary tables

I've created a SQL stored procedure that I now need to access from Crystal Reports XI via the Database Expert.  When attempting to do so from CR, I am prompted for the single parameter in my Read More

Views

55

Votes

0

Answers

11

August 16, 2011

How do I dynamically assign column name in MySQL stored procedure (with cursors)?

Hello Everyone, Situation: In a stored procedure (with cursors), I have an update statement. in this update statement I want to dynamically assign the column name. This gives problems: ERROR 1064 (420 Read More

Views

47

Votes

0

Answers

3

August 16, 2011

stored procedures give error Object reference not set to an instance of an object.

look my store procedures set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[priceoption] @prdid int,@pricefrom int, @priceto int, @RowCount int output AS BEGIN     SET NOCO Read More

Views

43

Votes

0

Answers

8

August 16, 2011

PHP, mySQL - getting error "Commands out of sync; you can't run this command now" running multiple functions on a page.

I've tried searching for an answer but didn't find anything that worked in this situation - so I hope someone can help with this.   I have a page that calls multiple functions and each fu Read More

Views

55

Votes

0

Answers

7

August 15, 2011

Interbase stored procedures gone missing?

Dear Experts, (1) I have had a very strange occurrence with one of our clients' Interbase (5.6) databases. Several stored procedures simply 'went missing' and the front-end application obv Read More

Views

51

Votes

0

Answers

5

August 16, 2011

MsAccess calling MySQL Stored procedures.  Most effective way

I need some advice from Access and MySQL experts :> I am slowly migrating an access application over to MySQL V5 so I can use Views, Triggers, Stored Procedures, and other enhancements to increase Read More

Views

30

Votes

0

Answers

6

August 15, 2011

ASP Stored Procedure that accesses data in 2 DB2 databases

I?m creating an ASP website that uses (calls) DB2 stored procedures to manipulate data in a DB2 database.  I have a situation where my StarSQL (driver) needs to call a stored procedure in my data Read More

Views

38

Votes

0

Answers

16

August 15, 2011

Cannot access a recordset returned from a stored procedure.

Hello, I am really stumped on this one. I have a web page with numerous recordsets all of which are populated from a SQL server database through stored procedures and views. I am having one particular Read More

Views

39

Votes

0

Answers

9

August 16, 2011

VB.net issue with stored procedures

I have an issue when i create multiple post its  and I send them to myself( Test purposes) or other users.  If I dont want to view I close them down in a fast way. However when closed ,each Read More

Views

67

Votes

0

Answers

3

August 16, 2011

Store Procedures with asp

I have the following stored procedures, all is correct. and now my asp code has the following error message: ADODB.Command error '800a0bb9' Arguments are of the wrong type, are out of acceptab Read More

Views

29

Votes

0

Answers

7

August 16, 2011

How do I connect my ASP page to an existing DLL and call the stored procedure?

I have an ASP page and it is using an existing DLL which will call the stored procedures and returns record sets. I am trying to call the attached function in DLL as follows. set oV = server.CreateObj Read More

Views

48

Votes

0

Answers

2

August 15, 2011

Passing Parameters error - Classic ASP to SQL 2000 stored procedures

Hi! I am not sure where my brain is this week (maybe already on vacation), I have been having parameter passing problems, so maybe someone can help me get a clue. This is my asp page, it is pulling in Read More

Views

38

Votes

0

Answers

5

August 16, 2011

Cannot restart Window Service SharePoint Search Service

I have used Central Administration to stop the Window SharePoint Service Search. I tried to start it again but I got the following error : WSS_Search_ServerName on np:.\pipe\MSSQL$Microsof t##SSEE\sq Read More

Views

22

Votes

0

Answers

3

August 16, 2011

CF Error Involving Stored Procedures

ODBC Error Code = 07001 (Wrong number of parameters) [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error Hint: The cause of this error is usually that your query contains a refere Read More

Views

33

Votes

0

Answers

26

August 16, 2011

invalid object error

Here is the problem I'm encountering. I have a search form with 4 selection lists that are dynamically populated.  The recordsets are created using stored procedures.  The stored procedu Read More

Views

32

Votes

0

Answers

18

August 16, 2011

The precision is invalid. Updating recordset with stored procedure - ASP and MSSQL

Hi. I have just started making webpages with stored procedures in asp. I am using a MSSQL database and ASP VB to develop my pages. So, I am a nubie and not so good in english. Please have patience wit Read More

Views

44

Votes

0

Answers

2

August 16, 2011

Form data and stored procedure

I have a simple HTML form that accepts 5 values from text inputs and attempts to invoke a db2 stored procedure using php and the php_ibm_db2.dll module.  The application breaks at the db2_execute Read More

Views

57

Votes

0

Answers

12

August 16, 2011

Cursors and Stored Procedures for iSeries - For use with Crystal Reports

I want to set up a stored procedure that will be used by Crystal Reports.  The procedure that I've created works but I get a data conversion error from Crystal.  I think I'm on the r Read More

Views

36

Votes

0

Answers

4

August 15, 2011