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

Simon

[Peter Bromberg [C# MVP]]

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

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Simon Harvey" wrote:

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

Simon

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

"Simon Harvey" <no******@hotmail.com> wrote in message
news:94*************************@news.microsoft.co m...

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

Simon