Unable to Get value from MySQL and print to TextBox

Question

So this method is supposed to get the ipaddress of the logged in user from a MySQL Database and print it to a textbox. However, I cant seem to get it right as the program just closes after I execute this method.

    public void readIPAddress()
    {
        string username = GlobalData._sharedUserName;
        String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
        conn = new MySql.Data.MySqlClient.MySqlConnection(connString);

        conn.Open();
        queryStr = "";
        queryStr = "SELECT ipaddress FROM webappdemo.userregistration WHERE username=?username";
        cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
        cmd.Parameters.AddWithValue("?username", username);
        cmd.ExecuteReader();

        while (cmd.ExecuteReader().Read())
        {
            textBoxIPAddress.Text = reader["ipaddress"].ToString();
        }

        conn.Close();
    }

If anyone could point out where I went wrong, I greatly appreciate your help!

Edit: After using try and catch I get this:

MySql.Data.MySqlClient.MySqlException (0x80004005): There is already an open DataReader associated with this Connection which must be closed first.
   at MySql.Data.MySqlClient.ExceptionInterceptor.Throw(Exception exception)
   at MySql.Data.MySqlClient.MySqlConnection.Throw(Exception ex)
   at MySql.Data.MySqlClient.MySqlCommand.CheckState()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at ConnectToDataBase.Form2.readIPAddress() in C:\Users\ee\Dropbox\ConnectToDataBase\ConnectToDataBase\Form2.cs:line 95

use try catch to trace the exception if any, Why are you executing the command two times using `ExecuteReader`? — sujith karivelil, Dec 16 '16 at 03:51
Suggestion: checkout [MySql.Data.MySqlClient.MySqlHelper](https://dev.mysql.com/doc/connector-net/en/connector-net-ref-mysqlclient-mysqlhelper.html) class. you may find it much easier to work with. — bansi, Dec 16 '16 at 03:55
Comment for the edit : this code won't compile as `reader` is not defined — sujith karivelil, Dec 16 '16 at 03:55
reader is defined outside of the method as MySql.Data.MySqlClient.MySqlDataReader reader; — Marvinatorrr, Dec 16 '16 at 03:57

score 1 · Accepted Answer · answered Dec 16 '16 at 04:27

Quick Fix:

You are executing the command two times, using ExecuteReader that's why you are getting such exception. If you execute the code like this means your code will works fine:

string queryStr = "SELECT ipaddress FROM webappdemo.userregistration WHERE username=@username";
using (MySqlConnection conn = new MySqlConnection(connString))
{
    conn.Open();
    using (MySqlCommand cmd = new MySqlCommand(queryStr, conn))
    {
        cmd.Parameters.AddWithValue("@username", username);
        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            textBoxIPAddress.Text = reader["ipaddress"].ToString();
        }
    }
}

Smart Fix:

Here you are fetching a single value from the database in such situations you need not to use reader at all. you can simply access those value by using ExecuteScalar() method, which will give you the required object. if so You can use the following code:

using(MySqlConnection conn = new MySqlConnection(connString))
{
    using(MySqlCommand cmd= new MySqlCommand(query, conn))
    {
        cmd.Parameters.Add("@username", username);
        conn.Open();
        object ipAddress= cmd.ExecuteScalar();
        if (ipAddress!= null) 
           textBoxIPAddress.Text = ipAddress.ToString();
        else
           textBoxIPAddress.Text = "No data found";
    }
}

Hope that you wont forget to add MySql.Data.MySqlClient; to the using section

score 0 · Answer 2 · answered Dec 16 '16 at 04:02

you are executing reader two times by calling ExecuteReader(), why you need Reader here, if you only need one value from database. use ExecuteScalar that will return first value of the first record from the result. Sample code:

try
{
    string query = "SELECT ipaddress FROM webappdemo.userregistration WHERE username = @username";
    string connString =ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
    using(MySqlConnection connection = new MySqlConnection(connString))
    {
        using(MySqlCommand command = new MySqlCommand(query, connection))
        {
            command.Parameters.Add("@username", username);

            connection.Open();
            object ip= command.ExecuteScalar();
            if (ip != null) {
              textBoxIPAddress.Text = ip.ToString();
            }
        }
    }
}
catch(MySqlException ex)
{
    // do something with the exception

}

score 0 · Answer 3 · answered Dec 16 '16 at 04:42

Problem:

    cmd.ExecuteReader(); //Executing reader and not assigning to anything

    while (cmd.ExecuteReader().Read()) //Executing reader again and not assigning to anything again
    {
        //There is nothing assigned to reader.
        textBoxIPAddress.Text = reader["ipaddress"].ToString(); 
    }

Quick Solution:

    //assuming reader is defined
    reader = cmd.ExecuteReader();

    while (reader.Read()) //read from the reader
    {
        textBoxIPAddress.Text = reader["ipaddress"].ToString(); 
    }

Alternative Solutions using MySql.Data.MySqlClient.MySqlHelper:

try {
    object ip = MySqlHelper.ExecuteScalar(connString, query, new MySqlParameter[] {
                                    new MySqlParameter("?username", username) 
                                }));
    if (ip != null) {
        textBoxIPAddress.Text = ip.ToString();
    }
} catch (Exception ex) {
    // do something with the exceptio
}

If you insist on using reader:

//assuming reader is defined
reader = MySqlHelper.ExecuteReader(connString, query, new MySqlParameter[] {
                                new MySqlParameter("?username", username) 
                            }));
while (reader.Read()) //read from the reader
{
    textBoxIPAddress.Text = reader["ipaddress"].ToString(); 
}

Note: the above code is just typed in here and may contain syntax errors. take this a a guideline.

Unable to Get value from MySQL and print to TextBox

3 Answers3