Stored procedure problem!

Posted on 2000-02-01
Medium Priority
Last Modified: 2008-03-04
This should be an easy one!

I have a table called "renewal", which has a field called "owing" of type varchar(10). The owing field contains only numbers (some of them have decimals). I created a stored procedure called sp_count2 which finds the sum of the "owing" field. The stored procedure works.

The problem is when I call the stored procedure from a web page, I can't retrieve the sum if I set the total variable to type float. If I set it to adInteger, then it returns an integer.

If I set it to adFloat or adNumeric, I get this error:

ADODB.Parameters error '800a0e7c'
The application has improperly defined a Parameter object.

Here's the stored procedure:

   @out_renewal_total float OUTPUT
declare @q_renewal_total float

Select @q_renewal_total = Sum(Convert(float(10,2), owing))
From renewal

select @out_renewal_total = @q_renewal_total

Here's the ASP page:

Dim renewal_total

Call get_count()

Function get_count()

   Dim conn
   set conn = Server.CreateObject("ADODB.Connection")
   conn.Open "dsn"

   ' find total counts for this year
   Call calc_count(conn, renewal_total)

   Set conn = Nothing

End Function

' Calculate total revenue
Sub calc_count(con, renew_total)

   Dim cmdCount
   Set cmdCount = Server.CreateObject("ADODB.Command")
   cmdCount.CommandText = "sp_count2"

   cmdCount.ActiveConnection = con
   cmdCount.CommandType = adCmdStoredProc

   cmdCount.Parameters.Append    cmdCount.CreateParameter("out_renewal_total", adInteger, adParamOutput)


   renew_total = cmdCount.Parameters("out_renewal_total")
   Set cmdCount = Nothing

   Response.Write "total: " & renew_total & "<BR>"

End Sub      

Question by:hobster
1 Comment

Accepted Solution

mmips earned 60 total points
ID: 2481010
Try adSingle...instead

adSingle 4 Indicates aA single-precision floating- point value (DBTYPE_R4).

adNumeric 131 Indicates aAn exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).

adFloat doesn't exist


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question