Questiondefault value or passing optional parameter to stored procedure using DAAB ? please guide and help

Topics: Data Access Application Block, Enterprise Library Core, General discussion
Feb 13, 2010 at 4:28 PM

Hello,

 

I am using DAAB with stored procedures. I use stored procedures more then once if possible. If I add a parameter later in stored procedure, I have to supply value for this in functions where it is even not being used as well, other wise it shows an error that number of parameters are not same.

is there some solution for this ?

Or please guide is there some if I add parameter in last of parameter list in stored procedure and set a default value for it and I may have not to provide value for this from functions where I am not using it.

Please guide.

thanks for your attention and time.

Haansi

Feb 15, 2010 at 4:40 AM

Hi,

Can you please try the solution of implementing a optional parameter in your stored procedure as described in this blog. http://weblogs.asp.net/stanleygu/archive/2010/02/08/solution-5-implementing-optional-parameters-in-t-sql-stored-procedures.aspx

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 15, 2010 at 5:55 AM

Thanks a lot for your kind attention and support.

Actually I tried this before but I am not getting why it is not working (sure it because of my lack of knowledge and experience).

What I did in stored procedure is set default valus as null for optional parameters. Furthemore I am doing it in last parameter.

in c# code I am doing like:

public int UpdateCorporateAccountConfirmation(CorporateAccount obj)
        {
            int result = 1;
            
                object objresult = db.ExecuteNonQuery("p_corporateaccount_Confirmations", 2,obj.Id,
                obj.StatementBy, obj.AutoEmail, obj.Email, obj.FaxConfirm, obj.DocsChecked, obj.InfoReceived, obj.TraccrApproved,
                obj.Login, obj.Password, UserStatus.Pending, 0);
                result = Int32.Parse(objresult.ToString());
           
            return result;
        }

Here I have not passed a parameter that was set to optional. but it still say no of parameters is not equal to no of values. can you plz advice ?

thanks for your time and support

Feb 16, 2010 at 4:03 AM

It fails because the overload you used for ExecuteNonQuery explicitly compares the number of parameters you passed and the number of parameters the command should have.  The way to accomplish the behavior you want is to create a DbCommand object first, add parameters to it, and call ExecuteNonQuery against the Database object passing the DbCommand object.

DbCommand cmd = db.GetStoredProcCommand("p_corporateaccount_Confirmations");
db.AddInParameter.....
object objResult = db.ExecuteNonQuery(cmd);

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Feb 16, 2010 at 7:22 AM

thanks