Using C# 3.5, accessing SQL Server to execute a stored procedure with a number of parameters of various data types (int, money(decimal), datetime, string). Some of these parameters can have null values. When I execute the stored procedure using the query analyzer, the proc works OK. When I execute the stored procedurefrom within my c# program using ASO.NET I get back an error message the a parameter was not supplied. It is always the first parameter with the null value. If I give that parameter a value,I get a messagenot about that parameter but about the next one which is now the first with a null value

Following is a code snippet used to create an SqlParameter:

public static SqlParameter MakeInputParam(string ParamName, decimal? Value)

{

SqlParameter param = new SqlParameter(ParamName, SqlDbType.Decimal);

param.Direction =

ParameterDirection.Input;

param.IsNullable =

true;

if (Value == null)

{param.Value =

null; }

else

{param.Value = (

decimal)Value;}

return param;

}

---

Developer

Try setting the param.Value to DBNull.Value instead of null when the Value is null.

Hope this helps.

---

www.insteptech.com ; msmvps.com/blogs/deborahk
We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

Hello

Deborahkis right. When sending a null value as a Parameter value in a command to the database, you cannot use null (Nothing in Visual Basic® .NET). Instead you need to use DBNull.Value. For example:

'Visual Basic
Dim param As SqlParameter = New SqlParameter("@Name", SqlDbType.NVarChar, 20)
param.Value = DBNull.Value

//C#
SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar, 20);
param.Value = DBNull.Value;

The point was documented in Best Practices for Using ADO.NET.

Regards,
Jialiang Ge

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.