DB2ParameterCollection and number of parameters

Hi
I have a SQL that takes multiple parameters and populates them with value on the grid. Now, IBM documentation says "The number of parameters in the collection must be equal to the number of parameter placeholders within the command text, or the DB2 .NET Data Provider raises an error." So, how can I add duplicate params to the DB2ParameterCollection.

My SQL: Select count(*) from (Select * from T1 where ID=? EXCEPT Select * from T1 where ID=? AND TESTID=?)

My .NET code tried this (see below, but throws error that DB2ParameterCollection already contains this column)

Reference:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.dndp.doc/htm/frlrfIBMDataDB2DB2ParameterCollectionClassTopic.htm
for (int j = 0; j < paramCols.Count; j++)
{
    string paramCol = paramCols[j].ToString ();
    DB2ColumnTypeInfo paramInfo = (DB2ColumnTypeInfo)paramsInfo[paramCol.ToUpper ()];
    cmd.Parameters.Add (paramCol, paramInfo.DB2Type, paramInfo.length, paramCol);
}
 
for (int k = 0; k < inputCols.Count; k++)
{
    string inputCol = inputCols[k].ToString ();
    cmd.Parameters[k].Value = e.DataControllerRow[inputCol];
}

Open in new window

LuckyLucksAsked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
you can also change your query to this, which will solve your problem, and actually run faster
Select sum(case when testid = ? then 0 else 1 end) as cnt
from T1
where ID=?
0
 
momi_sabagCommented:
did you just try to assign the parameter a different name?
0
 
momi_sabagCommented:
that is, add the same parameter twice for the ID, but with different names
0
 
LuckyLucksAuthor Commented:
Actually my query is as below:
Count of : Select all records, except those identified by parametric cols (that are also PK), and from this set exclude where the value is VAL for two of the given PKs.

Select count(*)from(Select*from T where ID=? EXCEPT Select*from T where ID=?and TESTID =?and PRODUCTID=?)s where s.TESTID='VAL' or s.PRODUCTID='VAL'
0
 
momi_sabagCommented:
so use

Select count(*)
        from T
        where ID=? and (TESTID <> ? OR PRODUCTION <> ?)
        and (TESTID='VAL' or PRODUCTID='VAL')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.