Simple problem passing nulls with ADO.net 2.0

[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

 

[Guest]

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

 

[William \(Bill\) Vaughn]

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.
__________________________________

 

[Otis Mukinfus]

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!

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

 

[Simon Harvey]

Many thanks to you all for your help