Solved

DB2ParameterCollection and number of parameters

Posted on 2009-05-20
5
386 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

929 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

14 Experts available now in Live!

Get 1:1 Help Now