how to handle the parameterized query expects the parameter which was not supplied

Question

I am inserting data on a button click.I am trying to handle exceptions for the field.My code is:

try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "INSERT INTO omotnica (id,name,date,number)" +
                                    "VALUES(@id,@naziv_predmeta,@date,@number)";

            cmd.Parameters.Add("@id", SqlDbType.Int).Value = idInt;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 300).Value = this.textBoxName.Text;
            try
            {
                DateTime datt = DateTime.Parse(textBoxDatumUpisa.Text);
                textBoxDatumUpisa.Text = datt.ToString("d/M/yyyy");
                cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = datt;
            }
            catch (FormatException)
            {
                MessageBox.Show("Date must be dd/MM/yyyy"); 
            }
            try
            {
                cmd.Parameters.Add("@number", SqlDbType.Int).Value = Convert.ToInt32(textBoxNumber.Text);

            }
            catch (SqlException e) when (e.Number == 8178)
            {
                MessageBox.Show("U must enter something");
            }
            catch (FormatException)
            {
                MessageBox.Show("It must be an integer");
            }

            con.Close();
             MessageBox.Show("Saved!");

        }
        catch (Exception ex)
        {
            MessageBox.Show(" error" + ex.Message + ex.Source );

        }try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "INSERT INTO omotnica (id,name,date,number)" +
                                    "VALUES(@id,@naziv_predmeta,@date,@number)";

            cmd.Parameters.Add("@id", SqlDbType.Int).Value = idInt;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar, 300).Value = this.textBoxName.Text;
            try
            {
                DateTime datt = DateTime.Parse(textBoxDatumUpisa.Text);
                textBoxDatumUpisa.Text = datt.ToString("d/M/yyyy");
                cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = datt;
            }
            catch (FormatException)
            {
                MessageBox.Show("Date must be dd/MM/yyyy"); 
            }
            try
            {
                cmd.Parameters.Add("@number", SqlDbType.Int).Value = Convert.ToInt32(textBoxNumber.Text);

            }
            catch (SqlException e) when (e.Number == 8178)
            {
                MessageBox.Show("U must enter something");
            }
            catch (FormatException)
            {
                MessageBox.Show("It must be an integer");
            }

            con.Close();
             MessageBox.Show("Saved!");

        }
        catch (Exception ex)
        {
            MessageBox.Show(" error" + ex.Message + ex.Source );

        }

When I enter some string in the textBoxNumber,it fires up a FormatException and also an exception he parameterized query expects the parameter which was not supplied.When I leave the textBox blank it fires the parameterized query expects the parameter which was not supplied. How to change that?Didn't I cover that exception with the SQLException catch? I need it to fire a message box when it's blank or it's not integers inside the textbox.

Answer 1

You can safely try to convert without getting an exception like this

int num;
if (!Int32.TryParse(textBoxNumber.Text, out num)) {
    MessageBox.Show("It must be an integer");
    return;
}

And for text columns that may be empty

object textValue = String.IsNullOrWhiteSpace(textBoxName.Text)
    ? (object)textBoxName.Text
    : (object)DBNull.Value;

Note that you have to pass DBNull.Value and not just null as parameter, otherwise the parameter will be treated as missing.

If you want to show an error message instead:

if(String.IsNullOrWhiteSpace(textBoxName.Text)) {
    MessageBox.Show("It must be an integer");
    return;
}

---

But the code gets very messy, if you try to convert textbox values, show error messages and construct an SQL statement at the same time.

Rather create a class for the values

class Omotnica 
{
    public int ID { get; set; }
    public string Name { get; set; }
    public DateTime Date { get; set; }
    public int Number { get; set; }
}

and in a frist step get the values into this class. This can also be done with object data binding. this does all the conversions for you!

Then setting up the SQL command will be much easier.

If you still want to handle the textbox values manually, you could create a method for this

private Omotnica GetValues()
{
    var o = new Omotnica();
    if (!Int32.TryParse(textBoxNumber.Text, out o.Number)) {
        return null;
    }

    if(String.IsNullOrWhiteSpace(textBoxName.Text)) {
        return null;
    }
    o.Name = textBoxName.Text;

    ... and so on
}

then you can clean up the SQL part like this

Omotnica o = GetValues();
if (o == null) {
    MessageBox.Show("Please enter something for number, name, ...");
} else {
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "INSERT INTO omotnica (id,name,date,number) VALUES (@id,...";
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = o.ID;
    cmd.Parameters.Add("@name", SqlDbType.NVarChar, 300).Value = o.Name;
    ...
}

Looks much tidier, doesn't it?

Answer 2

Just add: if(textbox.Text == string.Empty) return; at the start of the Click event so this doesn't happen. Instead of catching so many exeptions and showing a messageBox add ifconditions and only handle exepctions that are supposed to happen.

And if you want to recive a int input use a numbericUpDown

Answer 3

Convert.ToInt32(String.Empty) will raise a FormatException because it cannot parse an empty string to a Int32 Value.

Converting an invalid string to Int32 will raise a FormatException but also a SQLException because the conversion fails and also the parameterized query, because it expects Int32.

You can catch System.Exception and switch on possible errors, instead of trying to catch two exceptions:

catch (Exception ex)            
{                
    if (ex is FormatException)
    {
        if (string.IsNullOrEmpty(this.textboxName.Text){ 
            MessageBox.Show("U must enter something");
        } else {
            MessageBox.Show("It must be an integer");
        }
        return; 
     } 
     else if (ex is SqlException) {
        MessageBox.Show(ex.Message);
        return;
     }
   throw;
}