MySQLDataReader not reading C#

Question

The Stored Procedure:

BEGIN
SELECT * FROM `user` WHERE `EmailAddress`= @p0 AND `Password` = @p1;
END

Where @p0 and @p1 are Varchar(100).

And the code:

using (MySqlConnection con = new MySqlConnection(Database.MySQLConstring))
{
    using (MySqlCommand cmd = new MySqlCommand("LoginCheck", con))
    {
        cmd.CommandType = CommandType.StoredProcedure; //"LoginCheck"
        cmd.Parameters.AddWithValue("email", Email);
        cmd.Parameters.AddWithValue("password", Pword);

        con.Open();

        MySqlDataReader reader = cmd.ExecuteReader();
        UserModel UM = new UserModel();
        While (reader.Read())
        {
            UM.UserId = (int)reader["UserID"];
            UM.DisplayName = (string)reader["DisplayName"];
            UM.Moderator = (int)reader["Moderator"];
        }

        con.Close();
        While(!string.IsNullOrEmpty(UM.DisplayName) && UM.UserId != 0)
        {
            Result = 1;
            return UM;
        }

        Result = -1;
        return UM;
    }
}

The code runs succesfull until it comes to the while(reader.Read()) part, then it skips it and goes to the con.close(). No errors or exceptions are thrown. It worked when I was using it when everything was SQL and not MySQL ,but I need to get it working in MySQL.

When I run the stored Procedure itself in de databse then I get my result that I need. but when I use the code it will skip the While part of the code.

@EhsanSajjad Im using PHPMyAdmin, it works when I run it there. — MisterMan, Dec 27 '18 at 15:36
Are you certain that it's the order of adding parameters that matters and not their names? You seem to have called your parameters `@p0` and `@p1` in the definition of the stored procedure, and then added them in c# as @`email` and @`password`. I would try changing the stored procedure to accept @email and @password instead of @p0 and @p1, and see if the c# works out. You might also need @ in the C# names — Caius Jard, Dec 27 '18 at 15:41
ps, you said *The code runs succesfull until it comes to the while(reader.Read())* -> there is no such part in your code — Caius Jard, Dec 27 '18 at 15:49
@CaiusJard I understand your statement, but at first I had the names of my parameter the same as how i named them in the code, but it didnt worked. Then I found here on Stack overflow someone who had them declared as '@p0' even when in there it was declared different and it worked for him, I tried it to and it worked for me to. Thats why the names in the code and the SP are different. I tested again to change the names so they match like you suggested, but it still skips the While loop — MisterMan, Dec 27 '18 at 15:51
@CaiusJard You're right, my bad. Before I posted my question I tried the if statement, from a suggestion but that didnt worked either. I changed it back to the while — MisterMan, Dec 27 '18 at 15:54

Caius Jard · Accepted Answer · 2018-12-27T16:01:14.607

If I were going to execute a procedure that was declared as:

CREATE PROCEDURE x(em VARCHAR, pw VARCHAR) --declaration of parameters
BEGIN
  SELECT * FROM `user` WHERE `EmailAddress`= em AND `Password` = pw;
END

I would ensure the parameters in my C# code were named the same as in the declaration of the stored procedure:

cmd.Parameters.Add(new MySqlParameter("em", Email));
cmd.Parameters.Add(new MySqlParameter("pw", Password));

I suspect that your query is not getting any rows, because the values you set never make it into the parameters, and are hence never used to query. reader.Read() returns false, because there are no rows. Also make sure that the values for EMail and Password that you're querying really do exist in the table

Try as a debugging thing, to make your query like:

SELECT UserID, DisplayName, Moderator FROM `user` WHERE `EmailAddress`= @p0 AND `Password` = @p1 
UNION ALL 
SELECT 0, CONCAT_WS(' ', 'There is no user with email/password of', @p0, '/', @p1), '' FROM DUAL;

Or however your parameters are named now..

Your reader.Read()should now return true because this query should always return a row, so inspect the value of DisplayName, it should tell what search terms were applied

I did that in the beginning but that doesnt do the trick either. Then I tried the '@p0' and '@p1' names because they are a reference to the first and second parameter you declare of the created stored procedure. Atleast thats what I red on a solution from Stack overflow. — MisterMan, Dec 27 '18 at 15:58
I have rewritten my stored procedure and changed my parameter names to something more clear as suggested, and my reader works again. It was probably the problem that the parameters didn't arrived to my stored procedure. — MisterMan, Dec 27 '18 at 16:20

MySQLDataReader not reading C#

1 Answers1