Passing C# data to MySQL

Question

I am trying to pass data from some textboxes in C# to a MySQL database, but everytime I click submit from my C# application, the row creates empty spaces, as if my textboxes were empty.

public void Insert()
{
    string query = "insert into albertolpz94.miembros(usuario,contraseña,permisos,nombre,apellidos) values('" + userAdmin.registroUsuario.Text + "','" + userAdmin.registroContraseña.Text + "','" + userAdmin.registropermisos2 + "','" + userAdmin.registroNombre.Text + "','" + userAdmin.registroApellidos.Text + "');";
    //open connection
    if (this.OpenConnection() == true)
    {
        //create command and assign the query and connection from the constructor
        MySqlCommand cmd = new MySqlCommand(query, connection);

        //Execute command
         cmd.ExecuteNonQuery();

        //close connection
        this.CloseConnection();
    }
}

registroUsuario for example is a public textbox in my userAdmin class, the database is named "albertolpz94" and the table is "miembros". I would like to know why is this happening or how to use parameters (as someone suggested) as I am new to Mysql > C#

Try to use parametrized query - http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp — Valentin, Mar 19 '16 at 19:26
So? Plug in a debugger, set a breakpoint and look at the SQL you so clumsily are putting together (instead of using parameters). — TomTom, Mar 19 '16 at 19:26
What do you mean with _the row creates empty spaces_? There is a new row in your table and this row has no data? — Steve, Mar 19 '16 at 19:28
Yes, that what I mean. It creates a new row in the table, and values are empty. (Even though I set those to be NotNull) — Alberto Lopez Rodriguez, Mar 19 '16 at 19:28
I'm baffled by how you can have a row of empty values with a NOT NULL constraint on the columns... I know MySQL's awfully quirky, but that's ridiculous. — jleach, Mar 19 '16 at 20:23

jleach · Accepted Answer · 2016-03-19T20:18:33.360

First off, know that the connection and command variables need to be properly disposed of. We ensure this by using a using block like so:

using (MySqlConnection conn = new MySqlConnection())
    using (MySqlCommand cmd = new MySqlCommand()) {

    // ensure your connection is set
    conn.ConnectionString = "your connection string";
    cmd.Connection = conn;

}

Then, work up your CommandText using @ParamName to denote parameters:

using (MySqlConnection conn = new MySqlConnection())
    using (MySqlCommand cmd = new MySqlCommand()) {

    // ensure your connection is set
    conn.ConnectionString = "your connection string";
    cmd.Connection = conn;

    cmd.CommandText = "INSERT INTO ThisTable (Field1, Field2) VALUES (@Param1, @Param2);";

}

Next, use cmd.Parameters.AddWithValue() to insert your values. This should be done so the framework can avoid SQL Injection attacks (Rustam's answer is very dangerous, as is the way you were doing it):

using (MySqlConnection conn = new MySqlConnection())
    using (MySqlCommand cmd = new MySqlCommand()) {

    // ensure your connection is set
    conn.ConnectionString = "your connection string";
    cmd.Connection = conn;

    cmd.CommandText = "INSERT INTO ThisTable (Field1, Field2) VALUES (@Param1, @Param2);";

    cmd.Parameters.AddWithValue("@Param1", userAdmin.YourProperty);
    cmd.Parameters.AddWithValue("@Param2", userAdmin.YourOtherProperty);

}

and finally, you can then open the connection and execute it:

using (MySqlConnection conn = new MySqlConnection())
    using (MySqlCommand cmd = new MySqlCommand()) {

    // ensure your connection is set
    conn.ConnectionString = "your connection string";
    cmd.Connection = conn;

    cmd.CommandText = "INSERT INTO ThisTable (Field1, Field2) VALUES (@Param1, @Param2);";

    cmd.Parameters.AddWithValue("@Param1", userAdmin.YourProperty);
    cmd.Parameters.AddWithValue("@Param2", userAdmin.YourOtherProperty);


    cmd.Connection.Open();
    cmd.ExecuteNonQuery();
    cmd.Connection.Close();
}

And finally, realize that when these command and connection variables exit the using block, they will be properly disposed of. The using block ensures proper disposal regardless of any exceptions that might be thrown or whether we forget to close/dispose ourselves.

Now, for helpful debugging, wrap the core execution and connection opening in a try/catch block, and write the exception to the output window if there is one:

using (MySqlConnection conn = new MySqlConnection())
    using (MySqlCommand cmd = new MySqlCommand()) {

    // ensure your connection is set
    conn.ConnectionString = "your connection string";
    cmd.Connection = conn;

    cmd.CommandText = "INSERT INTO ThisTable (Field1, Field2) VALUES (@Param1, @Param2);";

    cmd.Parameters.AddWithValue("@Param1", userAdmin.YourProperty);
    cmd.Parameters.AddWithValue("@Param2", userAdmin.YourOtherProperty);

    try {
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
        cmd.Connection.Close();
    } catch (Exception e) {
        System.Diagnostics.Debug.WriteLine("EXCEPTION: " + e.ToString());
    }
}

At this point you set a breakpoint on the exception catching and check your locals window to see what your state is... in locals, you should see your cmd variable... expand it, look for the Parameters collection, expand that, check the count and values, etc, as shown:

If seting my parameters to userAdmin.textBox 1 , this is what my database receives: "System.Windows.Forms.TextBox, Text:" , if setting my parameters to userAdmin.textBox1.text, I get blank spaces — Alberto Lopez Rodriguez, Mar 19 '16 at 20:14
In this case it sounds like a binding problem with the textbox. Your requesting the value of the textbox itself appears to be returning null or an empty string... can you confirm this? (e.g., before you start doing any of the db work, throw a breakpoint and use the immediate window to check, or output the values to the output window as described in the catch block of my answer) — jleach, Mar 19 '16 at 20:20

score 0 · Answer 2 · answered Mar 19 '16 at 19:47

The best option for you is to debug and find out if the input reaches the query section. Likewise, make sure you OPEN the connection before executing the query. It is not the best practice to mention input variables like you are doing. Instead, try this:

public void Insert()
    {
        string query = "insert into albertolpz94.miembros(usuario,contraseña,permisos,nombre,apellidos) values('{0}','{1}','{2}','{3}','{4}')", userAdmin.registroUsuario, userAdmin.registroContraseña, userAdmin.registropermisos2, userAdmin.registroNombre, userAdmin.registroApellidos;
        //DOUBLECHECK IF YOU REALLY OPENED A CONNECTION

        if (this.OpenConnection() == true)
        {
            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Execute command
             cmd.ExecuteNonQuery();

            //close connection
            this.CloseConnection();
        }
    }

Also, check the validity of your connection

Sorry, but the downvote is for suggesting to put non-parameterized formatted strings into a querystring, and not properly ensuring connection disposal. — jleach, Mar 19 '16 at 19:59

Passing C# data to MySQL

2 Answers2