SQL allow null in table in C#

Question

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?

Also ignore the "using sqlAdapter", i dont use it anymore for this query — lemunk
– lemunk, Commented Apr 15, 2013 at 12:25
eh? i thought it didnt matter about case sens in sql? normally I try to stick to case sensitivity in my c# code as good practice, but sql i always thought wasnt? — lemunk
– lemunk, Commented Apr 15, 2013 at 12:44
although i cant see any issues there, i can understand where your coming from, although it isn't case sensative and this case isnt an issue, it is always good practice to keep case sensitivity. Ill Update my query and code to better represent this. — lemunk
– lemunk, Commented Apr 15, 2013 at 13:00
Sorry for the confusion - you're right, the SQL parameter names of a stored procedure are NOT case sensitive, even when being called from a C# application; just tested and verified. — marc_s
– marc_s, Commented Apr 15, 2013 at 16:07

Thorsten Dittmar · Accepted Answer · 2013-04-15 13:04:18Z

6

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

Manually setting it do DBNull.Value as in: InfoCommand.Parameters.AddWithValue("@Pareto", (object)pareto ?? DbNull.Value);
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.

edited Apr 15, 2013 at 13:04

answered Apr 15, 2013 at 12:30

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lemunk Over a year ago

exactly yes really good answer wish i could accept more than one but that would defy the meaning of answer lol, +1 anyway for great advice!

David S. · Accepted Answer · 2013-04-15 12:33:41Z

4

use

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

edited Apr 15, 2013 at 12:33

answered Apr 15, 2013 at 12:28

David S.

6,1432 gold badges45 silver badges83 bronze badges

5 Comments

lemunk Over a year ago

problem here is conversion error of type nullable int to dbnull its says

David S. Over a year ago

@StevenSmith Ah, you need to do (Object)pareto ?? DBNull.Value (I edited answer)

lemunk Over a year ago

perfect so problem was trying to add the nullable value as the paramter, but because its null it doesnt get used? thus using dbnull as the parameter?

David S. Over a year ago

@StevenSmith Exactly, plus sql server doesn't have a concept of c# null, only its own kind of null, which DBNull.Value represents.

lemunk Over a year ago

ahhh so thats why, i always thought null was generic to all languages and dbnull was a special type of null that DBOs distinctly recognise....wow, the more you know! nice one David!

Kramii · Accepted Answer · 2013-04-15 12:29:58Z

1

Try this:

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

answered Apr 15, 2013 at 12:29

Kramii

8,4094 gold badges35 silver badges39 bronze badges

1 Comment

lemunk Over a year ago

yeh didnt know why i didnt think of a condition hehe

Collectives™ on Stack Overflow

SQL allow null in table in C#

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related