I have some textbox which can have empty value. When I am trying to insert that textbox.text in insert command then it show error in sql. I want the automatic check when textbox is empty it will insert null in database in sql server 2008 R2. I am using normal insert into command and I do not want to use procedure(too clumsy). please help me anyone.
Thanks in advance.
Not really sure about your code, but it should be like:
using (SqlConnection conn = new SqlConnection("ConnectionString"))
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "INSERT into yourTable(ID, Col1, Col2) VALUES (@ID, @Col1, @Col2);";
cmd.Connection = conn;
conn.Open();
cmd.Parameters.AddWithValue("@ID", yourID);
cmd.Parameters.AddWithValue("@Col1", string.IsNullOrWhiteSpace(textBox1.Text) ?
DBNull.Value: textBox1.Text);
cmd.Parameters.AddWithValue("@Col2", string.IsNullOrWhiteSpace(textBox2.Text) ?
DBNull.Value : textBox2.Text);
cmd.ExecuteNonQuery();
}
You can check for Empty String using string.IsNullOrWhiteSpace, pass null accordingly. Use string.IsNullOrEmpty(), if you want to consider spaces as valid values.
null pre-edit was not the right call - DBNull.Value being the key. Otherwise very clear, explanatory code from the start.null rather than DBNull.Value :)If you are using direct insert query
"insert into table_name (column1,column2,column3) values("+column1value+","+column2value+","+textbox.Text.Trim()!=""?textbox.Text.Trim():DBNull.Value+")";
Even if you are using parameterised query, you can use the same expression
textbox.Text.Trim()!=""?textbox.Text.Trim():DBNull.Value
Try something like this:
using (SqlConnection conn = new SqlConnecction(connStr))
{
conn.Open();
string qry = "INSERT TargetTable (TargetColumn) VALUES (@TextValue)";
using (SqlCommand cmd = new SqlCommand(qry, conn))
{
cmd.Parameters.Add(new SqlParameter("@TextValue", String.IsNullOrEmptyOrWhiteSpace(textBox.Text) ? DBNull.Value : textBox.Text));
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException sex)
{
// whatever
}
}
}
