Passing Nothing as a value to a database?

I have a table that contains a nvarchar column that allows nulls.

I have a method in VB.NET that has a optional description variable that is set to Nothing

Public Shared Sub InsertDescription(Optional ByVal Description As String = Nothing)
{
    //Insert Description Procedure Call
}

If I call this method like:

InsertDescription();

It fails saying that the procedure was expecting the @Description, but was not provided. The only way I have found to remedy this is to set the defau开发者_开发知识库lt value to "" or to not make it optional and just pass in "", but I don't want to insert an "" string into the database tables description column, I want to pass in null by default.

Try using the DbNull.Value

If a database field has missing data, you can use the DBNull.Value property to explicitly assign a DBNull object value to the field.

-- Doesn't this work?

if (Description is nothing) then ' Set SP param to dbnull.value

If not, could you post some more relevant code?

Either pass DBNull, or in your stored proc declare the @Description parameter with default null

You can set @Description to a null value by default in your stored procedure when declaring your input parameters. This way @Description will be optional.

ALTER PROCEDURE MyProcedure
  @Description varchar(50) = null
AS
  ...

You will have to set the @Description parameter to DBNull.Value

EDIT

IIRC I think you need to change your declaration of Description to

Description As Nullable(Of String) = DbNull.Value

Database nulls are represented by DBNull.Value you want an if statement to set the param to DBNull.Value if Description = Nothing.

declaration missing.

Public Shared Sub InsertDescription(Optional ByVal Description As String = Nothing)
 {
   //Insert Description Procedure Call
 }

try using DBNull search here for more details