Solved

Dynamically Create SqlParameters collection

Posted on 2004-10-19
3
714 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 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