Calling StoredProcedures with Parameters set with null, gives an e

[Renato Vieira]

I get the error 'Procedure 'spBO_HeadlineCreate' expects parameter
'@imageid', which was not supplied' when i call the procedure with the
@imageid=null, but id the @imageid as a diff. value no error occurs.
Here's the code and the storedproc...

-- code
SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection
ConfigurationSettings.AppSettings["cnStrDBAdmin"]); // Create connection.
SqlCommand sqlUpdProg=new SqlCommand ();
sqlUpdProg.CommandType=CommandType.StoredProcedure ;
sqlUpdProg.CommandText="spBO_ProgramSave";
sqlUpdProg.Connection=sqlConnection ;
//Handle the parameters
sqlUpdProg.Parameters.Add("@title", SqlDbType.NVarChar);
sqlUpdProg.Parameters.Add("@imageid", SqlDbType.BigInt);
sqlUpdProg.Parameters.Add("@date", SqlDbType.DateTime);
sqlUpdProg.Parameters.Add("@text", SqlDbType.NText );
sqlUpdProg.Parameters["@title"].Value = _title;
sqlUpdProg.Parameters["@date"].Value = _date;
sqlUpdProg.Parameters["@text"].Value = _body;
if (_imageid!=-1)
sqlUpdProg.Parameters["@imageid"].Value = _imageid;
else
sqlUpdProg.Parameters["@imageid"].Value =null; //error occur
try
{
sqlConnection.Open();
int statusNews = sqlUpdProg.ExecuteNonQuery();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("ERROR: " + ex.ToString());
throw(ex);
}
finally
{
sqlConnection.Close(); //Close the Connection.
}
--code

--storedproc
CREATE PROCEDURE dbo.spBO_ProgramSave
@date smalldatetime,
@title nvarchar(100),
@imageid bigint,
@text ntext
AS
SET NOCOUNT ON
UPDATE dbo.t_programs SET
[date] = @date,
title = @title,
imageid = @imageid,
[text] = @text
GO
--storedproc

i would appreciate any help, thanks a lot
--
Renato /*Portugal*/ Vieira

[Tom Porterfield]

On Thu, 27 Jan 2005 07:41:05 -0800, Renato Vieira wrote:

I get the error 'Procedure 'spBO_HeadlineCreate' expects parameter
'@imageid', which was not supplied' when i call the procedure with the
@imageid=null, but id the @imageid as a diff. value no error occurs.

If you need to indicate null, try passing DBNull.Value instead of null.
--
Tom Porterfield

[Brian Brown]

Hi Renato,

Try setting the imageID parameter value to System.DBNull.Value. This class
differentiates between a null object and an unitialized value (NULL in the
db).

Please reply to this post if this does not work and I will relook at the
problem.

I hope this helps.
-----------------------------

[Renato Vieira]

Thank's a lot Brian and Tom, that solution worked.

Be good...