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_comp onent] @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
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",
data.MakeInParam("@title",
data.MakeInParam("@bspy", SqlDbType.Int, 4, curVals[1].ToString()),
data.MakeInParam("@ecbpy",
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_comp
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
drop it and recreate it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.