How to insert a null value to sql date field

Question

I'm making a project to insert data of elephants in a conservation center to it's database.In it I assume that some elephants are born in it while some are gifted.So I'm hoping to save two kinds of dates to the database.When an elephant is born it should only have a birthday where as if an elephant is gifted,it should have two dates named arrival date and birthday.

I have coded the inserting query.

            try
        {
            if (rbm.Checked == true)
            {
                gen = "Male";
            }
            else if (rbf.Checked == true)
            {
                gen = "Female";
            }
            if (rbgr.Checked == true)
            {
                med = "gifres";
            }
            else if (rbb.Checked == true)
            {
                med = "born";
            }
            String save_emp_query = "INSERT INTO ElephantData VALUES('" + txtidentyno.Text + "','" + txtelname.Text + "','" + cmbspecies.Text + "','" + gen + "','" + med + "','" + dtpdob.Text + "','" + dtpdoa.Text + "','" + cmbcon.Text + "')";
            cmd = new SqlCommand(save_emp_query, con);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Elephant " + txtelname.Text + " (" + txtidentyno.Text + ") successfully saved to the database!", "Saved!", MessageBoxButtons.OK, MessageBoxIcon.Information);
            clear();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error while Saving....." + Environment.NewLine + ex);
        }

Here,I'm having a problem when saving the dates.If I want to save an elephant who is born in the center,it should not have an arrival date.So I ignore the arrival date picker and save the details to the database.When I view data in my database,a default value is saved to the arrival date.

I need to keep the arrival date column blank by inserting a null value to it.How can I do it?

There's a plethora of examples online (on this site and the wider net), please search and try yourself. — HoneyBadger
– HoneyBadger, Commented Sep 17, 2018 at 6:21

Habeeb · Accepted Answer · 2018-09-17 06:36:11Z

1

First of all, its not a good practice to create query from .NET. Instead,

Use Query and pass values as parameters to SqlCommand, or
Use Stored Procedure and pass values as parameters to SqlCommand
Alternatively, you can use your existing approach and pass null like:

string save_emp_query = "INSERT INTO ElephantData VALUES('" + txtidentyno.Text + "','" + txtelname.Text + "','" + cmbspecies.Text + "','" + gen + "','" + med + "','" + dtpdob.Text + "',null,'" + cmbcon.Text + "')"

If you can following Approaches 1 and 2, you can refer the below code:

// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
"INSERT INTO ElephantData VALUES(@IdNo, @Species ....., conn);

// 2. define parameters used in command object
    SqlParameter param  = new SqlParameter();
    param.ParameterName = "@IdNo";
    param.Value         = txtidentyno.Text;
    SqlParameter param2  = new SqlParameter();
    param2.ParameterName = "@Species";
    param2.Value         = cmbspecies.Text;

// 3. add new parameter to command object
    cmd.Parameters.Add(param);

// 4. Finally Execute Query
cmd.ExecuteNonQuery();

edited Sep 17, 2018 at 6:36

answered Sep 17, 2018 at 6:28

Habeeb

8,0751 gold badge33 silver badges34 bronze badges

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

2 Comments

Akila Ranasinghe Over a year ago

I did it in the first method you suggested me and it perfectly worked.Thank you!

Habeeb Over a year ago

Good @akila-ranasingha, you followed the better approach. Could you please upvote and mark it as an answer, so that it helps the community better to find solutions.

Rangga · Accepted Answer · 2018-09-17 06:22:04Z

0

maybe you need condition like this

if(dtpdoa.Text == ''){
 String save_emp_query = "INSERT INTO ElephantData VALUES('" + txtidentyno.Text + "','" + txtelname.Text + "','" + cmbspecies.Text + "','" + gen + "','" + med + "','" + dtpdob.Text + "',null,'" + cmbcon.Text + "')";
}else{
 String save_emp_query = "INSERT INTO ElephantData VALUES('" + txtidentyno.Text + "','" + txtelname.Text + "','" + cmbspecies.Text + "','" + gen + "','" + med + "','" + dtpdob.Text + "','" + dtpdoa.Text + "','" + cmbcon.Text + "')";
}

answered Sep 17, 2018 at 6:22

Rangga

12 bronze badges

Collectives™ on Stack Overflow

How to insert a null value to sql date field

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related