How to insert int NULL into numeric database field?

Question

I am trying to insert a NULL value into a numeric database field from a winform textbox using C#. I am creating an app to enter production data into a database. Quantity variables are set as int? to accept null since there wouldn't be a value to enter if a piece of equipment sat down. The database fields also have a default value set to Null. How would I be able to leave a textbox blank and enter Null into the database field? I have scaled down my code to include what is affected.

private void btnInsert_Click(object sender, EventArgs e)
    {
        int? runQty = 0;
        int? scrapQty = 0;

        try
        {
            dbConn = new OleDbConnection();
            dbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ch + strDataFilePath + ch;
            dbConn.Open();

            sql = "INSERT INTO DailyProduction (runQty, scrapQty)" +
            "Values (@runQty, @scrapQty)";

            dbCmd = new OleDbCommand(sql, dbConn);

            if (runQty.HasValue)
            {
                runQty = Convert.ToInt16(this.runQuatity.Text);
                dbCmd.Parameters.Add("@runQty", OleDbType.Numeric).Value = runQty;
            }
            else
            {
                runQty = null;
                dbCmd.Parameters.Add("@runQty", OleDbType.Numeric).Value = runQty;
            }

            if (scrapQty.HasValue)
            {
                scrapQty = Convert.ToInt16(this.scrapQuantity.Text);
                dbCmd.Parameters.Add("@scrapQty", OleDbType.Numeric).Value = scrapQty;
            }
            else
            {
                scrapQty = null;
                dbCmd.Parameters.Add("@scrapQty", OleDbType.Numeric).Value = scrapQty;
            }

            dbCmd.Connection.Close();
            dbConn.Close();

            MessageBox.Show("Record Entered!");
        }
        catch (Exception err)
        {
            MessageBox.Show("Error: " + err.Message.ToString());
        }
    }

runQty.HasValue doesn't make sense, as you haven't given runQty a value yet. It will always be 0 at this point. It should be !String.IsNullOrEmpty(this.runQuatity.Text). Same goes for the other IF — Rob
– Rob ♦, Commented Feb 24, 2016 at 22:15
social.msdn.microsoft.com/Forums/en-US/… says to set the value to DBNull.Value. — Quantic
– Quantic, Commented Feb 24, 2016 at 22:15
Convert.ToInt16() is where your problem lies. It will convert null to 0, by design. You need to check if runQuantity.Text is empty and then set DBNull as parameter value. — Harsh
– Harsh, Commented Feb 24, 2016 at 22:17

Jaguir · Accepted Answer · 2016-02-24 22:27:23Z

2

You could do something like this:

var runQty = string.IsNullOrEmpty(this.runQuatity.Text)
    ? DBNull.Value
    : Convert.ToInt16(this.runQuatity.Text);
dbCmd.Parameters.Add("@runQty", OleDbType.Numeric).Value = runQty;

var scrapQty = string.IsNullOrEmpty(this.scrapQuantity.Text)
    ? DBNull.Value
    : Convert.ToInt16(this.scrapQuantity.Text);
dbCmd.Parameters.Add("@scrapQty", OleDbType.Numeric).Value = scrapQty;

answered Feb 24, 2016 at 22:27

Jaguir

3,7001 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arturo Menchaca · Accepted Answer · 2016-02-24 22:28:33Z

1

You can check whatever a string is null or empty using string.IsNullOrEmpty() or string.IsNullOrWhiteSpace() methods:

if (string.IsNullOrEmpty(textBox1.Text))
{
    ...
}

Also, for null values you should use DbNull.Value:

dbCmd.Parameters.Add("@runQty", OleDbType.Numeric).Value = DbNull.Value;
dbCmd.Parameters.Add("@scrapQty", OleDbType.Numeric).Value = DbNull.Value;

answered Feb 24, 2016 at 22:28

Arturo Menchaca

16.1k1 gold badge32 silver badges54 bronze badges

1 Comment

user2305700 Over a year ago

Thank you, I saw this type of solution in another post but, didn't think string would work as I am using an int. I should have tried anyway. This works exactly how I ant it to.

Collectives™ on Stack Overflow

How to insert int NULL into numeric database field?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related