mysql command executescalar returns null in C#

Question

Let's say I have a query result that looks as follows:

ID    NAME    Phone
----  ----    -----
1     John    123456
2     John    125678
3     John    345678
4     Abby    456789
5     Abby    567890

I want to return just a single row instance of name: John, where the phone number like '12%'.

In c#, I wrote this syntax to get the PersonName variable as the result of the query.

MySqlConnection connection = new MySqlConnection("SERVER=" + "localhost" + ";" + "DATABASE=" + "testdb" + ";" + "UID=" + "root" + ";" + "PASSWORD=" + "" + ";");
MySqlCommand command = new MySqlCommand();    

    connection.Open();
    string selectQuery = "SELECT NAME FROM testtable WHERE Phone LIKE '12%' ORDER BY ID LIMIT 1";
    command.Connection = connection;
    command.CommandText = selectQuery;
    string PersonName = (string)command.ExecuteScalar();
    connection.Close();

I don't know whats wrong with my code but the PersonName returns null. What did I do wrong?

When you run the selectQuery against the db directly (not via the code you show) what are the results? — Eric Walker, Jul 09 '15 at 16:10
Also: don't forget to use try/finally or using blocks to wrap your connection object, or you could end up locking yourself out of the database. — Joel Coehoorn, Jul 09 '15 at 16:13
its a number column. when I run the query against the db directly it returns John, like I was expected. I did insert table query with the similar order of command initiation. I couldn't find the error of my code. — rfa, Jul 09 '15 at 16:22
If you're getting a scalar value back when running the query directly, the error must be elsewhere. Can you add additional code to your question showing where you instantiate connection and command? — Eric Walker, Jul 09 '15 at 16:28
Thanks for the additional detail @rfa, just a quick question and then I have one last thing to try. Are your database and table both named testtable? — Eric Walker, Jul 10 '15 at 13:25
the db name is testdb and the table name is testtable. thanks — rfa, Jul 12 '15 at 01:33

score 1 · Answer 1 · answered Jul 13 '15 at 16:15

We have to be missing something else here. Try the following code sample based on what you provided:

try {
MySqlConnection connection = new MySqlConnection("SERVER=localhost;DATABASE=testdb;UID=root;PASSWORD=;");
MySqlCommand command = new MySqlCommand();    

connection.Open();
string selectQuery = "SELECT NAME FROM testtable WHERE Phone LIKE '12%' ORDER BY ID LIMIT 1";
command.Connection = connection;
command.CommandText = selectQuery;
string PersonName = (string)command.ExecuteScalar();
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
finally {
    connection.Close();
}

I have a feeling that for some reason the call to .Open() is failing and the error is being swallowed elsewhere. Try the above and let me know what you find out.

score 0 · Answer 2 · answered Apr 09 '21 at 21:00

do this : change this (string)command.ExecuteScalar(); by Convert.ToString(command.ExecuteScalar());

MySqlConnection connection = new MySqlConnection("SERVER=" + "localhost" + ";" + "DATABASE=" + "testdb" + ";" + "UID=" + "root" + ";" + "PASSWORD=" + "" + ";");

MySqlCommand command = new MySqlCommand();

connection.Open();
string selectQuery = "SELECT NAME FROM testtable WHERE Phone LIKE '12%' ORDER BY ID LIMIT 1";
command.Connection = connection;
command.CommandText = selectQuery;
string PersonName = Convert.ToString(command.ExecuteScalar());
connection.Close();

mysql command executescalar returns null in C#

2 Answers2