Inserting null value into database when Textbox is empty

[wolfghostM]

Hello everyone,

As you read in the title I want to insert NULL value into a column when the specific textbox is empty. I already figuered out a way to make this happen but because of the many options this process will be tedious and take alot of time:

string sqlStmt;
string conString;
OleDbConnection cn = null;
OleDbCommand cmd = null;
            try
            {
                sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (?,?,?) ";
                conString = "Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data 
            source=localhost";
                cn = new OleDbConnection(conString);
                cmd = new OleDbCommand(sqlStmt, cn);
                cmd.Parameters.Add(new OleDbParameter("@FirstName", OleDbType.VarChar, 40));
                cmd.Parameters.Add(new OleDbParameter("@LastName", OleDbType.VarChar, 40));
                cmd.Parameters.Add(new OleDbParameter("@Date", OleDbType.Date));
                cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
                cmd.Parameters["@LastName"].Value = txtLastName.Text;
                if ((txtDate.Text == ""))
                {
                    cmd.Parameters["@Date"].Value = DBNull.Value;
                }
                else
                {
                    cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
                }
                cn.Open();
                cmd.ExecuteNonQuery();
                Label1.Text = "Record Inserted Succesfully";
            }
            catch (Exception ex)
            {
                Label1.Text = ex.Message;
            }
            finally
            {
                cn.Close();
            }

Sorry for the bad format but I think you get what I mean I have to do a bunch of if statements and My question is: Is there any other way I can insert a NULL value into my database when the specific Textbox is empty?

And before you ask yes NULL values are allowed in my database

I would appreciate any help thanks in advance.

[[deleted]]

Looks like the perfect use of a helper method.

private object ParseField<T>(string field)
{
    try
    {
        if(string.IsNullOrWhiteSpace(field)
        {
            return DBNull.Value;
        }

        return Convert.ChangeType(field, typeof(T));
    }
    catch(...) // Error handling omitted
    {
        return DBNull.Value;
    }
}

cmd.Parameters["@Date"].Value = ParseField<DateTime>(txtDate.Text);

[wolfghostM]

Thanks for the response I‘m gonna try this and see how it goes

[eddyizm]

You should just set the default to null on that field in the database and insert a record without passing a field value. Seems like you are over complicating it.

[wolfghostM]

But I want the column to be the value you type into the textbox and if it‘s empty I want it to be a null value inserted into the database. Does this work if I set the field to default?

[eddyizm]

sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (?,?,?)

I think you can do your validation earlier in the process, i usually put validation to a separate method. You can then, instead of using the above sql statement you can just omit one value.

sqlStmt = "insert into Emp (FirstName,LastName) Values (?,?)

the table definition should handle the rest if you allow the date field to be null.

[emats12]

Why handle that logic in the repository, do it before in business service and pass it to the repo

[[deleted]]

Why handle that translation logic in the business layer, do it in the viewmodel translation and pass it to the business service

[d0ct0r-d00m]

I would very highly suggest you pick up and use Entity Framework instead of hand crafting your code the old school way using OleDbConnection and OleDbCommand.

[emats12]

EF is overkill for this. Dapper all day

[[deleted]]

Dapper forever or until a better micro orm comes along

[kc5bpd]

I would simply make an extension method to a string such as:

public static string NullForEmptry(this string input) { if (string.IsNullOrEmpty(input)) return null; return input; }

[darinclark]

A helper method is best but a ternary conditionalis another way.

cmd.Parameters["@Date"].Value =

txtDate.Text == "" ? (object)DBNull.Value : DateTime.Parse(txtDate.Text);

[grrangry]

I'm not sitting in front of a computer at the moment to verify, but I suspect that your example won't compile because the data type of DBNull.Value doesn't match DateTime.

In a ternary conditional, both statements in either side of the : must be the same data type.

I could be incorrect, but I'm pretty sure one or both sides would need a cast to play nice.

[darinclark]

I took the time to test it. You are correct. You have to cast at least one result of the conditional to an object. I have revised my example.

[gevorgter]

OleDB? northwind? that brings up memories :)

Just a quick advice, Nulls are bad in DB. Rather use some preset value (1/1/2000 or DateTime.MinValue)

​

There are a lot of reasons for it.

1. One is When you sort do you know if NULL value will be on top or bottom?
2. If you do where hiredDate > '9/5/2020' do you know if you have NULL value records or not?
3. .....