ADO.NET: Send procedure parameter with null value

I need to send a parameter a null value to the procedure, but I get an error:

System.Data.SqlClient.SqlException: ‘Procedure or function ‘sel_mizanyeni’ expects parameter ‘@subeno’, which was not supplied.’

When I run the same procedure with the same parameters in SQL Server, it does not cause an error. When I run it with ADO.NET in C#, I get that error.

Running state in SQL Server:

exec sel_mizanyeni 
     @subeno=null,
     @tarih='2021-12-28 00:00:00',
     @kirilimlevel=8,
     @detaylevel=3,
     @hesap1=N'342',
     @hesap2=N'343',
     @dovizcinsi=0,
     @bakiyeverenler=1,
     @optnetbakiye=1,
     @optdisticsir=0,
     @opteval=0,
     @kurtarihi='2021-12-28 00:00:00',
     @yilsonu=0,
     @firmano=NULL,
     @ekno=NULL,
     @ikitariharasi=0,
     @bittarih='2021-12-28 00:00:00',
     @migration=1

This code is not working in C#:

SqlCommand cmd = new SqlCommand("sel_mizanyeni", baglanti);
cmd.CommandType = CommandType.StoredProcedure;

// I need to pass NULL here, but I get the mentioned error
cmd.Parameters.AddWithValue("@subeno", null);

cmd.Parameters.AddWithValue("@tarih", "2021-12-28 00:00:00");
cmd.Parameters.AddWithValue("@kirilimlevel", 8);
cmd.Parameters.AddWithValue("@detaylevel", 3);
cmd.Parameters.AddWithValue("@hesap1", "342");
cmd.Parameters.AddWithValue("@hesap2", "343");
cmd.Parameters.AddWithValue("@dovizcinsi", 0);
cmd.Parameters.AddWithValue("@bakiyeverenler", 1);
cmd.Parameters.AddWithValue("@optnetbakiye", 1);
cmd.Parameters.AddWithValue("@optdisticsir", 0);
cmd.Parameters.AddWithValue("@opteval", 0);
cmd.Parameters.AddWithValue("@kurtarihi", "2021-12-28 00:00:00");
cmd.Parameters.AddWithValue("@yilsonu", 0);

cmd.Parameters.AddWithValue("@firmano", null);
cmd.Parameters.AddWithValue("@ekno", null);

cmd.Parameters.AddWithValue("@ikitariharasi", 0);
cmd.Parameters.AddWithValue("@bittarih", "2021-12-28 00:00:00");
cmd.Parameters.AddWithValue("migration", 1);

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

dataGridView1.DataSource = ds.Tables[0];

Answer

From Specifying parameter data types – ADO.NET documentation,

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

Hence, use DBNull.Value instead of null for the parameter’s value.

Advertisement

Answer