Ismail Gunes Ismail Gunes - 2 months ago 9
MySQL Question

MySqlCommand: no rows returned

I have a database created in a server and I added a row by MySql query browser for testing. This row is visible either with PhpMyAdmin or MySql query browser.

But when I want to reach this table within my program it says me there is no rows (

reader.HasRows = false
)

cs is the connection string in
PublicVariables
class

Here is the code

public static int checkuser(string myuser, string mypass)
{
try
{
using (MySqlConnection conn = new MySqlConnection(PublicVariables.cs))
{
string MypassMd5 = MakeMD5(mypass);
conn.Open();

if (conn == null)
Environment.Exit(0);

using (MySqlCommand cmd =
new MySqlCommand("SELECT username, password " + "FROM Users WHERE username = 'myuser'" ,conn))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
//DateTime mytime = DateTime.Now ;
if (reader.HasRows)
{
if (Convert.ToString(reader["password"]) != MypassMd5)
{
reader.Close();
conn.Close();
return -1;
}
else
{
PublicVariables.UserId = Convert.ToString(reader["username"]);
PublicVariables.UserDegre = Convert.ToInt16(reader["userdegre"]);
conn.Close();
reader.Close();
return 1;
}
}
else
{
reader.Close();
conn.Close();
return 2;
}
}
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.ToString());
}

return 0;
}


What's wrong in my code?

Answer

Well the primary error is in your command string , myuser is a variable and you cannot pass its value putting the variable name inside quotes.

new MySqlCommand("SELECT username, password FROM Users WHERE username = 'myuser'" ,conn)

instead this line should be converted to use a parameterized query

string commandText = "SELECT username, password, userdegre FROM Users WHERE username = @uname"; 
using (MySqlCommand cmd = new MySqlCommand(commandText ,conn)
{
    cmd.Parameters.AddWithValue("@uname", myuser);
    ....

Looking at your code you have another error after this. You try to read the field userdegre, but this field is not retrieved by your query, so you need to add it to the list of retrieved fields.
But the only field you really need to know is userdegre because you already know the username and the password, so you could remove the datareader and use ExecuteScalar and pass the username and the password as parameters for the WHERE clause. If you get anything in return then you are sure that your user is authenticated by the database.

string commandText = "SELECT userdegre FROM Users WHERE username = @uname AND Password =@pwd";
using(MySqlCommand cmd = new MySqlCommand( commandText ,conn))
{                        
    cmd.Parameters.AddWithValue("@uname", myuser);
    cmd.Parameters.AddWithValue("@pwd", MypassMd5);
    var result = cmd.ExecuteScalar();
    if(result != null)
    {
          PublicVariables.UserId = myuser;
          PublicVariables.UserDegre = result.ToString();
    }
}