Insert NULL into SQL when string is empty

Question

trying to insert NULL when there wasn't selected anything in my combobox but when I do this:

prikaz.Parameters.AddWithValue("@odjezd", cb_odjezd.Text == string.Empty ?
                                               null : cb_odjezd.Text);

I receive following exception:

The parameterized query expects the parameter @ odjezd

May someone help me solve this please?

score 8 · Accepted Answer · edited May 23 '17 at 11:53

8

Try to use DbNull.Value value instead.

So it becomes

prikaz.Parameters.AddWithValue("@odjezd", 
         string.IsNullOrEmpty(cb_odjezd.Text) ? DbNull.Value : cb_odjezd.Text);

EDIT

Acroding to acepted answer here, it should be enough in your case to (object)DBNull.Value

The issue is that C# expects the same type values in condition here, but in one case you set DbNull.Value in another string. So casting DbNull.Value to object make "pass" that rule.

edited May 23 '17 at 11:53

Community

1
1

answered Sep 10 '13 at 06:46

Tigran

61,654
8
86
123

It says: Type of conditional expression cannot be determined because there is no implicit conversion between Systém.DBnull and string. Any ideas? – Marek Sep 10 '13 at 06:50
Insert a cast of one side or the other to `(object)` to make it compile, e.g. `? (object)DbNull.Value : cb_objezd.Text` – Damien_The_Unbeliever Sep 10 '13 at 06:52
@Marek One thing ,Your `datatype` is `nullable` ? – Suraj Singh Sep 10 '13 at 07:37

Harsh · Answer 2 · 2013-09-10T06:51:08.420

2

Use DBNull

prikaz.Parameters.AddWithValue("@odjezd", cb_odjezd.Text == string.Empty ? DBNull.value : cb_odjezd.Text);

try using this :

if (cb_odjezd.Text== string.Empty)
{
    prikaz.Parameters.AddWithValue("@odjezd", DBNull.Value);
}
else
{
    prikaz.Parameters.AddWithValue("@odjezd", cb_odjezd.Text);
}

The keyword null represents an invalid reference. The class System.DbNull represents a nonexistent value in a database field.

edited Sep 10 '13 at 06:51

answered Sep 10 '13 at 06:45

Harsh

3,683
2
25
41

4

*Type of conditional expression cannot be determined because there is no implicit conversion between '`System.DBNull`' and '`string`'* – MarcinJuraszek Sep 10 '13 at 06:47
It says: Type of conditional expression cannot be determined because there is no implicit conversion between Systém.DBnull and string may you please help me solve this out please? – Marek Sep 10 '13 at 06:48
@Marek The error tells you that both side of `?`(Operator ,branches etc.) have different types , You have to implicitly convert them to a common type . – Suraj Singh Sep 10 '13 at 08:59

Suraj Singh · Answer 3 · 2013-09-10T09:00:56.957

Either the type of first_expression and second_expression must be the same, or an implicit conversion must exist from one type to the other.-MSDN.

?: can not cast type itself ,Here you are returning either a string or DbNull type value ,So you have to cast instance of _objezd.Textto object type to make it compatible .

 prikaz.Parameters.AddWithValue("@odjezd", 
  _odjezd.Text == string.Empty ? DbNull.Value : (object)cb_odjezd.Text);

score 1 · Answer 4 · edited May 23 '17 at 12:24

DBNull is frankly a bit of a PITA - and it bites lots of people. However, one way to avoid this pain is to use an ORM or micro-ORM that will make this problem go away. For example, with dapper:

int id = ...
string odjezd = ...
...
connection.Execte("insert SomeTable (...) values (@id, @odjezd, ...)",
     new {id, odjezd, ... });

Here dapper will automatically parameterize the values correctly, inlcuding treating null-references and empty Nullable<T> as DBNull.Value. Plus it means you avoid all the "fun" of ADO.NET, simply having to worry about:

connection management
the sql
what the parameter values are (but not the parameterization itself)

Note that dapper also has great tools in terms of materialization too, to avoid all the pain of data-readers.

score 1 · Answer 5 · edited Jun 01 '20 at 15:55

Try my replied solution from this post my reply

Actually it is a type confusion problem which should be an compiler time problem. But cause in runtime when nullable string is passed to AddWithValue(string, object).

If you want string, cast DBNull.Value to string by microsoft c# DBNull library build-in method DBNull.Value.ToString() equivalently string.Empty.

If you want a generic type cast the string (object)str.

Insert NULL into SQL when string is empty

5 Answers5