SQL Server ADO.NET: Send procedure parameter with null value

gcuhipw9  于 2023-05-05  发布在  .NET
关注(0)|答案(2)|浏览(210)

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];
smdncfj3

smdncfj31#

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.

tgabmvqs

tgabmvqs2#

If you're able to change the stored proc, can always do @subeno TheType = null .. in its declaration, then it won't complain about not supplying param value.

相关问题