Calling StoredProcedure s with Parameters set with null, gives an e

[Renato Vieira]

I get the error 'Procedure 'spBO_HeadlineC reate' 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.Sql Client.SqlConne ction
ConfigurationSe ttings.AppSetti ngs["cnStrDBAdm in"]); // Create connection.
SqlCommand sqlUpdProg=new SqlCommand ();
sqlUpdProg.Comm andType=Command Type.StoredProc edure;
sqlUpdProg.Comm andText="spBO_P rogramSave";
sqlUpdProg.Conn ection=sqlConne ction ;
//Handle the parameters
sqlUpdProg.Para meters.Add("@ti tle", SqlDbType.NVarC har);
sqlUpdProg.Para meters.Add("@im ageid", SqlDbType.BigIn t);
sqlUpdProg.Para meters.Add("@da te", SqlDbType.DateT ime);
sqlUpdProg.Para meters.Add("@te xt", SqlDbType.NText );
sqlUpdProg.Para meters["@title"].Value = _title;
sqlUpdProg.Para meters["@date"].Value = _date;
sqlUpdProg.Para meters["@text"].Value = _body;
if (_imageid!=-1)
sqlUpdProg.Para meters["@imageid"].Value = _imageid;
else
sqlUpdProg.Para meters["@imageid"].Value =null; //error occur
try
{
sqlConnection.O pen();
int statusNews = sqlUpdProg.Exec uteNonQuery();
}
catch (Exception ex)
{
System.Diagnost ics.Debug.Write Line("ERROR: " + ex.ToString());
throw(ex);
}
finally
{
sqlConnection.C lose(); //Close the Connection.
}
--code

--storedproc
CREATE PROCEDURE dbo.spBO_Progra mSave
@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_HeadlineC reate' 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.V alue. 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...