none
Strange SqlString behavior RRS feed

  • Question

  •  

    I'm doing some database work and have started to use SqlTypes.  I was hoping that it would be easier to handle NULL's and also improve performance slightly.  There seems to be some strange behavior with SqlString though.  With an INSERT query, the following code works:

     

    Code Snippet

    command.Parameters.Add("@textparam1", new SqlString("test"));

    command.Parameters.Add("@textparam2", DBNull.Value);

    command.Parameters.Add("@intparam", SqlInt32.Null);

     

     

    However the following does not work:

    Code Snippet

    command.Parameters.Add("@textparam3", SqlString.Null);

     

     

    An exception is throw at run time when I call ExecuteNonQuery().  The exception is "Data is Null. This method or property cannot be called on Null values.".  This looks like the exception you get when you try reading the value of SqlString.Null.  Have Microsoft forgotten to check for nulls here?  Shouldn't the SqlString.Null work in exactly the same way as the SqlInt32.Null?  I could easily surround it with and 'if', but without knowing the reasons I will probably end up with bugs at some point, and most of the benefits of SqlString will be lost.

    Thursday, February 14, 2008 10:46 AM

All replies

  • Have you tried assigning a type to the parameter, like this:

    cmd.Parameters.Add("@TextParam", SqlDbType.NVarChar, 50).Value = SqlString.Null;
    Thursday, February 14, 2008 1:39 PM
  • Thanks, that does work, although I still think the original code should work.

     

    I'm not sure which way I'm going to go with this.  Including all of the types is more work, and  a lot easier to make mistakes when maintaining it.

    Thursday, February 14, 2008 2:47 PM