locked
Parameters with DBNull.Value becomes "default" and causes error in SqlServer RRS feed

  • Question

  • User1631665723 posted

    Hi, I have a table with a nullable column.

    When I try to insert a record using INSERT statement I pass a parameter set to the value DBNull.Value.

    Then I get an error from the SqlServer saying that the parameter was not supplied.

    Using Sql-profiler i see that the applied parameter is set to the keyword 'default'.

    I try to run the query inside Sql Server Management Studio, it looks something like this:

    exec sp_executesql N'INSERT INTO [tbl] ([Val1]) VALUES (@Val1_P)', @Val1_P=default

    First, why is the server giving the error about parameter not supplied? I have supplied it, and the value is keyword default.

    Then I try to change the query to setting @Val1_P=null. This works..

    BUT the problem then is, how do I set this from ADO Command object? I have set the parameter to DbNull.Value, and it translate it to 'default'.. What's going on here? I wanted the parameter to be the keyword 'null' not 'default'.

    Any suggestions? Thank you in advance!

    Friday, October 8, 2010 9:06 PM

Answers

  • User-2115483147 posted

    Please check this article and check the paragraph, I think it can help you to fix this issue,

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/d2cba2de-d733-4acf-b75a-c763aec16e23

    For input parameters, the value is bound to the SqlCommand that is sent to the server. For output and return value parameters, the value is set on completion of the SqlCommand and after the SqlDataReader is closed.

    When you send a null parameter value to the server, the user must specify DBNull, not null. The null value in the system is an empty object that has no value. DBNull is used to represent null values.

    An exception is thrown if non-Unicode XML data is passed as a string.

    If the application specifies the database type, the bound value is converted to that type when the provider sends the data to the server. The provider tries to convert any type of value if it supports the IConvertible interface. Conversion errors may result if the specified type is not compatible with the value.

    Both the DbType and SqlDbType properties can be inferred by setting the Value.

    The Value property is overwritten by SqlDataAdapter.UpdateCommand.

    Also please check this article if you are not familiar with DBNull,

    http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 14, 2010 11:36 PM

All replies

  • User1224940435 posted

    1. Try to supply null instead DBnull.Value

    Still problem then put some code that passvalue so it will help me to solve your problem. 

    Saturday, October 9, 2010 12:49 AM
  • User-548979073 posted

    If the column is nullable you can declare the parameter as below

    DECLARE @Val AS VARCHAR(50) = NULL

    Now even of you don't pass any value to parameter @Val null will be inserted and when a proper value is passed that will be inserted to table.

    Saturday, October 9, 2010 1:14 AM
  • User1867929564 posted

     Has the problem solved ?

    If you are using sp then set that parameter as optional.
    Show the code.

    Monday, October 11, 2010 6:57 AM
  • User-2115483147 posted

    Please check this article and check the paragraph, I think it can help you to fix this issue,

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/d2cba2de-d733-4acf-b75a-c763aec16e23

    For input parameters, the value is bound to the SqlCommand that is sent to the server. For output and return value parameters, the value is set on completion of the SqlCommand and after the SqlDataReader is closed.

    When you send a null parameter value to the server, the user must specify DBNull, not null. The null value in the system is an empty object that has no value. DBNull is used to represent null values.

    An exception is thrown if non-Unicode XML data is passed as a string.

    If the application specifies the database type, the bound value is converted to that type when the provider sends the data to the server. The provider tries to convert any type of value if it supports the IConvertible interface. Conversion errors may result if the specified type is not compatible with the value.

    Both the DbType and SqlDbType properties can be inferred by setting the Value.

    The Value property is overwritten by SqlDataAdapter.UpdateCommand.

    Also please check this article if you are not familiar with DBNull,

    http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 14, 2010 11:36 PM