Solved

Dynamically Create SqlParameters collection

Posted on 2004-10-19
3
702 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:a222493
  • 2
3 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 12350515
drop it and recreate it
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 12350526
0
 
LVL 3

Accepted Solution

by:
skpatra earned 250 total points
ID: 12355680
If you want to get this done without too many changes, do this:

ArrayList colList = new ArrayList(); //namespace to include is System.Collection

//now add any number of params based on any condition
if(curVals[0].ToString() != "")  
    ArrayList.Add(data.MakeInParam("@title", SqlDbType.VarChar, 50, curVals[0].ToString()));
  .
  .
  .
//now extract an array from the arraylist
SqlParameter[] parameters = (SqlParameter[])(colList.ToArray(typeof(SqlParameter)));
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Web appliction 7 48
ASP.NET Web API or ASP.NET Core MVC? 3 52
What are the Important skill to have as Asp.net Developer 8 43
Get id from json Data with NewtonSoft 3 34
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now