CODEWITHSUNDEEP
c#sqlsql-server-2008
lemunk
lemunk

Reputation: 2636

SQL allow null in table in C#

Using SQL Server 2008, WinForms C# .NET 4.5, Visual Studio 2012.

I have a query that currently updates a table with some information from a GridView.

Below is the code that calls the stored procedure:

public void UpdateMain(string part, int? pareto)
{
  try
  {
    using (SqlConnection AutoConn = new SqlConnection(conn32))
    {
      AutoConn.Open();
      using (SqlCommand InfoCommand = new SqlCommand())
      {
        using (SqlDataAdapter infoAdapter = new SqlDataAdapter(InfoCommand))
        {
          InfoCommand.Connection = AutoConn;
          InfoCommand.CommandType = CommandType.StoredProcedure;
          InfoCommand.CommandText = "dbo.updateMain";
          InfoCommand.Parameters.AddWithValue("@part", part);
          InfoCommand.Parameters.AddWithValue("@pareto", pareto);
          InfoCommand.CommandTimeout = 180;

          InfoCommand.ExecuteNonQuery();
        }
      }
    }
  }
  catch (Exception e)
  {
    //MessageBox.Show("Error in connection :: " + e);
  }
}

And here's the SQL:

ALTER PROCEDURE [dbo].[updateMain]
    @part varchar(255),
    @Pareto int
as
    UPDATE dbo.ParetoMain 
    SET NewPareto = @Pareto 
    WHERE Part = @part

Nothing fancy as you can see. The problem I have is the Newpareto doesn't have to have a value, so I need it to allow nulls. I made sure the table allows nulls. And in my C# code I made sure to use nullable int, but when I run the code I get the error:

Exception:Thrown: "Procedure or function 'updateMain' expects parameter '@Pareto', which was not supplied." (System.Data.SqlClient.SqlException)
A System.Data.SqlClient.SqlException was thrown: "Procedure or function 'updateMain' expects parameter '@Pareto', which was not supplied."

So how do I stop this error and get the null into the table?

Upvotes: 1

Views: 1980

Answers (3)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

The problem is that the parameter is expected, but not added if the nullable value is null. You need to address this by either:

  1. Manually setting it do DBNull.Value as in: InfoCommand.Parameters.AddWithValue("@Pareto", (object)pareto ?? DbNull.Value);
  2. Or by making the parameter optional as in: @Pareto int = null

Your stored procedure could look like this if you want to make the parameter optional:

ALTER PROCEDURE [dbo].[updateMain]
@part varchar(255),
@Pareto int = null
as
UPDATE dbo.ParetoMain SET NewPareto =@Pareto WHERE Part = @part

EDIT
I take it from the accepted answer that you need to cast to object due to type mismatch problems. I'm fixing my answer for the sake of completeness.

Upvotes: 6

David S.
David S.

Reputation: 6105

use

InfoCommand.Parameters.AddWithValue("@Pareto", (Object)pareto ?? DBNull.Value);

Upvotes: 4

Kramii
Kramii

Reputation: 8419

Try this:

if (pareto != null)
{
  InfoCommand.Parameters.AddWithValue("@pareto", pareto);
}
else
{
  InfoCommand.Parameters.AddWithValue("@pareto", DBNull);
}

Upvotes: 1

Related Questions