ADO.NET, SqlParameter, and NULL

[quantass]

Im trying to execute the following:

 

mySqlCmd.Parameters.Add("@Parent_ID", SqlDbType.Int).Value = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : myParentID);

 

However i get an error that Null cant be converted to Int. Is there any way i can uyse the SqlParameters approach but pass in a Null value for the field or must i wrap the entire connection within a IF/ELSE statement one containing a hard-coded NULL within the query and the other as a standard parameter with a proper int value?

 

 

Thanks

[amir100]

Me.SqlCommand1.Parameters(1).Value = DBNull.Value

 

I use VB but in this case it doesn't matter. The type of Parameters(1) is SqlDbType.Int. I didn't experience the same error you have. Are you sure it's your code that is wrong? Have you setup your database correctly? Maybe your source column doesn't allow null. Just guessing though.

 

Here's a complete list of my (sample) code:

        Try
           Me.SqlConnection1.Open()
           Me.SqlCommand1.Parameters(0).Value = "23"
           Me.SqlCommand1.Parameters(1).Value = DBNull.Value
           Me.SqlCommand1.ExecuteNonQuery()
       Catch exc As Exception
           Me.SqlConnection1.Close()
       Finally
           Me.SqlConnection1.Close()
       End Try

[quantass]

Thanks. All is good now!!

[amir100]

Mind sharing your solution of the problem?

[Nerseus]

My guess the problem was with the casting. The ternary can't resolve the left side (DBNull.Value, which is just "object") and the right side (an int). You'd normally have to cast the int as an object, to match the type of DBNull.Value.

 

On a side note, if quantass is reading this, why would you pass myParentID when you're checking sectionUpdate.iSection.ParentID? Normally the tertiary operator is used just like you have it, but returns the value it's comparing. For example:

Change
... = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : myParentID); 
to this:
... = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : sectionUpdate.iSection.ParentID); 

 

-ner

Edited February 21, 2007 by Nerseus

[amir100]

That's new. I never knew such constraint in a ternary operator. I guess I need to read more. :D