troubleshooting Question

Stored Procedure Call on ASP 3.0

Avatar of riskyricky1972
riskyricky1972 asked on
ASP
2 Comments1 Solution1077 ViewsLast Modified:
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(@MiddleName)) = 0 or @MiddleName=''
   begin
   set @MiddleName=Null
   end

if datalength(ltrim(@StreetName)) = 0 or @StreetName=''
   begin
   set @StreetName=Null
   end

if datalength(ltrim(@AptNum)) = 0 or @AptNum=''
   begin
   set @AptNum=Null
   end

if datalength(ltrim(@Generation)) = 0 or @Generation=''
   begin
   set @Generation=Null
   end

if datalength(ltrim(@DOB)) = 0 or @DOB=''
   begin
   set @DOB=Null
   end

if datalength(ltrim(@Direction)) = 0 or @Direction=''
   begin
   set @Direction=Null
   end

if datalength(ltrim(@PurchasedDate)) = 0 or @PurchasedDate=''
   begin
   set @PurchasedDate=Null
   end

if datalength(ltrim(@BankName)) = 0 or @BankName=''
   begin
   set @BankName=Null
   end

if datalength(ltrim(@DriverLicense)) = 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",adVarChar,adParamInput,9,ssn)
                         .Parameters.Append .CreateParameter("@FirstName",adVarChar,adParamInput,30,fname)
                         .Parameters.Append .CreateParameter("@LastName",adVarChar,adParamInput,30,lname)
                         .Parameters.Append .CreateParameter("@MiddleName",adVarChar,adParamInput,30,mname)
                         .Parameters.Append .CreateParameter("@AddrType",adVarChar,adParamInput,1,"P")
                         .Parameters.Append .CreateParameter("@StreetNum",adVarChar,adParamInput,50,streetnum)
                         .Parameters.Append .CreateParameter("@StreetName",adVarChar,adParamInput,40,streetname)
                         .Parameters.Append .CreateParameter("@AptNum",adVarChar,adParamInput,10,aptnum)
                         .Parameters.Append .CreateParameter("@City",adVarChar,adParamInput,40,city)
                         .Parameters.Append .CreateParameter("@State",adVarChar,adParamInput,2,state)
                         .Parameters.Append .CreateParameter("@Zip",adVarChar,adParamInput,9,zip)
                         .Parameters.Append .CreateParameter("@Generation",adVarChar,adParamInput,10,suffix)
                         .Parameters.Append .CreateParameter("@DOB",adVarChar,adParamInput,10,"01/01/2005")
                         .Parameters.Append .CreateParameter("@Direction",adVarChar,adParamInput,10,NULL)
                          '//////////////////////////////////////////////////////////////////////////////////////////
                         .Parameters.Append .CreateParameter("@HomeOwn",adsmallint,adParamInput,1,NULL)
                         .Parameters.Append .CreateParameter("@HomeLengthinmonth",adinteger,adParamInput,1,NULL)
                         .Parameters.Append .CreateParameter("@PurchasedDate",adDBTimeStamp,adParamInput,10,NULL)
                         .Parameters.Append .CreateParameter("@CurrentMarPrice",adDouble,adParamInput,10,NULL)
                         .Parameters.Append .CreateParameter("@LoanBal",adDouble,adParamInput,10,null)
                         .Parameters.Append .CreateParameter("@BankName",adVarChar,adParamInput,50,null)
                         .Parameters.Append .CreateParameter("@ChkAcctBal",adDouble,adParamInput,10,null)
                         .Parameters.Append .CreateParameter("@SavAcctBal",adDouble,adParamInput,10,null)
                         .Parameters.Append .CreateParameter("@DriverLicense",adVarChar,adParamInput,10,null)
                         .Parameters.Append .CreateParameter("@Phone",adVarChar,adParamInput,12,null)
                         .Parameters.Append .CreateParameter("@Fax",adVarChar,adParamInput,12,null)
                         '/////////////////////////////////////////////////////////////////////////////////////////
                         
                         'HomeOwn, HomeLengthinmonth, PurchasedDate, CurrentMarPrice, LoanBal, BankName, ChkAcctBal,
                         'SavAcctBal, DriverLicense, Phone, fax
                                     'rdohomeyn,totalinmonth,txtpurchaseddate,txtmarketprice,txtloanbalance,txtbank,txtcheckingbalance,txtsavingbalance
                         '*******************************************************************************************
                         'output parameter
                         .Parameters.Append .CreateParameter("@indemnitorID",adInteger,adParamOutput, ,0)
                                     .Execute ,, adExecuteNoRecords
                         IndemnitorID = .Parameters("@indemnitorID")
                         InsertIndemnitor=IndemnitorID
                         '*******************************************************************************************
             end with
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros