Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dynamically Create SqlParameters collection

Posted on 2004-10-19
3
Medium Priority
?
718 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 1000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

636 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