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 doesnt matter. The type of Parameters(1) is SqlDbType.Int. I didnt experience the same error you have. Are you sure its your code that is wrong? Have you setup your database correctly? Maybe your source column doesnt allow null. Just guessing though.

Heres 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 cant resolve the left side (DBNull.Value, which is just "object") and the right side (an int). Youd 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 youre checking sectionUpdate.iSection.ParentID? Normally the tertiary operator is used just like you have it, but returns the value its comparing. For example:

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

-ner

 

[amir100]

Thats new. I never knew such constraint in a ternary operator. I guess I need to read more.