Alexander Alexander - 1 month ago 8
SQL Question

how to handle exception when a field in database does not allow nulls

I have a field in the database that is nvarchar and it cannot be null.
I am having a problem to display the specific message when an error occurs.
When inserting in my database,I tried two things.
first:

if (string.IsNullOrWhiteSpace(textBoxCity.Text))
MessageBox.Show("This cannot be empty");
else
cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = textBoxCity.Text;


second:

try
{
if(string.IsNullOrWhiteSpace(textBoxCity.Text))
{
MessageBox.Show("Cannot be empty");

}
else
{
cmd.Parameters.Add("@city", SqlDbType.Int).Value = textBoxCity.Text;
}
}
catch (Exception ex)
{
if (ex is FormatException)
{
MessageBox.Show("Cannot be empty");
return;
}
else if (ex is SqlException)
{
MessageBox.Show("Cannot be empty");
}
throw;
}


the second one gives me the right message but it also gives me an exception where it says the scalar must be declared.how can I handle that?
I've tried to give it a db.null,but because a field does not allow nulls,the it gives me another exception,again that is not format,or sql.Can you tell me what kind of exception is this,or how can I handle this?

Answer

If the value is required but not supplied, you shouldn't try to insert it into the database anyway - of course that will cause an exception.

bool valid = true;
if (string.IsNullOrWhiteSpace(textBoxCity.Text))
{
     valid = false;
     MessageBox.Show("This cannot be empty");
}

if(valid)
{
    cmd.Parameters.Add("@city", SqlDbType.Int).Value = textBoxCity.Text;
    //execute sql query here
}

As the other answer said, you should parse the text into an int as well.