C# and MySql Procedure

Question

I have 2 tables. Main_items and Help_items.

Main_items has these columns
(main_items_id,main_items_name)

Help_items has these columns
(help_items_id,Help_items_name, main_items_id).

I wrote this Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `thamer1`(in main_items_id_ int, 
out res int)
BEGIN
declare a int;
declare b int;

select count(help_items_id)
into a from help_items
where main_items_id=main_items_id_;

if a=0 then
    set b=(main_items_id_*10)+1;
    set res=b;
else
    select COALESCE(max(help_items_id),0)+1
    into res
    from help_items
    where main_items_id=main_items_id_;
end if;
END

This procedure works with MySql WrokBench.

And this for c# code

private void a_KeyDown(object sender, KeyEventArgs e)
    {
        using (MySqlConnection mysqlcon6 = new 
MySqlConnection(connectString))
        {
            mysqlcon6.Open();
            MySqlCommand mysqlcmd6 = new MySqlCommand("thamer1", mysqlcon6);

            mysqlcmd6.CommandType = CommandType.StoredProcedure;
            mysqlcmd6.CommandText = "thamer1";
            mysqlcmd6.Parameters.Add("@main_items_id_", MySqlDbType.Int32).Value = a.Text;
            mysqlcmd6.Parameters.Add("@res", MySqlDbType.Int32).Value=HITEM.Text;
            mysqlcmd6.ExecuteNonQuery();
           // MessageBox.Show("saved");
            // GridFill();
        }
    }

I select value (for main_items_id) from DataGrideView and fetch it into textbox named a.

When I press ENTER I get this Message

System.FormatException:' Input string was not in a correct format'

I hope to help me to solve this error.

Answer 1

Remove the portion of this line that sets the parameter value:

mysqlcmd6.Parameters.Add("@res", MySqlDbType.Int32).Value=HITEM.Text;

It looks like you expect that to bind the result of @res to the HITEM textbox, and that's not what happens. HITEM.Text is just a string, and when you assign that value to an int parameter, you're telling MySql you expect it to be able to parse that string into an int.

Instead, only create the parameter, like this:

mysqlcmd6.Parameters.Add("@res", MySqlDbType.Int32);

You also need to tell ADO.Net this is an OUTPUT parameter. Then check the parameter value after the query runs by assigning the parameter value to HITEM.Text rather than from HITEM.Text:

private void a_KeyDown(object sender, KeyEventArgs e)
{
    //You can re-use the *names* of these variables, since their scopes are limited to the method
    //You can also stack them to share the same scope block and reduce nesting/indentation
    using (var con = new MySqlConnection(connectString))
    using (var cmd = new MySqlCommand("thamer1", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        //  mysqlcmd6.CommandText = "thamer1"; //you already did this in constructor. Don't need to do it again
        cmd.Parameters.Add("@main_items_id_", MySqlDbType.Int32).Value = a.Text;
        //DON'T assign to the Value, but DO make sure ADO.Net understands this is an OUTPUT parameter
        cmd.Parameters.Add("@res", MySqlDbType.Int32).Direction = ParameterDirection.Output;

        //wait as long as possible to call Open()
        con.Open();
        cmd.ExecuteNonQuery();

        //Now you can assign **to** HITEM.Text, rather than from it.
        HITEM.Text = cmd.Parameters["@res"].Value;
    }
    //End the scope as soon as possible, so the connection can be disposed faster
    // MessageBox.Show("saved");
    // GridFill();
}

And here it is again without all the extra comments:

private void a_KeyDown(object sender, KeyEventArgs e)
{
    using (var con = new MySqlConnection(connectString))
    using (var cmd = new MySqlCommand("thamer1", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@main_items_id_", MySqlDbType.Int32).Value = a.Text;
        cmd.Parameters.Add("@res", MySqlDbType.Int32).Direction = ParameterDirection.Output;

        con.Open();
        cmd.ExecuteNonQuery();
        HITEM.Text = cmd.Parameters["@res"].Value;
    }
}

Even better practice would move all your SQL methods to a separate class, away from your event handlers. The event handlers should only need to call methods in the new class, like this:

public static class DB
{
    private static string connectionString = "...";

    public static int thamer(int main_item_id)
    {
        using (var con = new MySqlConnection(connectString))
        using (var cmd = new MySqlCommand("thamer1", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@main_items_id_", MySqlDbType.Int32).Value = main_item_id;
            cmd.Parameters.Add("@res", MySqlDbType.Int32).Direction = ParameterDirection.Output;

            con.Open();
            cmd.ExecuteNonQuery();
            return (int)cmd.Parameters["@res"].Value;
        }
    }
}

private void a_KeyDown(object sender, KeyEventArgs e)
{
    HITEM.Text = DB.thamer(int.Parse(a.Text)).ToString();
}

Answer 2

Change this

 mysqlcmd6.Parameters.Add("@main_items_id_", 
MySqlDbType.Int32).Value = a.Text;
            mysqlcmd6.Parameters.Add("@res", MySqlDbType.Int32).Value = 
HITEM.Text;

to

int value1 = 0;
int value2 = 0;

if (!Int32.Text.TryParse(a.Text) || !Int32.TryParse(HITEM.Text))
{
    return;
}
 mysqlcmd6.Parameters.Add("@main_items_id_",  MySqlDbType.Int32).Value =  value1;
 mysqlcmd6.Parameters.Add("@res", MySqlDbType.Int32).Value = value2;