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("@p id", adChar, adParamInput, 9, Session("pid"))
objCmd.Parameters.Append objCmd.CreateParameter("@f name", adVarChar, adParamInput, 50, fname)
objCmd.Parameters.Append objCmd.CreateParameter("@l name", adVarChar, adParamInput, 50, lname)
objCmd.Parameters.Append objCmd.CreateParameter("@h addr", adVarChar, adParamInput, 100, haddr)
objCmd.Parameters.Append objCmd.CreateParameter("@h city", adVarChar, adParamInput, 100, hcity)
objCmd.Parameters.Append objCmd.CreateParameter("@h state", adChar, adParamInput, 2, hstate)
objCmd.Parameters.Append objCmd.CreateParameter("@h zip", adChar, adParamInput, 5, hzip)
objCmd.Parameters.Append objCmd.CreateParameter("@l addr", adVarChar, adParamInput, 100, laddr)
objCmd.Parameters.Append objCmd.CreateParameter("@l city", adVarChar, adParamInput, 100, lcity)
objCmd.Parameters.Append objCmd.CreateParameter("@l state", adChar, adParamInput, 2, lstate)
objCmd.Parameters.Append objCmd.CreateParameter("@l zip", adChar, adParamInput, 5, lzip)
objCmd.Parameters.Append objCmd.CreateParameter("@h scounty", adTinyInt, adParamInput, , hscounty)
objCmd.Parameters.Append objCmd.CreateParameter("@h sstate", adChar, adParamInput, 2, hsstate)
objCmd.Parameters.Append objCmd.CreateParameter("@h sname", adVarChar, adParamInput, 100, hsname)
objCmd.Parameters.Append objCmd.CreateParameter("@e mail", adVarChar, adParamInput, 100, email)
objCmd.Parameters.Append objCmd.CreateParameter("@s emcredits" , adSmallInt, adParamInput, , semcredits)
objCmd.Parameters.Append objCmd.CreateParameter("@t otalcredit s", adSmallInt, adParamInput, , totalcredits)
objCmd.Parameters.Append objCmd.CreateParameter("@s tanding", adVarChar, adParamInput, 10 , standing)
objCmd.Parameters.Append objCmd.CreateParameter("@g pa", adNumeric, adParamInput, , gpa)
objCmd.Execute
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
objCmd.CommandText = "sp_uscholar_studentinsert
set objCmd.ActiveConnection = Data3
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@p
objCmd.Parameters.Append objCmd.CreateParameter("@f
objCmd.Parameters.Append objCmd.CreateParameter("@l
objCmd.Parameters.Append objCmd.CreateParameter("@h
objCmd.Parameters.Append objCmd.CreateParameter("@h
objCmd.Parameters.Append objCmd.CreateParameter("@h
objCmd.Parameters.Append objCmd.CreateParameter("@h
objCmd.Parameters.Append objCmd.CreateParameter("@l
objCmd.Parameters.Append objCmd.CreateParameter("@l
objCmd.Parameters.Append objCmd.CreateParameter("@l
objCmd.Parameters.Append objCmd.CreateParameter("@l
objCmd.Parameters.Append objCmd.CreateParameter("@h
objCmd.Parameters.Append objCmd.CreateParameter("@h
objCmd.Parameters.Append objCmd.CreateParameter("@h
objCmd.Parameters.Append objCmd.CreateParameter("@e
objCmd.Parameters.Append objCmd.CreateParameter("@s
objCmd.Parameters.Append objCmd.CreateParameter("@t
objCmd.Parameters.Append objCmd.CreateParameter("@s
objCmd.Parameters.Append objCmd.CreateParameter("@g
objCmd.Execute
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*
> 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks that was the trick!