Simple problem passing nulls with ADO.net 2.0

  • Thread starter Thread starter Simon Harvey
  • Start date Start date
S

Simon Harvey

Hi everyone,

I'm having a really simple problem - I can't seem to insert a null value
into the database. When I do it tells me that the procedure expects the parameter
and that I'm not providing it.
Well, I am, its just a null value!

The code is:

con.Open();
trans = con.BeginTransaction();

cmd = new SqlCommand("UpdateProperty", con, trans);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@id", id));
cmd.Parameters.Add(new SqlParameter("@propertyName", propertyName));
cmd.Parameters.Add(new SqlParameter("@city", null));
// THE PROBLEM

cmd.ExecuteNonQuery();



When executing the command, it throws an exception that says that the procedure
expects the @city parameter.

Can anyone tell me how to send the database a null value! The column definition
in the database is more than happy to take nulls

Thanks to anyone who can help

Kindest Regards

Simo
 
Simon,
The C# "null" keyword is not the same as System.DbNull.Value. You might try
that. It still depends on how the database is set up, and also,how the stored
proc is written.
Peter
 
Try passing DbNull.Value.
null in C# means "don't set the Parameter value" so SQL Server assumes you
mean to take the Parameter default.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi everyone,

I'm having a really simple problem - I can't seem to insert a null value
into the database. When I do it tells me that the procedure expects the parameter
and that I'm not providing it.
Well, I am, its just a null value!
Click to expand...

Peter and Bill are both correct, but another solution is to give the
parameter in the stored procedure the default value of NULL thus:

@YourParam int = NULL

Then The procedure will assign the vale of null to the parameter if it
is not included in the input.

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com
 
Back
Top