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];
2条答案
按热度按时间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 ofnull
for the parameter's value.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.