MySQL Parameter must be defined with C#

Question

I created a stored procedure on a MySQL database and it works fine if i run it directly from Navicat (a SGBD) but if i try to run the script of the stored procedure from C# code i get an error "Parameter '@req' must be defined". My code bellow:

DROP PROCEDURE IF EXISTS SP_GET_EXPERIENCE_RECORD_STATISTICS;

CREATE PROCEDURE SP_GET_EXPERIENCE_RECORD_STATISTICS(IN startTime  VARCHAR(19), IN endTime VARCHAR(19), IN isEquipment INT)
BEGIN
DECLARE req VARCHAR(1000);
SET @req = "SELECT ";

IF(isEquipment = 1) THEN
    SET @req = CONCAT(@req, "ClientName,");
ELSE
    SET @req = CONCAT(@req, "ContentName 'ClientName',");
END IF;

SET req = CONCAT(req, " COUNT(1) 'ConsumeCount',
SUM(Points) 'PointsCount',
SUM(CASE WHEN Mode = 1 THEN 1 ELSE 0 END) 'CardCount',
SUM(CASE WHEN Mode = 1 THEN Points ELSE 0 END) 'CardPoints',
SUM(CASE WHEN Mode = 0 THEN 1 ELSE 0 END) 'WeChatCount',
SUM(CASE WHEN Mode = 0 THEN Points ELSE 0 END) 'WeChatPoints'
FROM experiencerecord
WHERE EndTime BETWEEN '", startTime, "' AND '", endTime, "'");

IF (isEquipment = 1) THEN
    SET @req = CONCAT(@req, " GROUP BY ClientName");
ELSE
    SET req = CONCAT(req, " GROUP BY ContentName");
END IF;

PREPARE stmt FROM @req;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;

 -- CALL SP_GET_EXPERIENCE_RECORD_STATISTICS('2010-01-01 00:00:00', '2016-12-31 23:59:59', 0);
 -- CALL SP_GET_EXPERIENCE_RECORD_STATISTICS('2010-01-01 00:00:00', '2016-12-31 23:59:59', 1);

And my code in C# :

MySqlConnection conn = new MySqlConnection(Properties.Settings.Default.DatabaseConnectionString);

public bool mysql(string sql)
    {
        try
        {
            MySqlCommand comm = new MySqlCommand(sql, conn);
            MySqlDataAdapter mda = new MySqlDataAdapter(comm);
            DataSet ds = new DataSet();
            mda.Fill(ds);
            return true;
        }
        catch (Exception ex)
        {
            return false;
            throw ex;
        }

    }

Any idea where it come from will be very appreciated.

what is sql in your C# code

e4c5
– e4c5

2016-12-27 05:19:02 +00:00
Commented Dec 27, 2016 at 5:19 — e4c5
– e4c5, Commented Dec 27, 2016 at 5:19

huysentruitw · Accepted Answer · 2022-10-06 13:34:54Z

9

Finally i added AllowUserVariables=True; to the connectionString and now it works super fine.

edited Oct 6, 2022 at 13:34

huysentruitw

28.2k10 gold badges95 silver badges142 bronze badges

answered Dec 27, 2016 at 5:26

Hans

3921 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ali Rasheed · Accepted Answer · 2016-12-26 11:04:48Z

0

Seems a typo error

IF (isEquipment = 1) THEN
    SET @req = CONCAT(@req, " GROUP BY ClientName");
ELSE
    SET @req = CONCAT(@req, " GROUP BY ContentName"); //You forgot @req here!
END IF;

Moreover I don't see any AddWithParameter line in your C# code that should be there as @req is a parameter.

MySqlDataAdapter mda = new MySqlDataAdapter(comm);
mda.Parameters.AddWithValue("@req",value_goes_here);

edited Dec 26, 2016 at 11:04

answered Dec 26, 2016 at 10:59

Ali Rasheed

2,8152 gold badges23 silver badges35 bronze badges

2 Comments

Hans Over a year ago

I fixed the missing @req you found so it's better but I still have an error "Parameter '@req' must be defined". That's strange, req is just a variable, not a parameter...

Ali Rasheed Over a year ago

Try removing @ from all req after SET keyword.

Collectives™ on Stack Overflow

MySQL Parameter must be defined with C#

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related