CODEWITHSUNDEEP
c#mysqlparameter-passingcall
rafalefighter
rafalefighter

Reputation: 734

Call mysql stored procedures parameter

I develop store procedure for my counting purpose in MySQL database. When I run this, it counts all the rows that inside the table. I want to count only today entries. And call my procedure from C# with submittaimestamp value equal to my form datetime picker value. What modification should I do? When I run this it giving me error:

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);

Upvotes: 0

Views: 769

Answers (1)

Marc B
Marc B

Reputation: 360572

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.

Upvotes: 1

Related Questions