riskyricky1972
asked on
Stored Procedure Call on ASP 3.0
I have the following sp from my database programmer.:
CREATE procedure up_AddIndemnitors(
@SSN varchar(9),
@FirstName varchar(30),
@LastName varchar(30),
@MiddleName varchar(30) = null,
@AddrType varchar(1) = null,
@StreetNum varchar(30),
@StreetName varchar(40),
@AptNum varchar(10) = null,
@City varchar(40),
@State varchar(2),
@Zip varchar(9),
@Generation varchar(10) = null,
@DOB datetime = null,
@Direction varchar(10) = null,
@HomeOwn smallint = null,
@HomeLengthinmonth int = null,
@PurchasedDate datetime = null,
@CurrentMarPrice money =null,
@LoanBal money =null,
@BankName varchar(50) = null,
@ChkAcctBal money =null,
@SavAcctBal money =null,
@DriverLicense varchar(20) = null,
@Phone varchar(14) = null,
@Fax varchar(14) = null,
@indemnitorID int OUTPUT)
as
set nocount on
if datalength(ltrim(@MiddleNa me)) = 0 or @MiddleName=''
begin
set @MiddleName=Null
end
if datalength(ltrim(@StreetNa me)) = 0 or @StreetName=''
begin
set @StreetName=Null
end
if datalength(ltrim(@AptNum)) = 0 or @AptNum=''
begin
set @AptNum=Null
end
if datalength(ltrim(@Generati on)) = 0 or @Generation=''
begin
set @Generation=Null
end
if datalength(ltrim(@DOB)) = 0 or @DOB=''
begin
set @DOB=Null
end
if datalength(ltrim(@Directio n)) = 0 or @Direction=''
begin
set @Direction=Null
end
if datalength(ltrim(@Purchase dDate)) = 0 or @PurchasedDate=''
begin
set @PurchasedDate=Null
end
if datalength(ltrim(@BankName )) = 0 or @BankName=''
begin
set @BankName=Null
end
if datalength(ltrim(@DriverLi cense)) = 0 or @DriverLicense=''
begin
set @DriverLicense=Null
end
if datalength(ltrim(@Phone)) = 0 or @Phone=''
begin
set @Phone=Null
end
if datalength(ltrim(@Fax)) = 0 or @Fax=''
begin
set @Fax=Null
end
--081605 Per Ricky's request, only new SSN information is inserted.
if not exists(select SSN from tblIndemnitors where SSN = @SSN)
begin
Insert into tblIndemnitors ( SSN, FirstName, LastName, MiddleName, AddrType, StreetNum,
StreetName, AptNum, City, State, Zip, Generation, DOB, Direction, HomeOwn, HomeLengthinmonth, PurchasedDate, CurrentMarPrice, LoanBal, BankName, ChkAcctBal, SavAcctBal, DriverLicense, Phone, fax)
Values (@SSN, @FirstName, @LastName, @MiddleName, @AddrType, @StreetNum, @StreetName,
@AptNum, @City, @State, @Zip, @Generation, @DOB, @Direction , @HomeOwn, @HomeLengthinmonth, @PurchasedDate, @CurrentMarPrice,
@LoanBal, @BankName, @ChkAcctBal, @SavAcctBal, cast(@DriverLicense as varchar(20)), cast(@Phone as varchar(14)), cast(@Fax as varchar(14)))
--select IndemnitorID from tblIndemnitors where IndemnitorID = @@identity
select @indemnitorID = @@identity
--return
end
else
begin
set @indemnitorID = (select IndemnitorID from tblIndemnitors where SSN = @SSN)
end
'///////////////////////// ////////// ////////// ////////// ////////// ////////// ////////// ////////// ////////// //////
and following asp, it does not work and it points to execute line. any idea?
set objCmd = Server.CreateObject("Adodb .Command")
with objCmd
.ActiveConnection = conBMS
.CommandText = "up_AddIndemnitors"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@ssn",ad VarChar,ad ParamInput ,9,ssn)
.Parameters.Append .CreateParameter("@FirstNa me",adVarC har,adPara mInput,30, fname)
.Parameters.Append .CreateParameter("@LastNam e",adVarCh ar,adParam Input,30,l name)
.Parameters.Append .CreateParameter("@MiddleN ame",adVar Char,adPar amInput,30 ,mname)
.Parameters.Append .CreateParameter("@AddrTyp e",adVarCh ar,adParam Input,1,"P ")
.Parameters.Append .CreateParameter("@StreetN um",adVarC har,adPara mInput,50, streetnum)
.Parameters.Append .CreateParameter("@StreetN ame",adVar Char,adPar amInput,40 ,streetnam e)
.Parameters.Append .CreateParameter("@AptNum" ,adVarChar ,adParamIn put,10,apt num)
.Parameters.Append .CreateParameter("@City",a dVarChar,a dParamInpu t,40,city)
.Parameters.Append .CreateParameter("@State", adVarChar, adParamInp ut,2,state )
.Parameters.Append .CreateParameter("@Zip",ad VarChar,ad ParamInput ,9,zip)
.Parameters.Append .CreateParameter("@Generat ion",adVar Char,adPar amInput,10 ,suffix)
.Parameters.Append .CreateParameter("@DOB",ad VarChar,ad ParamInput ,10,"01/01 /2005")
.Parameters.Append .CreateParameter("@Directi on",adVarC har,adPara mInput,10, NULL)
'///////////////////////// ////////// ////////// ////////// ////////// ////////// ////////// /////
.Parameters.Append .CreateParameter("@HomeOwn ",adsmalli nt,adParam Input,1,NU LL)
.Parameters.Append .CreateParameter("@HomeLen gthinmonth ",adintege r,adParamI nput,1,NUL L)
.Parameters.Append .CreateParameter("@Purchas edDate",ad DBTimeStam p,adParamI nput,10,NU LL)
.Parameters.Append .CreateParameter("@Current MarPrice", adDouble,a dParamInpu t,10,NULL)
.Parameters.Append .CreateParameter("@LoanBal ",adDouble ,adParamIn put,10,nul l)
.Parameters.Append .CreateParameter("@BankNam e",adVarCh ar,adParam Input,50,n ull)
.Parameters.Append .CreateParameter("@ChkAcct Bal",adDou ble,adPara mInput,10, null)
.Parameters.Append .CreateParameter("@SavAcct Bal",adDou ble,adPara mInput,10, null)
.Parameters.Append .CreateParameter("@DriverL icense",ad VarChar,ad ParamInput ,10,null)
.Parameters.Append .CreateParameter("@Phone", adVarChar, adParamInp ut,12,null )
.Parameters.Append .CreateParameter("@Fax",ad VarChar,ad ParamInput ,12,null)
'///////////////////////// ////////// ////////// ////////// ////////// ////////// ////////// ////
'HomeOwn, HomeLengthinmonth, PurchasedDate, CurrentMarPrice, LoanBal, BankName, ChkAcctBal,
'SavAcctBal, DriverLicense, Phone, fax
'rdohomeyn,totalinmonth,tx tpurchased date,txtma rketprice, txtloanbal ance,txtba nk,txtchec kingbalanc e,txtsavin gbalance
'************************* ********** ********** ********** ********** ********** ********** ******
'output parameter
.Parameters.Append .CreateParameter("@indemni torID",adI nteger,adP aramOutput , ,0)
.Execute ,, adExecuteNoRecords
IndemnitorID = .Parameters("@indemnitorID ")
InsertIndemnitor=Indemnito rID
'************************* ********** ********** ********** ********** ********** ********** ******
end with
CREATE procedure up_AddIndemnitors(
@SSN varchar(9),
@FirstName varchar(30),
@LastName varchar(30),
@MiddleName varchar(30) = null,
@AddrType varchar(1) = null,
@StreetNum varchar(30),
@StreetName varchar(40),
@AptNum varchar(10) = null,
@City varchar(40),
@State varchar(2),
@Zip varchar(9),
@Generation varchar(10) = null,
@DOB datetime = null,
@Direction varchar(10) = null,
@HomeOwn smallint = null,
@HomeLengthinmonth int = null,
@PurchasedDate datetime = null,
@CurrentMarPrice money =null,
@LoanBal money =null,
@BankName varchar(50) = null,
@ChkAcctBal money =null,
@SavAcctBal money =null,
@DriverLicense varchar(20) = null,
@Phone varchar(14) = null,
@Fax varchar(14) = null,
@indemnitorID int OUTPUT)
as
set nocount on
if datalength(ltrim(@MiddleNa
begin
set @MiddleName=Null
end
if datalength(ltrim(@StreetNa
begin
set @StreetName=Null
end
if datalength(ltrim(@AptNum))
begin
set @AptNum=Null
end
if datalength(ltrim(@Generati
begin
set @Generation=Null
end
if datalength(ltrim(@DOB)) = 0 or @DOB=''
begin
set @DOB=Null
end
if datalength(ltrim(@Directio
begin
set @Direction=Null
end
if datalength(ltrim(@Purchase
begin
set @PurchasedDate=Null
end
if datalength(ltrim(@BankName
begin
set @BankName=Null
end
if datalength(ltrim(@DriverLi
begin
set @DriverLicense=Null
end
if datalength(ltrim(@Phone)) = 0 or @Phone=''
begin
set @Phone=Null
end
if datalength(ltrim(@Fax)) = 0 or @Fax=''
begin
set @Fax=Null
end
--081605 Per Ricky's request, only new SSN information is inserted.
if not exists(select SSN from tblIndemnitors where SSN = @SSN)
begin
Insert into tblIndemnitors ( SSN, FirstName, LastName, MiddleName, AddrType, StreetNum,
StreetName, AptNum, City, State, Zip, Generation, DOB, Direction, HomeOwn, HomeLengthinmonth, PurchasedDate, CurrentMarPrice, LoanBal, BankName, ChkAcctBal, SavAcctBal, DriverLicense, Phone, fax)
Values (@SSN, @FirstName, @LastName, @MiddleName, @AddrType, @StreetNum, @StreetName,
@AptNum, @City, @State, @Zip, @Generation, @DOB, @Direction , @HomeOwn, @HomeLengthinmonth, @PurchasedDate, @CurrentMarPrice,
@LoanBal, @BankName, @ChkAcctBal, @SavAcctBal, cast(@DriverLicense as varchar(20)), cast(@Phone as varchar(14)), cast(@Fax as varchar(14)))
--select IndemnitorID from tblIndemnitors where IndemnitorID = @@identity
select @indemnitorID = @@identity
--return
end
else
begin
set @indemnitorID = (select IndemnitorID from tblIndemnitors where SSN = @SSN)
end
'/////////////////////////
and following asp, it does not work and it points to execute line. any idea?
set objCmd = Server.CreateObject("Adodb
with objCmd
.ActiveConnection = conBMS
.CommandText = "up_AddIndemnitors"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@ssn",ad
.Parameters.Append .CreateParameter("@FirstNa
.Parameters.Append .CreateParameter("@LastNam
.Parameters.Append .CreateParameter("@MiddleN
.Parameters.Append .CreateParameter("@AddrTyp
.Parameters.Append .CreateParameter("@StreetN
.Parameters.Append .CreateParameter("@StreetN
.Parameters.Append .CreateParameter("@AptNum"
.Parameters.Append .CreateParameter("@City",a
.Parameters.Append .CreateParameter("@State",
.Parameters.Append .CreateParameter("@Zip",ad
.Parameters.Append .CreateParameter("@Generat
.Parameters.Append .CreateParameter("@DOB",ad
.Parameters.Append .CreateParameter("@Directi
'/////////////////////////
.Parameters.Append .CreateParameter("@HomeOwn
.Parameters.Append .CreateParameter("@HomeLen
.Parameters.Append .CreateParameter("@Purchas
.Parameters.Append .CreateParameter("@Current
.Parameters.Append .CreateParameter("@LoanBal
.Parameters.Append .CreateParameter("@BankNam
.Parameters.Append .CreateParameter("@ChkAcct
.Parameters.Append .CreateParameter("@SavAcct
.Parameters.Append .CreateParameter("@DriverL
.Parameters.Append .CreateParameter("@Phone",
.Parameters.Append .CreateParameter("@Fax",ad
'/////////////////////////
'HomeOwn, HomeLengthinmonth, PurchasedDate, CurrentMarPrice, LoanBal, BankName, ChkAcctBal,
'SavAcctBal, DriverLicense, Phone, fax
'rdohomeyn,totalinmonth,tx
'*************************
'output parameter
.Parameters.Append .CreateParameter("@indemni
.Execute ,, adExecuteNoRecords
IndemnitorID = .Parameters("@indemnitorID
InsertIndemnitor=Indemnito
'*************************
end with
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have a number of errors when you declare the parameters. At least make the following changes:
.Parameters.Append .CreateParameter("@ssn",ad
.Parameters.Append .CreateParameter("@FirstNa
.Parameters.Append .CreateParameter("@LastNam
.Parameters.Append .CreateParameter("@MiddleN
.Parameters.Append .CreateParameter("@AddrTyp
.Parameters.Append .CreateParameter("@StreetN
.Parameters.Append .CreateParameter("@StreetN
.Parameters.Append .CreateParameter("@AptNum"
.Parameters.Append .CreateParameter("@City",a
.Parameters.Append .CreateParameter("@State",
.Parameters.Append .CreateParameter("@Zip",ad
.Parameters.Append .CreateParameter("@Generat
.Parameters.Append .CreateParameter("@DOB",ad
.Parameters.Append .CreateParameter("@Directi
'/////////////////////////
.Parameters.Append .CreateParameter("@HomeOwn
.Parameters.Append .CreateParameter("@HomeLen
.Parameters.Append .CreateParameter("@Purchas
.Parameters.Append .CreateParameter("@Current
.Parameters.Append .CreateParameter("@LoanBal
.Parameters.Append .CreateParameter("@BankNam
.Parameters.Append .CreateParameter("@ChkAcct
.Parameters.Append .CreateParameter("@SavAcct
.Parameters.Append .CreateParameter("@DriverL
.Parameters.Append .CreateParameter("@Phone",
.Parameters.Append .CreateParameter("@Fax",ad
.Parameters.Append .CreateParameter("@indemni
There may be more changes required, but without any error message, I can only guess.
Note: Please do not respond with: "It did not work" and nothing else or I will not bother to give any additional feedback.