May 17, 2002 at 5:22 pm
In ASP I'm using an ADO Command object and it's Parameters collection. One parameter needs to be a precision of 5 and scale of 2. The table field data type is decimal with precision of 5 and scale of 2. The Stored Precedure input variable is data type decimal. Setting the Parameter object data type to adDecimal errors out when the code is run. Only by making it adSingle will the Stored Procedure execute. Problem is, if I input anything beyond a whole number the decimal values are dropped! (e.g., 42.25 is stored as 42). What am I doing wrong here?
May 17, 2002 at 8:24 pm
Can you post you ado parameters (what you tried and what you did) and your SP definition? This will help us understand why or what was occurring.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 18, 2002 at 9:46 am
Dear Antares686,
Yes, I'd be glad to post these. Thank you very much for your assistance. The code for the stored procedure follows this, then a bit of explanation of the ASP code containing the ADO Command and Parameters. Afterward, an explanation of what I tried.
SP:
CREATE PROCEDURE usp_SaveUpdateCourse(
@COURSE_ID int,
@COURSE_NAIOMT_NUM varchar(10),
@COURSE_LEVEL varchar(10),
@COURSE_NAME varchar(75),
@COURSE_CEUS decimal,
@COURSE_COMMENTS varchar(1000),
@COURSE_DEACTIVE bit,
@COURSE_CID int,
@status int output
)
AS
DECLARE @intCID int
--Assume alright.
SET @status=0
--Get the current CID value.
BEGIN
SELECT @intCID=COURSE_CID FROM COURSE WHERE COURSE_ID=@COURSE_ID
END
--Check for a change in the record's concurrency id (CID)
IF @intCID <> @COURSE_CID
BEGIN
SET @status=1
END
ELSE
BEGIN
UPDATE COURSE
SET
COURSE_NAIOMT_NUM=@COURSE_NAIOMT_NUM,
COURSE_LEVEL=@COURSE_LEVEL,
COURSE_NAME=@COURSE_NAME,
COURSE_CEUS=@COURSE_CEUS,
COURSE_COMMENTS=@COURSE_COMMENTS,
COURSE_DEACTIVE=@COURSE_DEACTIVE,
--Increment the CID
COURSE_CID=@COURSE_CID + 1
WHERE COURSE_ID=@COURSE_ID
END
GO
Next, the ASP Code:
'Get the command obj.
set objComm = Server.CreateObject("ADODB.Command")
objComm.CommandText="usp_SaveUpdateCourse"
objComm.CommandType=adCmdStoredProc
'Create the parameter and add it to the Para Array.
set objParam = objComm.CreateParameter("COURSE_ID",adInteger,adParamInput,,Request.Form("COURSE_ID"))
objComm.Parameters.append objParam
set objParam = objComm.CreateParameter("COURSE_NAIOMT_NUM",adVarChar,adParamInput,10,Request.Form("COURSE_NAIOMT_NUM"))
objComm.Parameters.append objParam
set objParam = objComm.CreateParameter("COURSE_LEVEL",adVarChar,adParamInput,10,Request.Form("COURSE_LEVEL"))
objComm.Parameters.append objParam
set objParam = objComm.CreateParameter("COURSE_NAME",adVarChar,adParamInput,75,Request.Form("COURSE_NAME"))
objComm.Parameters.append objParam
set objParam = objComm.CreateParameter("COURSE_CEUS",adSingle,adParamInput,,(EmptyStringToNull(Request.Form("COURSE_CEUS"))))
objComm.Parameters.append objParam
set objParam = objComm.CreateParameter("COURSE_COMMENTS",adVarChar,adParamInput,1000,Request.Form("COURSE_COMMENTS"))
objComm.Parameters.append objParam
set objParam = objComm.CreateParameter("COURSE_DEACTIVE",adBoolean,adParamInput,,CkboxToBoolean(Request.Form("COURSE_DEACTIVE")))
objComm.Parameters.append objParam
set objParam = objComm.CreateParameter("COURSE_CID",adInteger,adParamInput,,Request.Form("COURSE_CID"))
objComm.Parameters.append objParam
'Add last parameter as output param for checking status.
set objParam = objComm.CreateParameter("status",adInteger,adParamOutput)
objComm.Parameters.append objParam
Fianlly, here's what I tried:
The table field, COURSE_CEUS is a decimal data type with precision of 5 and scale of 2 (SQL Server 2000). It seemed to me that I needed to use type adDecimal for my ADO Parameter data type as well as decimal data type when I declared my SP input variable. The SP wouldn't run except when I pulled this parameter altogether. That was how I knew it was the culprit. Then I tried ADO adSingle and adDouble as well as adNumeric. I also tried changing the SP data type to those corresponding values. The only setup that allowed the SP to run is what the code reveals: ADO Parameter adSingle, SP variable decimal, table field decimal. The problem is while I can run the SP and save a number, only the integer part saved, not the decimal. So 42.5 saves as 42. Please let me know if any other information will be helpful. I'm deeply indebted to you for your kind assistance.
DoogieB
Edited by - DoogieB on 05/18/2002 09:48:24 AM
May 18, 2002 at 7:08 pm
See if this offers any help.
quote:
Fixing Decimal Data TypesIf the stored procedure you are calling includes a parameter with the SQL Server data type of decimal, money, or smallmoney, you might experience difficulty if you use the ADO data type adDecimal (14) when creating a Parameter object. DTCs assign the ADO data type 14 if they detect a decimal data type in the stored procedure, so you're particularly likely to see the problem if you've used a DTC.
You'll know you have the problem if the stored procedure has one of the decimal data types and you see this error when requesting the Web page that calls the stored procedure:
At least one parameter contained a type that was not supported.
To make the fix, you need to find the line in which the decimal parameter is created. In the script generated by the DTC, look for calls to the CreateParameter method. For example, you might find a line like this:
Set tmpParam = cmdTemp.CreateParameter("@vSalary",
14, 1, 4, [vSalary])
In the CreateParameter method, the second parameter is the data type of the data you are passing. You will find the value 14 there for decimal and money data types. Substitute the ADO data type adNumeric (131), and the stored procedure will work fine.
Found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvid/html/msdn_visql.asp
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 19, 2002 at 11:49 am
Dear Antares686,
Thank you very much for that reference. While it didn't resolve my problem of dropping decimal notation it did get me thinking about the precision and scale issue. I found out after more research that my error consisted of two things:
1)The need to set two properties, NumericScale and Precision, on the ADO Parameter object whenever the data type is adNumeric or adDecimal. That called for two extra lines of code:
set objParam = objComm.CreateParameter("COURSE_CEUS",adDecimal,adParamInput,,(EmptyStringToNull(Request.Form("COURSE_CEUS"))))
objParam.NumericScale=2
objParam.Precision=5
objComm.Parameters.append objParam
and 2)
The SP input variable declaration required specifying the precision and scale like this:
@COURSE_CEUS decimal(5,2)
It works fine now, thanks to your kind assistance. I'm very grateful for this forum and want to offer my sincere appreciation for your time and efforts to help me!
DoogieB
Edited by - DoogieB on 05/19/2002 11:49:54 AM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply