command.parameters.add not working - mysql C#

Question

Using xaml/wpf in C# with MySQL; VS 2013; up-to-date mysql Net Connector (downloaded 3 days ago)

I've realize there's a lot of threads related to my question; but I have yet to find an answer. ... and I'm about to pull my hair out... I get a general exception error:

A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in System.Data.dll

On this line: adapter.Update(prodTable);

In this function:

private void UpdateDBValues()
        {
            MySqlConnection connection = new MySqlConnection(connString);
            MySqlDataAdapter adapter = new MySqlDataAdapter();

            //Get our current time in our desired string format( ##:## AM/PM)
            DateTime time = DateTime.Now;             // Use current time
            string format = "h:mm tt";            // Use this format
            string timeStr = time.ToString(format);

            //UPDATE
            string updateString = "UPDATE gas_production " +
              "SET well_id=?well_id, entry_date=?entry_date, status=?status, " +
              "event=?event, hours_down=?hours_down, time=?time, mcf=?mcf,static_psi=?static_psi, " +
              "line_psi=?line_psi, tbg_psi=?tbg_psi, csg_psi=?csg_psi, spm=?spm, cycle=?cycle," +
              "water=?water, water_line_psi=?water_line_psi, water_meter=?water_meter, comments=?comments" +
              "WHERE entry_id=?oldentry_id";

            MySqlCommand updateCommand = new MySqlCommand(updateString, connection);
            updateCommand.Parameters.Add("?well_id", MySqlDbType.VarChar, 10, "well_id");
            updateCommand.Parameters.Add("?entry_date", MySqlDbType.VarChar, 10, "entry_date");
            updateCommand.Parameters.Add("?status", MySqlDbType.VarChar, 20, "status");
            updateCommand.Parameters.Add("?event", MySqlDbType.VarChar, 50, "event");
            updateCommand.Parameters.Add("?hours_down", MySqlDbType.Int32, 11, "hours_down");
            updateCommand.Parameters.Add("?time", MySqlDbType.VarChar, 8, "time");
            updateCommand.Parameters.Add("?mcf", MySqlDbType.Int32, 11, "mcf");
            updateCommand.Parameters.Add("?static_psi", MySqlDbType.Int32, 11, "static_psi");
            updateCommand.Parameters.Add("?line_psi", MySqlDbType.Int32, 11, "line_psi");
            updateCommand.Parameters.Add("?tbg_psi", MySqlDbType.Int32, 11, "tbg_psi");
            updateCommand.Parameters.Add("?csg_psi", MySqlDbType.Int32, 11, "csg_psi");
            updateCommand.Parameters.Add("?spm", MySqlDbType.Int32, 11, "spm");
            updateCommand.Parameters.Add("?cycle", MySqlDbType.Int32, 11, "cycle");
            updateCommand.Parameters.Add("?water", MySqlDbType.Int32, 11, "water");
            updateCommand.Parameters.Add("?water_line_psi", MySqlDbType.Int32, 11, "water_line_psi");
            updateCommand.Parameters.Add("?water_meter", MySqlDbType.Int32, 11, "water_meter");
            updateCommand.Parameters.Add("?comments", MySqlDbType.VarChar, 255, "comments");
            MySqlParameter parameter = updateCommand.Parameters.Add("?oldentry_id", MySqlDbType.Int32, 11, "entry_id");
            parameter.SourceVersion = DataRowVersion.Original;
            adapter.UpdateCommand = updateCommand;

            //INSERT

            string insertString = "INSERT INTO gas_production "+
                "(entry_id, well_id, entry_date, status, event, hours_down, time, mcf, static_psi" +
                "line_psi, tbg_psi, csg_psi, spm, cycle, water, water_line_psi, water_meter, comments) " +
                "VALUES " +
                "(?entry_id, ?well_id, ?entry_date, ?status, ?event, ?hours_down, ?time, ?mcf, ?static_psi " +
                "?line_psi, ?tbg_psi, ?csg_psi, ?spm, ?cycle, ?water, ?water_line_psi, ?water_meter, ?comments)";

            MySqlCommand insertCommand = new MySqlCommand(insertString, connection);
            insertCommand.Parameters.Add("?entry_id", MySqlDbType.Int32, 11, "entry_id");
            insertCommand.Parameters.Add("?well_id", MySqlDbType.VarChar, 10, "well_id");
            insertCommand.Parameters.Add("?entry_date", MySqlDbType.VarChar, 10, "entry_date");
            insertCommand.Parameters.Add("?status", MySqlDbType.VarChar, 20, "status");
            insertCommand.Parameters.Add("?event", MySqlDbType.VarChar, 50, "event");
            insertCommand.Parameters.Add("?hours_down", MySqlDbType.Int32, 11, "hours_down");
            insertCommand.Parameters.Add("?time", MySqlDbType.VarChar, 8, timeStr);
            insertCommand.Parameters.Add("?mcf", MySqlDbType.Int32, 11, "mcf");
            insertCommand.Parameters.Add("?static_psi", MySqlDbType.Int32, 11, "static_psi");
            insertCommand.Parameters.Add("?line_psi", MySqlDbType.Int32, 11, "line_psi");
            insertCommand.Parameters.Add("?tbg_psi", MySqlDbType.Int32, 11, "tbg_psi");
            insertCommand.Parameters.Add("?csg_psi", MySqlDbType.Int32, 11, "csg_psi");
            insertCommand.Parameters.Add("?spm", MySqlDbType.Int32, 11, "spm");
            insertCommand.Parameters.Add("?cycle", MySqlDbType.Int32, 11, "cycle");
            insertCommand.Parameters.Add("?water", MySqlDbType.Int32, 11, "water");
            insertCommand.Parameters.Add("?water_line_psi", MySqlDbType.Int32, 11, "water_line_psi");
            insertCommand.Parameters.Add("?water_meter", MySqlDbType.Int32, 11, "water_meter");
            insertCommand.Parameters.Add("?comments", MySqlDbType.VarChar, 255, "comments");
            adapter.InsertCommand = insertCommand;

            //DELETE
            MySqlCommand deleteCommand = new MySqlCommand("DELETE FROM gas_production WHERE entry_id=?entry_id", connection);
            MySqlParameter delParameter = deleteCommand.Parameters.Add("?entry_id", MySqlDbType.Int32, 10, "entry_id");
            delParameter.SourceVersion = DataRowVersion.Original;
            adapter.DeleteCommand = deleteCommand;

            //UPDATE ADAPTER
            DataTable prodTable = (DataTable)((DataSourceProvider)FindResource("ProdTable")).Data; //get DataTable
            adapter.Update(prodTable);//finally update
        }

I have retyped 3 times to make sure all information is there; tried AddWithValues, @ and ? sigils, made sure my DataTable was exactly like my MySQL table, verified the Bindings from xaml to c# (which shouldn't matter should they?)...what am I missing here? And/or what different approach can I take to make this work? I believe it is in the syntax of the parameters I'm passing, but I can't find anything wrong and it only throws that 1 error... nothing else.

If I need to provide more information, please let me know what you need.

A little guidance would be greatly, greatly appreciated!

EDIT: Btw, I'm following this tutorial

how are parameteres defined / declared in MySql are they the same as in Sql Server..? if so can you explain what the `?` in your param name means — MethodMan, Mar 12 '15 at 15:32
the ? is from the query string I'm passing to the connector. "well_id=?well_id" then pass that through the adaptor command: updateCommand.Parameters.Add("?well_id", MySqlDbType.VarChar, 10, "well_id"); the Parameters is the function used with the Adaptor. So the parameter isn't "defined" per-say, but it is part of the adapter command — J_Roel, Mar 12 '15 at 15:35
use the '@' prefix within the statement, in Parameters.add without the '@' — Thomas Krojer, Mar 12 '15 at 15:45
Like so: "well_id=@well_id" and updateCommand.Parameters.Add("well_id", MysqlDbType.VarChar,10,"well_id"); ...didn't work, same error — J_Roel, Mar 12 '15 at 15:52
What about that adaptor.update... could fill work there instead? I see there is 7 steps of execution on the update method... [link](https://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update(v=vs.110).aspx) is the adaptor.update required for what I'm trying to do? — J_Roel, Mar 12 '15 at 16:02

score 0 · Answer 1 · answered Mar 16 '15 at 14:40

After trying this multiple times, I decided to start from scratch using a different database. I found out that some of the names of the fields where not being accepted by the connector. So instead of using underscores, like water_line_psi I would recommend trying waterLinePSI, or similar, to avoid those complications. Again, I don't know if that is the exact problem, but I have it working now and that is the only thing I see different.

score 0 · Answer 2 · answered May 19 '20 at 12:40

0

I faced this error. It turns out I didn't declare the command type as stored Procedure. I added this line and it was fixed.

command.CommandType = CommandType.StoredProcedure;

answered May 19 '20 at 12:40

Pronoy999

627
7
20

command.parameters.add not working - mysql C#

2 Answers2