Solved

DB2ParameterCollection and number of parameters

Posted on 2009-05-20
5
390 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:LuckyLucks
  • 4
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24435729
did you just try to assign the parameter a different name?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24435735
that is, add the same parameter twice for the ID, but with different names
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 24435751
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
 

Author Comment

by:LuckyLucks
ID: 24435821
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24435874
so use

Select count(*)
        from T
        where ID=? and (TESTID <> ? OR PRODUCTION <> ?)
        and (TESTID='VAL' or PRODUCTID='VAL')
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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