Empty Textbox still saves data in SQL Server even columns in table are not allowed null

Question

I am facing this issue, when I click save button when all textboxes are empty it shows stars labels on all of them. When I fill last textbox leaving all others empty, it saves data into database with empty strings.

How can I handle this issue?

if (tbIDCardNum.Text.Trim() == "")
{
    lblStarIDCardNum.Visibility = Visibility.Visible;
}

if (tbFirstName.Text.Trim() == "")
{
    lblStarFirstName.Visibility = Visibility.Visible;
}

if (rbMale.IsChecked == false && rbFemale.IsChecked == false)
{
    lblStarGender.Visibility = Visibility.Visible;
}

if (tbDOB.Text == "")
{
    lblStarDOB.Visibility = Visibility.Visible;
}

if (tbDateOfJoining.Text == "")
{
    lblStarDOJ.Visibility = Visibility.Visible;
}

if (tbEducation.Text.Trim() == "")
{
    lblStarEducation.Visibility = Visibility.Visible;
}

if (tbCNIC.Text.Trim() == "")
{
    lblStarCNIC.Visibility = Visibility.Visible;
}

if (tbSalary.Text.Trim() == "")
{
    lblStarSalary.Visibility = Visibility.Visible;
}

if (tbAddress.Text.Trim() == "")
{
    lblStarAddress.Visibility = Visibility.Visible;
}

if (tbEmail.Text.Trim() == "")
{
    lblStarEmail.Visibility = Visibility.Visible;
}

if (tbContact1.Text.Trim() == "")
{
    lblStarContact.Visibility = Visibility.Visible;
}
else
{
    try
    {
        conn.Open();

        cmd.CommandText = "insert into teacher (tIDCardNum, tFirstName, tLastName,tGender, tDOB, tCNIC, tEducation, tSalary, tJoinedOn, tAddress, tEmail, tContact1, tContact2, tContact3,tStatus) values ('" + tbIDCardNum.Text.Trim() + "' , '" + tbFirstName.Text.Trim() + "' , '" + tbLastName.Text.Trim() + "' , '" + gender + "' , '" + tbDOB.Text + "', '" + tbCNIC.Text + "' , '" + tbEducation.Text + "' , '" + tbSalary.Text.Trim() + "' , '" + tbDateOfJoining.Text.Trim() + "' , '" + tbAddress.Text.Trim() + "', '" + tbEmail.Text + "' , '" + tbContact1.Text + "' , '" + tbContact2.Text + "' , '" + tbContact3.Text + "',1)";

        cmd.Connection = conn;
        cmd.ExecuteNonQuery();

        cmd.Clone();
        conn.Close();

        HideStars();
        Refresh();

        MessageBox.Show("Saved");
   }
   catch (Exception ex)
   {
       if (ex.Message.Contains("Violation of PRIMARY KEY constraint "))
       {
           conn.Close();
           MessageBox.Show(ex.Message);
       }
       else
       {
           MessageBox.Show(ex.Message);
       }
   }
}

empty doesn't mean "Null". sql null is "unknown". empty textbox is just a string with length 0 - that's not unknown. — Marc B, Jul 22 '15 at 18:25
How can I make textboxes send Null. Or check if textboxes are seding empty strings — Code_Lover, Jul 22 '15 at 18:28
`if (length(text_from_textbox) = 0) then sql_value = 'null'` — Marc B, Jul 22 '15 at 18:29
Prevent SQL injection, use CommandParameter instead of string concatenation. — Techie, Jul 22 '15 at 18:31
Why are you using star labels? Use data validation controls instead. If you want to check null, refer [this article](http://stackoverflow.com/questions/7553567/how-to-check-if-string-is-null) — Techie, Jul 22 '15 at 18:37

score 1 · Answer 1 · answered Jul 22 '15 at 19:09

As I'm understanding it, what appears to be the main problem is that you check all fields in different if statements, but only the last has an else. As I'm assuming from your post, this is your problem; you want every textbox to have a value, before you start inserting it in the database, right?

This is better explained if break up your code into something a bit more reusable, incidentally cleaning up stuff a bit as well.

First, start off by introducing a variable in your class that we can use to see if there are any empty fields:

private bool HasEmptyFields = false;

Next, let's create a simple helper that checks if the textbox is empty/null, updates the appropriate label's visiblity state and set 'HasEmptyFields' to true if it is indeed empty:

private void ValidateField(TextBox textBox, Label label) {

    // check if the textbox actually is null - or empty (""), which is a difference
    // the nifty helper string.IsNullOrEmpty() will help with that
    var fieldIsEmpty = string.IsNullOrEmpty(textBox.Text.Trim());

    // next, based on if the field is empty,  set the visibility of the label
    // don't worry, this is fancy syntax for a simple if...then...else
    label.Visibility = fieldIsEmpty ? Visibility.Visible : Visibility.Hidden;

    if (fieldIsEmpty) {
        // ONLY if this field is actually null, or empty, we make sure to 
        // inform the rest of the code this occ
        HasEmptyFields = true;
    }
}

With this in place, we can do something like:

ValidateField(tbIDCardNum, lblStarIDCardNum);
ValidateField(tbFirstName, lblStarFirstName);
// etc... continue doing this for all you fields

if (HasEmptyFields) {
    // ONLY if there is any field detected as being empty/null
    // we simply stop here (and skip the insert-into-db stuff)
    return;
} 

try 
{
    // if all fields indeed have a value, let's
    // continue with the insert-into-db stuff here

    conn.Open();
    ...
}

Now there are certainly ways to makes this even prettier. But this might help you a bit in the right direction. Also worth mentioning are some other comments, like preventing SQL injection (which is bound to happen), as well as looking into data validation tools so you don't have to code all this validation yourself. But that is not in the scope of this answer, obviously.

score 1 · Answer 2 · answered Jul 22 '15 at 23:00

1

If you want to require fields to be filled, then you really should use a required field validator - see https://msdn.microsoft.com/en-us/library/5hbw267h%28VS.80%29.aspx?f=255&MSPPError=-2147217396

answered Jul 22 '15 at 23:00

morgb

2,252
2
14
14

Empty Textbox still saves data in SQL Server even columns in table are not allowed null

2 Answers2