Select Count query in DB2 with Array

Question

I am trying to Count all records in my DB2 database with a query where is uses the contents of an Array to search the Table.

           string[] strNumbers = txtNumbers.Text.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);

               string strSearch = "";
                   strSearch = "SELECT COUNT(*) FROM TABLENAME WHERE NUMBER = '" + strNumbers + "' AND COMMENT = '" + strMessage + "'";

                DB2Command cmdSearchTable = new DB2Command(strSearch, db2Connection);

                int nodeCount = 0;
                nodeCount = int.Parse(cmdSearchTable.ExecuteScalar().ToString());

if (nodeCount == 0) {
 Not Found          
 } else
  { Found
   }

This code throws up an exception ({"The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use."})

However if I use strNumbers.Length it doesn't throw up an error but the nodeCount it still 0.

Do I need to loop it or is it something else? The query and database connection is fine as I am able to Select,Insert into the database from the same program.

Thanks

EDIT - I have managed to solve this (See accepted answer) but now have a another problem. Lets say '1234567' is in the database once it will return 1 which is fine. If the input it '5551234' which isn't in the database it returns 0 which is also fine. However the problem lies with if the input is '1234567,5551234' the Count will return 1 as 1234567 is in the database, even though 5551234 isn't.

Is there a way of outputting 1 for 1234567 and then 0 for 5551234?

Db2 and MySQL are different products. Don't tag products not involved... — jarlh, Apr 05 '16 at 09:46
`strNumbers` is an array, so you can't concatenate string and array. what is the expected query string ? — Hari Prasad, Apr 05 '16 at 09:57
The expected input from the user will be numbers, could be 1234567 or 1234567, 7654321 etc. I want to Count the database for these. They all store in the array strNumbers. @HariPrasad — user6097989, Apr 05 '16 at 10:02
I'm not familiar with `DB2` syntax, just bear with me :-). Your final query should be something like `SELECT COUNT(*) FROM TABLENAME WHERE NUMBER = '" 1234567, 1234567, 7654321 "' AND COMMENT = '" + strMessage + "'"` ? — Hari Prasad, Apr 05 '16 at 10:06
Yes, I have just tried it with Number = '1234567' and it counted fine. But I want to use my strNumbers array instead of static as the user could enter any amount of numbers. — user6097989, Apr 05 '16 at 10:12
Side note: you're open to SQL Injection. You should be using parameterized queries. — Clockwork-Muse, Apr 05 '16 at 14:27

Magnus Wallström · Accepted Answer · 2016-04-05T10:42:19.717

0

Try changing

WHERE NUMBER = '" + strNumbers + "' AND

with

WHERE NUMBER IN (" + strNumbers + ") AND

Make sure that strNumbers is comma separated. Something like this '12345', 12346', '12347' or 12345,12346,12347 depending on the datatype of the field NUMBERS.

edited Apr 05 '16 at 10:42

answered Apr 05 '16 at 10:34

Magnus Wallström

1,469
4
15
23

Hi, Ive added a comma to my array var strNewResult = string.Join(",", strNumbers); and the Count works. Thank You! :) – user6097989 Apr 05 '16 at 10:52
Then I think that you could write something like this... SELECT NUMBER, COUNT(*) FROM TABLENAME WHERE NUMBER IN ('1234567', '9876543') GROUP BY NUMBER – Magnus Wallström Apr 05 '16 at 13:42
This works fine when I do the query directly into the database but from my VS, it only takes one count and doesn't seem to even register the 2nd. – user6097989 Apr 05 '16 at 14:20
Problem is that you are using cmdSearchTable.ExecuteScalar() when I believe that you should use ExecuteReader() which will return a recordset with as many lines as there are unique NUMBERs. Good luck! – Magnus Wallström Apr 05 '16 at 15:02

Select Count query in DB2 with Array

1 Answers1