Exception Details: System.Data.SqlClient.SqlException: Procedure spInsertBillingQ expects

Hello Coders,

I need help fixing a problem in my code that I do not understand. I basically have a procedure that is supposed to take all user information and then insert it to the DB or update the Database based on user entry.

The call to the method is this

Dim

banknameAsString =""If (objBill.BillTransaction(Session("conString"), Session("whattobill"), PROC_CURR, Session("lISN"), 0, Session("fISN"), Request.ServerVariables("REMOTE_ADDR"),"scott@.mycleanstart.com", Session("fAmount"), Session("first_name"), Session("last_name"), Session("city"), Session("state"), Session("phone"), Session("address1"), Session("address2"), Session("postal_code"), Session("email_address"), Session("fDesc"), Session("ssn"), Session("nameoncard"), Session("PAN"), Session("cvv"), Session("cardexpirationdate"), bankname, Session("txtroutingnumber"), Session("txtaccountnumber"), Session("txtchecknumber"), Session("bqISN"), _

returnedbqISN, AuthCode, OrderNumber, DeclineCode, TermCode, ErrorMessage, authenticationValue, authenticationTransactionID, str_Centinal_ECI,

"signup", PAResStatus, SignatureVerification, paypalSubAgreeID, notificationLocation, strErrorNo, strErrorDesc, strTransactionID, strStatus, strStatusCode, strReasonCode))Then

And then below is the actual method itself......

NOTE: The code below is contained in a DLL

1public bool BillTransaction(string strDBInstance,string ccORach,string whichProcessor,Int32 lISN, Int32 mISN,2Int32 fISN,string ip,string merchant_email,string total_amount,3string firstname,string lastname,string city,string state,string phone,string address1,string address2,string zip,string customer_email,4string product_desc,string socialsecuritynum,string nameoncard,string creditcardnumber, Int32 cardverifynum, DateTime cardexpiredate,5string bankname,string routingnumber,string accountnumber,string checknumber, Int32 bqISN,6ref Int32 returnedbqISN,ref string AuthCode,ref string OrderNumber,ref string DeclineCode,7ref string TermCode,ref string ErrorMessage,8string authenticationValue,string authenticationTransactionID,string eci,string transactiontype,9string PAResStatus,string SignatureVerification,string paypalSubAgreeID,string notificationLocation,10ref string strErrorNo,ref string strErrorDesc,ref string strTransactionId,ref string strStatus,11ref string strStatusCode,ref string strReasonCode)1213{1415if (ccORach.Trim().Length <= 0)16{17ErrorMessage ="CC or Check?";18return false;19}2021if (whichProcessor.Trim().Length <= 0)22{23ErrorMessage ="Blank processor";24return false;25}2627decimal grand_total = Convert.ToDecimal(decimal.Parse(total_amount).ToString("N2"));28string ProcessorResponse="";29Database db = DatabaseFactory.CreateDatabase(strDBInstance);30DBCommandWrapper dbCmdWrapper =null;31bool retBilling =true;32string sql ="";33string bqAction ="SBILL";34string strFirstName ="";35string strLastName ="";36string[] cardname = nameoncard.Split(new char[] {' '});37strFirstName = cardname[0];38for (int i=1; i <= cardname.GetUpperBound(0); i++)39{40strLastName = strLastName +' ' + cardname[i].Trim();41}4243MCS_Encryption.Encryption mcscrypt =new MCS_Encryption.Encryption();44//card number45string enc_cardnumber = mcscrypt.Encrypt(creditcardnumber);46//account number47string enc_accountnumber = mcscrypt.Encrypt(accountnumber);4849if (bqISN > 0)50{51//if bqISN is present, do UPDATE instead52sql ="spUpdateBillingQ";53dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);54dbCmdWrapper.AddInParameter("@.bqISN",DbType.Int32,bqISN);55dbCmdWrapper.AddInParameter("@.email",DbType.String,customer_email);56dbCmdWrapper.AddInParameter("@.bankname",DbType.String,bankname);57dbCmdWrapper.AddInParameter("@.routingnumber",DbType.String,routingnumber);58dbCmdWrapper.AddInParameter("@.accountnumber",DbType.String,enc_accountnumber);59dbCmdWrapper.AddInParameter("@.checknumber",DbType.String,checknumber);60dbCmdWrapper.AddInParameter("@.nameoncard",DbType.String,nameoncard);61dbCmdWrapper.AddInParameter("@.cardnumber",DbType.String,enc_cardnumber);62dbCmdWrapper.AddInParameter("@.cardverifynum",DbType.Int32,cardverifynum);63dbCmdWrapper.AddInParameter("@.cardexpirationdate",DbType.DateTime,cardexpiredate);64dbCmdWrapper.AddInParameter("@.billaddress1",DbType.String,address1);65dbCmdWrapper.AddInParameter("@.billaddress2",DbType.String,address2);66dbCmdWrapper.AddInParameter("@.billcity",DbType.String,city);67dbCmdWrapper.AddInParameter("@.billstate",DbType.String,state);68dbCmdWrapper.AddInParameter("@.billzip",DbType.String,zip);69dbCmdWrapper.AddInParameter("@.billphone",DbType.String, phone);70dbCmdWrapper.AddInParameter("@.ip",DbType.String, ip);71dbCmdWrapper.AddInParameter("@.fISN",DbType.Int32, fISN);72dbCmdWrapper.AddInParameter("@.price",DbType.Currency, grand_total);73dbCmdWrapper.AddInParameter("@.description",DbType.String, product_desc);74dbCmdWrapper.AddInParameter("@.bqaction",DbType.String, bqAction);75dbCmdWrapper.AddInParameter("@.creditamount",DbType.Currency, 0);76dbCmdWrapper.AddInParameter("@.processor",DbType.String, whichProcessor);77db.ExecuteNonQuery(dbCmdWrapper) ;7879returnedbqISN = bqISN;80}81else82{83//insert into billingQ or update if bqISN is passed84sql ="spInsertBillingQ";85dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);86dbCmdWrapper.AddInParameter("@.lISN",DbType.Int32,lISN);87dbCmdWrapper.AddInParameter("@.mISN",DbType.Int32,mISN);88dbCmdWrapper.AddInParameter("@.email",DbType.String,customer_email);89dbCmdWrapper.AddInParameter("@.bankname",DbType.String,bankname);90dbCmdWrapper.AddInParameter("@.routingnumber",DbType.String,routingnumber);91dbCmdWrapper.AddInParameter("@.accountnumber",DbType.String,enc_accountnumber);92dbCmdWrapper.AddInParameter("@.checknumber",DbType.String,checknumber);93dbCmdWrapper.AddInParameter("@.nameoncard",DbType.String,nameoncard);94dbCmdWrapper.AddInParameter("@.cardnumber",DbType.String,enc_cardnumber);95dbCmdWrapper.AddInParameter("@.cardverifynum",DbType.Int32,cardverifynum);96dbCmdWrapper.AddInParameter("@.cardexpirationdate",DbType.DateTime,cardexpiredate);97dbCmdWrapper.AddInParameter("@.billaddress1",DbType.String,address1);98dbCmdWrapper.AddInParameter("@.billaddress2",DbType.String,address2);99dbCmdWrapper.AddInParameter("@.billcity",DbType.String,city);100dbCmdWrapper.AddInParameter("@.billstate",DbType.String,state);101dbCmdWrapper.AddInParameter("@.billzip",DbType.String,zip);102dbCmdWrapper.AddInParameter("@.billphone",DbType.String, phone);103dbCmdWrapper.AddInParameter("@.ip",DbType.String, ip);104dbCmdWrapper.AddInParameter("@.fISN",DbType.Int32, fISN);105dbCmdWrapper.AddInParameter("@.price",DbType.Currency, grand_total);106dbCmdWrapper.AddInParameter("@.description",DbType.String, product_desc);107dbCmdWrapper.AddInParameter("@.bqaction",DbType.String, bqAction);108dbCmdWrapper.AddInParameter("@.creditamount",DbType.Currency, 0);109dbCmdWrapper.AddInParameter("@.processor",DbType.String, whichProcessor);110returnedbqISN = 0;111returnedbqISN = (Int32)db.ExecuteScalar(dbCmdWrapper) ;112113}114115116117//bill credit card or ACH118switch (ccORach.Trim())119{120case"CC":121retBilling = BillCreditCard(whichProcessor,ip,merchant_email,grand_total, strFirstName.Trim(), strLastName.Trim(),122city,state, phone, address1,address2, zip, customer_email,123product_desc, socialsecuritynum,creditcardnumber, cardverifynum, cardexpiredate,returnedbqISN,124ref AuthCode,ref OrderNumber,ref DeclineCode,ref TermCode,ref ErrorMessage,ref ProcessorResponse,125authenticationValue,authenticationTransactionID, eci, transactiontype, PAResStatus, SignatureVerification);126break;127case"ACH":128retBilling = BillACH(whichProcessor, ip,merchant_email, grand_total, firstname, lastname,129city,state, phone, address1,address2, zip, customer_email,130product_desc, socialsecuritynum,bankname, routingnumber, accountnumber, checknumber, returnedbqISN,131ref AuthCode,ref OrderNumber,ref DeclineCode,ref TermCode,ref ErrorMessage,ref ProcessorResponse);132break;133case"PAYPAL":134retBilling = BillPayPal(ip,merchant_email,grand_total,firstname,lastname,135city,state,phone,address1,address2,zip,customer_email,136product_desc,socialsecuritynum,returnedbqISN, paypalSubAgreeID, notificationLocation,137ref strErrorNo,ref strErrorDesc,ref strTransactionId,ref strStatus,138ref strStatusCode,ref strReasonCode);139break;140}141142//insert into billingDetail143char bqRecurring ='N';144sql ="spInsertBillingDetail2";145dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);146147dbCmdWrapper.AddInParameter("@.mISN",DbType.Int32,mISN);148dbCmdWrapper.AddInParameter("@.bqISN",DbType.Int32,returnedbqISN);149dbCmdWrapper.AddInParameter("@.bdamountcollected",DbType.Currency,grand_total);150dbCmdWrapper.AddInParameter("@.bdtransactioncode",DbType.String,AuthCode);151dbCmdWrapper.AddInParameter("@.bdstatus",DbType.Boolean,retBilling);152dbCmdWrapper.AddInParameter("@.bdauthcode",DbType.String,AuthCode);153dbCmdWrapper.AddInParameter("@.bdordernumber",DbType.String,OrderNumber);154dbCmdWrapper.AddInParameter("@.bdtracecode",DbType.String,returnedbqISN);155dbCmdWrapper.AddInParameter("@.email",DbType.String,customer_email);156dbCmdWrapper.AddInParameter("@.bankname",DbType.String,bankname);157dbCmdWrapper.AddInParameter("@.routingnumber",DbType.String,routingnumber);158dbCmdWrapper.AddInParameter("@.accountnumber",DbType.String,enc_accountnumber);159dbCmdWrapper.AddInParameter("@.checknumber",DbType.String,checknumber);160dbCmdWrapper.AddInParameter("@.nameoncard",DbType.String,nameoncard);161dbCmdWrapper.AddInParameter("@.cardnumber",DbType.String,enc_cardnumber);162dbCmdWrapper.AddInParameter("@.cardverifynum",DbType.Int32,cardverifynum);163dbCmdWrapper.AddInParameter("@.cardexpirationdate",DbType.DateTime,cardexpiredate);164dbCmdWrapper.AddInParameter("@.billaddress1",DbType.String,address1);165dbCmdWrapper.AddInParameter("@.billaddress2",DbType.String,address2);166dbCmdWrapper.AddInParameter("@.billcity",DbType.String,city);167dbCmdWrapper.AddInParameter("@.billstate",DbType.String,state);168dbCmdWrapper.AddInParameter("@.billzip",DbType.String,zip);169dbCmdWrapper.AddInParameter("@.billphone",DbType.String, phone);170dbCmdWrapper.AddInParameter("@.ip",DbType.String, ip);171dbCmdWrapper.AddInParameter("@.bqrecurring",DbType.String, bqRecurring);172dbCmdWrapper.AddInParameter("@.Message",DbType.String, ErrorMessage);173dbCmdWrapper.AddInParameter("@.ProcessorResponse",DbType.String, ProcessorResponse);174dbCmdWrapper.AddInParameter("@.processor",DbType.String, whichProcessor);175176dbCmdWrapper.AddInParameter("@.strErrorNo",DbType.String, strErrorNo);177dbCmdWrapper.AddInParameter("@.strErrorDesc",DbType.String, strErrorDesc);178dbCmdWrapper.AddInParameter("@.strTransactionId",DbType.String, strTransactionId);179dbCmdWrapper.AddInParameter("@.strStatus",DbType.String, strStatus);180dbCmdWrapper.AddInParameter("@.strStatusCode",DbType.String, strStatusCode);181dbCmdWrapper.AddInParameter("@.strReasonCode",DbType.String, strReasonCode);182183184Int32 returnedbdISN = 0;185returnedbdISN = (Int32)db.ExecuteScalar(dbCmdWrapper) ;186if (retBilling)187{188if (UpdateBillingQStatuses(strDBInstance,returnedbqISN,'S'))189{190return true;191}192}193else194{195if (UpdateBillingQStatuses(strDBInstance,returnedbqISN,'F'))196{197return false;198}199}200return false;201202}

Dollarjunkie:

83//insert into billingQ or update if bqISN is passed
84sql ="spInsertBillingQ";
85dbCmdWrapper = db.GetStoredProcCommandWrapper(sql);
86dbCmdWrapper.AddInParameter("@.lISN",DbType.Int32,lISN);
87dbCmdWrapper.AddInParameter("@.mISN",DbType.Int32,mISN);
88dbCmdWrapper.AddInParameter("@.email",DbType.String,customer_email);
89dbCmdWrapper.AddInParameter("@.bankname",DbType.String,bankname);
90dbCmdWrapper.AddInParameter("@.routingnumber",DbType.String,routingnumber);
91dbCmdWrapper.AddInParameter("@.accountnumber",DbType.String,enc_accountnumber);
92dbCmdWrapper.AddInParameter("@.checknumber",DbType.String,checknumber);
93dbCmdWrapper.AddInParameter("@.nameoncard",DbType.String,nameoncard);
94dbCmdWrapper.AddInParameter("@.cardnumber",DbType.String,enc_cardnumber);
95dbCmdWrapper.AddInParameter("@.cardverifynum",DbType.Int32,cardverifynum);
96dbCmdWrapper.AddInParameter("@.cardexpirationdate",DbType.DateTime,cardexpiredate);
97dbCmdWrapper.AddInParameter("@.billaddress1",DbType.String,address1);
98dbCmdWrapper.AddInParameter("@.billaddress2",DbType.String,address2);
99dbCmdWrapper.AddInParameter("@.billcity",DbType.String,city);
100dbCmdWrapper.AddInParameter("@.billstate",DbType.String,state);
101dbCmdWrapper.AddInParameter("@.billzip",DbType.String,zip);
102dbCmdWrapper.AddInParameter("@.billphone",DbType.String, phone);
103dbCmdWrapper.AddInParameter("@.ip",DbType.String, ip);
104dbCmdWrapper.AddInParameter("@.fISN",DbType.Int32, fISN);
105dbCmdWrapper.AddInParameter("@.price",DbType.Currency, grand_total);
106dbCmdWrapper.AddInParameter("@.description",DbType.String, product_desc);
107dbCmdWrapper.AddInParameter("@.bqaction",DbType.String, bqAction);
108dbCmdWrapper.AddInParameter("@.creditamount",DbType.Currency, 0);
109dbCmdWrapper.AddInParameter("@.processor",DbType.String, whichProcessor);
110returnedbqISN = 0;
111returnedbqISN = (Int32)db.ExecuteScalar(dbCmdWrapper) ;

This would seem to be the pertinent part of your code. Unfortunately you are using a custom object (dbCmdWrapper) and a custom method (AddInParameter) for building your SQL command, so it's probably going to be tough for us to help you without seeing that code. What does dbCmdWrapper look like right before you execute line 111?

|||

Hi,

From the error message, we can see that the @.bankname parameter is missing in the stored procedure execution.

In this case, you can check in your wrapper class to see if it has been added successfully.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!