Link to home
Start Free TrialLog in
Avatar of mears11
mears11

asked on

Problem calling stored procedure with numeric/decimal data type

  Ok, from chopping functionality out of my stored procedure, I've found where my ASP call to my stored procedure is messing up.  As it stands here is my stored procedure:

CREATE PROCEDURE sp_uscholar_studentinsert
@pid char(9),
@fname varchar(50),
@lname varchar(50),
@haddr varchar(100),
@hcity varchar(100),
@hstate char(2),
@hzip char(5),
@laddr varchar(100),
@lcity varchar(100),
@lstate char(2),
@lzip char(5),
@hscounty tinyint,
@hsstate char(2),
@hsname varchar(100),
@email varchar(100),
@semcredits smallint,
@totalcredits smallint,
@standing varchar(10),
@gpa numeric(3,2)
AS

if exists(Select * from uscholar_student where pid = @pid)
begin
update uscholar_student set
fname = @fname,
lname = @lname,
haddr = @haddr,
hcity = @hcity,
hstate = @hstate,
hzip = @hzip,
laddr = @laddr,
lcity = @lcity,
lstate = @lstate,
lzip = @lzip,
hscounty = @hscounty,
hsstate = @hsstate,
hsname = @hsname,
email = @email,
semcredits = @semcredits,
totalcredits = @totalcredits,
standing = @standing,
gpa = @gpa
where pid = @pid
end

else
return(1)

Go




The following is the relevant info from my ASP code (the second to last line is the one that doesn't work.  I know this because if I remove the gpa parameter and the gpa portion of the update statement, it works):

.
.
.
gpa = Request.Form("GPA")
.
.
.
set objCmd = Server.CreateObject("ADODB.Command")


objCmd.CommandText = "sp_uscholar_studentinsert"
set objCmd.ActiveConnection = Data3
objCmd.CommandType = adCmdStoredProc

objCmd.Parameters.Append objCmd.CreateParameter("@pid", adChar, adParamInput, 9, Session("pid"))
objCmd.Parameters.Append objCmd.CreateParameter("@fname", adVarChar, adParamInput, 50, fname)
objCmd.Parameters.Append objCmd.CreateParameter("@lname", adVarChar, adParamInput, 50, lname)
objCmd.Parameters.Append objCmd.CreateParameter("@haddr", adVarChar, adParamInput, 100, haddr)
objCmd.Parameters.Append objCmd.CreateParameter("@hcity", adVarChar, adParamInput, 100, hcity)
objCmd.Parameters.Append objCmd.CreateParameter("@hstate", adChar, adParamInput, 2, hstate)
objCmd.Parameters.Append objCmd.CreateParameter("@hzip", adChar, adParamInput, 5, hzip)
objCmd.Parameters.Append objCmd.CreateParameter("@laddr", adVarChar, adParamInput, 100, laddr)
objCmd.Parameters.Append objCmd.CreateParameter("@lcity", adVarChar, adParamInput, 100, lcity)
objCmd.Parameters.Append objCmd.CreateParameter("@lstate", adChar, adParamInput, 2, lstate)
objCmd.Parameters.Append objCmd.CreateParameter("@lzip", adChar, adParamInput, 5, lzip)
objCmd.Parameters.Append objCmd.CreateParameter("@hscounty", adTinyInt, adParamInput, , hscounty)
objCmd.Parameters.Append objCmd.CreateParameter("@hsstate", adChar, adParamInput, 2, hsstate)
objCmd.Parameters.Append objCmd.CreateParameter("@hsname", adVarChar, adParamInput, 100, hsname)
objCmd.Parameters.Append objCmd.CreateParameter("@email", adVarChar, adParamInput, 100, email)
objCmd.Parameters.Append objCmd.CreateParameter("@semcredits", adSmallInt, adParamInput, , semcredits)
objCmd.Parameters.Append objCmd.CreateParameter("@totalcredits", adSmallInt, adParamInput, , totalcredits)
objCmd.Parameters.Append objCmd.CreateParameter("@standing", adVarChar, adParamInput, 10 , standing)
objCmd.Parameters.Append objCmd.CreateParameter("@gpa", adNumeric, adParamInput, , gpa)


objCmd.Execute
Avatar of af500
af500

what's the error?
mears11:

> The following is the relevant info from my ASP code (the second to last line
> is the one that doesn't work.  I know this because if I remove the gpa parameter
> and the gpa portion of the update statement, it works):

If you specify adDecimal or adNumeric data type, you must also set the NumericScale and the Precision properties of the Parameter object.

Hope That Helps,
Dex*
ASKER CERTIFIED SOLUTION
Avatar of Dexstar
Dexstar

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mears11

ASKER

Thanks that was the trick!