Call mysql stored procedures parameter from C#

Vote count:

0

i have make store procedure for my counting purpose in mysql database . but when i rus this it count all the rows that inside the table.beside of that i want to count only today entries. and call my procedure from C# with submittaimestamp value equal to my form datetimepicker. what are the modification should i do ? when i run this it giving me error like

"incorrect number of arguments for procedure wartif.allcounter; expected 0, got 1"

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `allcounter`()
BEGIN
SELECT username, COUNT(IF(status='hold',1,null)) 'hold',
       COUNT(IF(status='complete',1,null)) 'complete',
       COUNT(IF(loantype='tls',1,null)) 'tls',
       COUNT(IF(loantype='rtf',1,null)) 'rtf',
       COUNT(IF(loantype='ktl',1,null)) 'ktl',
       COUNT(IF(loantype='con',1,null)) 'con',
       COUNT(IF(status='hold',1,null)) 'hold',
       COUNT(IF(status='complete',1,null)) 'complete',
       COUNT(IF(status='route',1,null)) 'route',
       COUNT(IF(status='route(*)',1,null)) 'route(*)',
       COUNT(IF(neworsecond='new',1,null)) 'new', 
       COUNT(IF(neworsecond='Second',1,null)) 'Second'
FROM loans
WHERE DATE(submittimestamp) = submitdate
group by username;

   END

this is how i tried t call it from my application

MySqlCommand cmdwaqDatabase = new MySqlCommand("call allcounter('submitdate')", conwaqDatabase);

DateTime dateTimeValue = tdtp.Value;
cmdwaqDatabase.Parameters.AddWithValue("submitdate", dateTimeValue);

edited 2 mins ago

Dave Zych

9,088

asked 5 mins ago

charith

8

1 Answer

Vote count:

1

You didn't specify any parameters for your sproc, so

WHERE DATE(submittimestamp) = submitdate
                              ^^^^^^^^^^

is undefined within the function (unless you actually have a field named submitdate).

The definition should be more like

CREATE DEFINER=`root`@`localhost` PROCEDURE `allcounter`(IN submitdate DATE)
                                                         ^^^^^^^^^^^^^^^^^^

Since you didn't define any paramters, you're getting an exactly correct error message - the function is defined with ZERO parameters, and you're trying to pass in ONE.

answered 1 min ago

Marc B

202k

Call mysql stored procedures parameter from C#