Link to home
Start Free TrialLog in
Avatar of a222493
a222493

asked on

Dynamically Create SqlParameters collection

Hi,

I am attempting to update my database through a stored procedure.  The stored procedure has several parameters.  I am populating the SqlParamters array using the following:

SqlParameter[] parameters =
      {
            data.MakeInParam("@usrid", SqlDbType.VarChar, 55, logonid),
            data.MakeInParam("@title", SqlDbType.VarChar, 50, curVals[0].ToString()),
            data.MakeInParam("@bspy", SqlDbType.Int, 4, curVals[1].ToString()),
            data.MakeInParam("@ecbpy", SqlDbType.VarChar, 1, curVals[2].ToString()),
            data.MakeInParam("@cbpy", SqlDbType.Int, 4, curVals[3].ToString()),
            data.MakeInParam("@bs", SqlDbType.Int, 4, curVals[4].ToString()),
            data.MakeInParam("@ecb", SqlDbType.VarChar, 1, curVals[5].ToString()),
            data.MakeInParam("@eb", SqlDbType.Int, 4, curVals[6].ToString()),
            data.MakeInParam("@perg", SqlDbType.Decimal, 4, curVals[7].ToString()),
            data.MakeInParam("@msev", SqlDbType.Int, 4, curVals[8].ToString()),
      };

My issue is, that when any of my text boxes which contain Integer values are left blank (so the Integer should be Null in the database) I receive an error message.

I updated my stored procedure to set the integers to null as follows:

CREATE PROCEDURE [dbo].[sp_insert_cash_component] @usrid varchar(50), @title varchar(50), @bspy int = Null, @ecbpy varchar(1), @cbpy int = Null, @bs int = Null, @ecb varchar(1), @eb int = Null , @perg decimal(18,4) = Null, @msev int = Null AS

I would now like to dynamically create the above Parameter Array depending on the value in my textbox.  For example, if curVals[1].ToString() is "", then do not add @bspy to the parameter array.

Is there a way to dynamically declare the SqlParameter array (without providing a size) and then adding parameters to it as necessary?

Thanks,

Dave
Avatar of Mikal613
Mikal613
Flag of United States of America image

drop it and recreate it
ASKER CERTIFIED SOLUTION
Avatar of skpatra
skpatra

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial