?
Solved

DB2ParameterCollection and number of parameters

Posted on 2009-05-20
5
Medium Priority
?
393 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
[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
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

771 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