Passing nulls to cmd parameters

[dew]

I am using sql to modify data, using parameters. However, if I set the
datatime to any other than a varchar, it bombs if the user enters a null
value. How am I supposed to handle this. Set param.Value to ???

param=cmd.parameters.add("@DueDate", sqldbtype.smalldatetime)
param.Value = txtDueDate.text

Thanks for your help.

 

[Bob Barrows [MVP]]

I am using sql to modify data, using parameters. However, if I set
the datatime to any other than a varchar, it bombs if the user enters
a null value. How am I supposed to handle this. Set param.Value to
???

param=cmd.parameters.add("@DueDate", sqldbtype.smalldatetime)
param.Value = txtDueDate.text

Thanks for your help

..
Set it to DbNull

 

[S. Justin Gengo [MCP]]

Or don't add it at all. Same effect.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[Bob Barrows [MVP]]

This will only have the same effect if it's an optional parameter, correct?
If the parameter has not been assigned a default value, then you must create
the parameter object and pass a value, even a Null, otherwise you'll get the
"expected parameter was not supplied" error.
Bob Barrows

 

[S. Justin Gengo [MCP]]

Bob,

If the database field that the parameter maps to accepts a null value then
not including the parameter just leaves the field null. Setting a SQL field
to allow nulls is the same as giving it NULL as the default. This way even
if a stored procedure has a field declared if that field isn't sent to the
stored procedure a null value will be inserted.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[Bob Barrows [MVP]]

No, that's not what I'm talking about. i'm talking about the parameter
declaration section in the CREATE PROCEDURE statement. If you do not give a
parameter a default value when declaring it, then that parameter is
required. You must create a parameter and pass a value (even Null) when
executing it with a SQLCommand object.

CREATE PROCEDURE myproc (
@requiredparm datetime) AS ...

vs

CREATE PROCEDURE myproc (
@optionalparm datetime=Null) AS ...

With the second version, you can just leave out the parameter when executing
the procedure. With the first version, failing to create and append the
SQLParameter will result in the error I referred to.

Bob

 

[S. Justin Gengo [MCP]]

Bob,

Yes, that's correct. I think we were just talking about two different
things. You are absolutely correct in stating that if a SQL parameter may be
set to null and you want to acheive that by not including the parameter in
the SqlCommand that the stored procedure should have that parameter set to
null by default.

--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche