Exception When Addwithvalue Parameter Is Null

Exception when AddWithValue parameter is NULL

Annoying, isn't it.

You can use:

command.Parameters.AddWithValue("@application_ex_id",
       ((object)logSearch.LogID) ?? DBNull.Value);

Or alternatively, use a tool like "dapper", which will do all that messing for you.

For example:

var data = conn.Query<SomeType>(commandText,
      new { application_ex_id = logSearch.LogID }).ToList();

I'm tempted to add a method to dapper to get the IDataReader... not really sure yet whether it is a good idea.

Null reference exception when using command.Parameters.AddWithValue()

At a very high-level using the Object initialization syntax follows these steps...

1. Create an instance of the desired class by calling the appropriate constructor
2. Call all of the listed property setters
3. Return the newly initialized object

ComboBoxValuesToShow is called from the constructor but based on the listed steps the parent property (which is used in the method) is not set until after the constructor returns, because the property setters are not called until after the instance is created. Therefore the parent property will always be null in this case.

There are a couple of ways that come to mind to solve this...

• If you want to use the parent property in the constructor then pass the value into the constructor and initialize the property internally.

Or

• Make the ComboBoxValuesToShow method accessible from the parent window and move the call to ComboBoxValuesToShow from the constructor to the event handler in the parent window.

.NET and MySql paramters, AddWithValue NULL variables, how to avoid to check for nulls

DbNull.Value doesn't solve the problem, the exception is thrown if you don't specify that the parameter is nullable.

Dim par As New MySqlParameter("p1", Nothing)
par.IsNullable = True
par.MySqlDbType = MySqlDbType.Int32 ' or any other type you need '
par.Direction = ParameterDirection.Input
command.Parameters.Add(par)
command.ExecuteNonQuery()

This way it works. I don't have to check for the empty string (or weird "impossible" value) in the SP.

Of course you can write a shared generic method to easy the parameter setting, working for any database/language type:

Public Shared Function GetNullableSqlParameter(Of T As IComparable)(ByVal parameterName As String, ByVal parameterType As MySqlDbType, ByVal value As T, Optional ByVal nonNullable As T = Nothing) As MySqlParameter

        Dim par As New MySqlParameter
        par.ParameterName = parameterName
        par.MySqlDbType = parameterType
        par.Direction = ParameterDirection.Input

        If value Is Nothing OrElse (nonNullable IsNot Nothing AndAlso nonNullable.Equals(value)) Then
            par.IsNullable = True
            par.Value = DBNull.Value
            par.SourceColumnNullMapping = True
        Else
            par.IsNullable = False
            par.Value = value
        End If

        Return par
    End Function

And call it like:

command.Parameters.Add(General.GetNullableSqlParameter("_zip", MySqlDbType.String, zipcode))

cmd.Parameters.AddWithValue(@param1,param1 ?? DbNull.Value)

no need to convert if null set DBNull

cmd.Parameters.AddWithValue("@col_1", (object)val1 ??DBNull.Value);

error with addwithvalue sql parameter

You are using the command variable twice instead of command2 after you create command2. You get the error because you cleared command of all parameters, then add parameters (which do not match the existing query) and then execute ExecuteNonQuery which then throws the error.

Change 2nd execution statement / command like so, notice that after the creation of command2 it is now also used instead of reusing command.

SqlCommand command2 = new SqlCommand(query2, cmd.Connection);
command2.Parameters.AddWithValue("@LName", regLname_text.Text);
command2.Parameters.AddWithValue("@FName", regFname_text.Text);
command2.Parameters.AddWithValue("@EmpTemplate", template);
command2.Parameters.AddWithValue("@AccountType", AcctType_cb.SelectedItem.ToString());
command2.Parameters.AddWithValue("@EmpStatus", "Active");
var numberOfRecordsInserted = command2.ExecuteNonQuery();

// value of numberOfRecordsInserted should be 1

Also when you are done using a SqlCommand you can dispose of it, there is no need to call SqlCommand.Parameters.Clear() unless you plan on reusing the exact same SqlCommand instance which you are not (at least not in the posted code).

cmd.Parameters.AddWithValue nullable int

You need to add an object:

cmd.AddWithValue("@DependencyID", _DependencyID == null? DBNull.Value : (object)_DependencyID); 

You can shorten that to

cmd.AddWithValue("@DependencyID", (object)_DependencyID ?? DBNull.Value); 

SqlParameter disappears when assigned a value of null?

Don't set the value to null if you want to pass a NULL to the stored procedure

The docs are almost right. In the phrase :

Use Value to send a NULL value as the value of the parameter.

The link points to DbNull.Value. That's a documentation error

Passing null instead of DbNull.Value means you want to use the stored procedure's default parameter. That part of the docs is correct :

Use null or do not set Value to use the default value for the parameter.

Update

I just added a Github issue for this

how to pass null value to database from cmd.Parameters.AddWithValue in asp.net

cmd.Parameters.Add(new SqlParameter("@RedirectUris", string.IsNullOrEmpty(RedirectUris.Text) ? (object)DBNull.Value : RedirectUris.Text));

---

Related Topics