How to force user to enter value in a database field

Question

With my Application, i want to make sure if user enter no value in a textbox, and click on the
save button to send data in the sqlserver db.The database side validation prevent this violation and set ErrorMessage which my application will catch and Display a meaninful Message
to the User. For each required field i set it to NOT NULL. But when i test,i can still enter
enter empty textbox values it gets inserted with out value. what am i missing?

string connectionstring = "Data Source=abcdef;Initial Catalog=HMS;Persist Security Info=True;User ID=sysad;Password=abcdef";
        SqlConnection connection = new SqlConnection(connectionstring);

        string SelectStatement = "SELECT * FROM tablename where RegistrationNo = @RegistrationNo";
        SqlCommand insertcommand = new SqlCommand(SelectStatement, connection);
        insertcommand.Parameters.AddWithValue("@RegistrationNo", textBox10.Text);
        SqlDataReader reader;
        try
        {
            connection.Open();
            reader = insertcommand.ExecuteReader();

            while (reader.Read())
            {
                textBox11.Text = reader["RegistrationNo"].ToString();
                textBox1.Text = reader["Appearance"].ToString();
                textBox2.Text = reader["VolumePH"].ToString();
                textBox3.Text = reader["Mobility"].ToString();
                textBox4.Text = reader["Viability"].ToString();
                textBox5.Text = reader["Head"].ToString();
                textBox6.Text = reader["MiddlePiece"].ToString();
                textBox7.Text = reader["Tail"].ToString();
                textBox8.Text = reader["SpermCount"].ToString();
                dateTimePicker1.Text = reader["Date"].ToString();
                textBox9.Text = reader["Comment"].ToString();



            }//end while
            reader.Close();
        }
        catch (Exception ex)
        {
            throw ex;

        }//end catch

I would guess that an empty string gets inserted in the database, something that does not violate the NOT NULL constraint. — Jean Hominal, Jun 26 '12 at 12:29
TextBox has not NULL value, they have empty value. Empty value is not equal to NULL, so the violation is not raised on DB. — Romil Kumar Jain, Jun 26 '12 at 12:31
In Win App, You can check for empty textbox through validations. [Here](http://stackoverflow.com/a/5605687/1004522) is the link. — Ebad Masood, Jun 26 '12 at 12:33

score 7 · Accepted Answer · answered Jun 26 '12 at 12:30

7

What am i missing?

I think you are missing a distinction between null and an empty string.

Databases distinguish between null and empty. If your ToString succeeds, then you have a non-null string there, and so DB is happy to accept it as a valid value.

In general, using DB for user-side validation is somewhat wasteful: if you know that the field must not be empty, you should check for it in the UI; DB validation should serve as the last resort that preserves the integrity of your data model.

answered Jun 26 '12 at 12:30

Sergey Kalinichenko

714,442
84
1,110
1,523

"Databases distinguish between null and empty." Not all databases are created equal. E.g. MS SQL DBs do, Oracle SQL DBs do not. ecomma is using MS SQL, so your statement is true in his case, just not in all. – Tim S. Jun 26 '12 at 12:51
@TimS. Well, Oracle is in a league of their own: they made a decision years ago, and decided to stick to it in the face of standards saying otherwise (that's what `2` in `VARCHAR2` is about). Here is a [link](https://forums.oracle.com/forums/thread.jspa?threadID=456874&start=0&tstart=0) that discusses the subject at great length (literally: it's five pages long). – Sergey Kalinichenko Jun 26 '12 at 13:06

score 1 · Answer 2 · answered Jun 26 '12 at 12:33

1

You can use requiredfield validator in the Server side code and validate. If it is empty string return error there itself.

Going to sql server and throwing error is bad.

if(txtBox.Text.Trim() == string.Empty)
  //throw "cannot be null error to user;

answered Jun 26 '12 at 12:33

Rajesh Subramanian

6,400
5
29
42

Ignacio Gómez · Answer 3 · 2012-06-26T12:45:42.590

0

May be a good idea is to check the fields when you press the button. Example:

private void button_Click(object sender, EventArg e){
   if (textbox1.Text == ""){
      MessageBox.Show("Your message to the user");
   }
}

Hope it helps

edited Jun 26 '12 at 12:45

answered Jun 26 '12 at 12:36

Ignacio Gómez

1,587
4
23
41

score 0 · Answer 4 · answered Jun 26 '12 at 12:45

0

Its better to check the user input in the user interface. If the user have to enter some value you should check it before trying to insert it to the database.

answered Jun 26 '12 at 12:45

Ozgur Dogus

911
3
14
38

How to force user to enter value in a database field

4 Answers4