How to assign 'NULL' word to a text box (if the textbox is empty) during time?

Question

I have a problem in executing a SQL query from a C# tool where it tries to do the insert.

I need to insert NULL value if the string is empty (not entered by the user). I tried with the DB null value and normal string 'NULL' to do the NULL insert but all I get is an empty value (insetead of NULL keyword) which gives me the error.

Let me know if anyone has the solution for this....

Below is my code

if (comboBox_ConfacValue.Text == "")
{
    comboBox_ConfacValue.Text = DBNull.Value.ToString();
}

if (combobox_conversionDescription.Text == "")
{
    combobox_conversionDescription.Text = "NULL";
}

try
{
    con.Open();

    if (MessageBox.Show("Do you really want to Insert these values?", "Confirm Insert", MessageBoxButtons.YesNo) == DialogResult.Yes)
    {
        SqlDataAdapter SDA = new SqlDataAdapter(@" insert INTO Table1 (alpha1,alpha2,alpha3)  VALUES ('" + comboBox_ConfacValue.Text + "','" + combobox_conversionDescription.Text + "','"+ combobox_Description.Text + "',')",con)

        SDA.SelectCommand.ExecuteNonQuery();
        MessageBox.Show("Inserted successfully.");
    }
}

The null value must be set witout ' symbol in the insert insert into Table1 (...) values ('1',null) not ('1','null'). Your insert produce ('1','null'). Better aprouch is to set parametres to connection — blogprogramisty.net, Apr 18 '16 at 07:36
And what happens if someone types _'); DROP TABLE Table1;--_ Learn about Sql Injection and use parameters — Steve, Apr 18 '16 at 07:39
guys I get the point... What if I'm using the subquerey for the values, each value needs a subquerey so at that point I can't use what you suggest ? — Gowtham Ramamoorthy, Apr 18 '16 at 12:31
@blogprogramisty.net.... yes I know it should be used like that but since I use subquerey for each parameter values I had no other option to proceed wiht this method.... — Gowtham Ramamoorthy, Apr 18 '16 at 12:32
Typo alert: it's a **query** (or *subquery*) - not a "querey" .... — marc_s, Aug 11 '16 at 08:02

score 2 · Accepted Answer · edited May 23 '17 at 11:44

You should avoid this kind of code. Concatenating strings to produce an sql command is a recipe to disasters. Parsing errors is the common mistake, but a worse foe is lurking behind this pattern and is called Sql Injection

    try
    {
        con.Open();
        if (MessageBox.Show("Do you really want to Insert these values?", "Confirm Insert", MessageBoxButtons.YesNo) == DialogResult.Yes)
        {
            // Now the command text is no more built from pieces of 
            // of user input and it is a lot more clear
            SqlCommand cmd = new SqlCommand(@"insert INTO Table1 
                (alpha1,alpha2,alpha3)  
                VALUES (@a1, @a2, @a3)", con);
            // For every parameter placeholder add the respective parameter
            // and set the DbNull.Value when you need it
            cmd.Parameters.Add("@a1", SqlDbType.NVarChar).Value =
                string.IsNullOrEmpty(comboBox_ConfacValue.Text) ? 
                              DbNull.Value : comboBox_ConfacValue.Text);  

            cmd.Parameters.Add("@a2", SqlDbType.NVarChar).Value = 
                string.IsNullOrEmpty(combobox_conversionDescription.Text ) ? 
                              DbNull.Value : combobox_conversionDescription.Text );  

            cmd.Parameters.Add("@a3", SqlDbType.NVarChar).Value = 
                string.IsNullOrEmpty(combobox_Description.Text ) ? 
                              DbNull.Value : combobox_Description.Text );  

            // Run the command, no need to use all the infrastructure of
            // an SqlDataAdapter here....
            int rows = cmd.ExecuteNonQuery();

            // Check the number of rows added before message...
            if(rows > 0) MessageBox.Show("Inserted Successfully.");

From the point of view of the Sql Parser (on the server) there is no difference where the parameter placeholder is located. — Steve, Apr 18 '16 at 12:30
However I am not sure if I have really understood what do you mean for _subquery_ Anyway, if my suggestion doesn't work it is better to post a new question with a full example of a _subquery_ — Steve, Apr 18 '16 at 12:36

How to assign 'NULL' word to a text box (if the textbox is empty) during time?

1 Answers1