Menu
I'm having trouble coming up with the proper syntax for allowing either a string or a NULL to be passed to the database. Here's my code:
string insertString = String.Format(
@"INSERT INTO upload_history (field1, field2, field3)
VALUES ('{0}', '{1}', '{2}')",
varField1, varField2, varField3);
I used single quotes around the variable placeholders so that the database would properly accept a string value. However, if NULL is passed, it ends up going into the database as the string "NULL".
Is there a way I can leave the single quotes out of the InsertCommand string and conditionally add single quotes to my variables?
521
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
You cannot insert NULL values in col1 and col2 because they are defined as NOT NULL. If you run the script as is, you will receive an error. To fix this code, replace NULL in the VALUES part with some values (for example, 42 and 'bird' ).
You can insert NULL value into an int column with a condition i.e. the column must not have NOT NULL constraints. The syntax is as follows. INSERT INTO yourTableName(yourColumnName) values(NULL); To understand the above syntax, let us first create a table.
Empty string or NullA SQL NVARCHAR() NULL can be either empty or null . If you allow the string to be null you'd better have a strict definition of how null is different to an empty string. There might be cases where null means unspecified while an empty string means specified as empty.
Don't concatenate the string (string.Format) - use parameters (@p1 etc) - then you can pass DBNull.Value to mean null to SQL Server
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"INSERT INTO upload_history (field1, field2, field3)
VALUES (@p1, @p2, @p3)";
cmd.Parameters.AddWithValue("@p1", (object)someVar ?? DBNull.Value);
//...
This also protects you from SQL injection
150
Concentating the string with String.Format might be a big security risk (SQL Injection), and also problematic if you want to insert the ' character.
Solution:
cmd.CommandText = "INSERT INTO upload_history (field1, field2, field3) " +
"VALUES (@p1, @p2, @p3)";
cmd.Parameters.AddWithValue("@p1", varField1);
cmd.Parameters.AddWithValue("@p2", varField2);
cmd.Parameters.AddWithValue("@p3", varField3);
cmd.ExecuteNonQuery();
In the spirit of answering the question as it was asked, and being fully aware that refactoring the code to paramaterizing the queries is the correct solution, you could write a function that returns either a single-quoted string or a non-quoted NULL string value, then remove the single-quotes from the query string.
string insertString = String.Format( @"INSERT INTO upload_history (field1, field2, field3) VALUES ({0}, {1}, {2})", ToStringorNull(varField1), ToStringorNull(varField2), ToStringorNull(varField3));
If you are using VS 2008 you could even implement it as an extension method.
string insertString = String.Format( @"INSERT INTO upload_history (field1, field2, field3) VALUES ({0}, {1}, {2})", varField1.ToStringorNull, varField2.ToStringorNull, varField3.ToStringorNull);
I'll leave creating the ToStringorNull function to you - it isn't hard :-)
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
