Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DB2ParameterCollection and number of parameters

Posted on 2009-05-20
5
Medium Priority
?
398 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 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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…
Integration Management Part 2
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 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