CODEWITHSUNDEEP
sql.net
Akila Ranasinghe
Akila Ranasinghe

Reputation: 169

How to insert a null value to sql date field

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.

enter image description here

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

Upvotes: 1

Views: 354

Answers (2)

Habeeb
Habeeb

Reputation: 8007

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

  1. Use Query and pass values as parameters to SqlCommand, or
  2. Use Stored Procedure and pass values as parameters to SqlCommand
  3. 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();

Upvotes: 1

Rangga
Rangga

Reputation: 1

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 + "')";
}

Upvotes: 0

Related Questions