471,594 Members | 1,780 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,594 software developers and data experts.

Convert Null to DBNull

Hello,
I have a function that expects the following parms. How can I handle null
values. For example, if FirstName comes through as null, how can i propogate
that null value to the stored procedure.

public long InsertPerson(string FirstName, string LastName, string
MiddleName, string EMail, long PersonTypeId,
string HomePhone, string CellPhone, bool EmailOptOut, string City,
long StateId, string PostalCode, string Street, long CountryId, long
InsertUserId)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("ks_Person_InsertPerson", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value =
LastName;
Feb 17 '07 #1
6 12717
On Fri, 16 Feb 2007 20:25:08 -0500, "scott ocamb" <so****@kiinc.comwrote:
>Hello,
I have a function that expects the following parms. How can I handle null
values. For example, if FirstName comes through as null, how can i propogate
that null value to the stored procedure.

public long InsertPerson(string FirstName, string LastName, string
MiddleName, string EMail, long PersonTypeId,
string HomePhone, string CellPhone, bool EmailOptOut, string City,
long StateId, string PostalCode, string Street, long CountryId, long
InsertUserId)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("ks_Person_InsertPerson", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value =
LastName;
if(string.IsNullOrEmpty(FirstName))
{
cmd.Parameters.AddWithValue("@FirstName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@FirstName", FirstName);
}

There may be typos here, but you get the idea...

Good luck with your project,

Otis Mukinfus

http://www.otismukinfus.com
http://www.arltex.com
http://www.tomchilders.com
http://www.n5ge.com
Feb 17 '07 #2
Hi,

"scott ocamb" <so****@kiinc.comwrote in message
news:%2******************@TK2MSFTNGP06.phx.gbl...
Hello,
I have a function that expects the following parms. How can I handle null
values. For example, if FirstName comes through as null, how can i
propogate that null value to the stored procedure.

Easily:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
== null? DBNull.Value: FirstName;


Feb 17 '07 #3
Ignacio Machin ( .NET/ C# MVP ) wrote:
Hi,

"scott ocamb" <so****@kiinc.comwrote in message
news:%2******************@TK2MSFTNGP06.phx.gbl...
>Hello,
I have a function that expects the following parms. How can I handle null
values. For example, if FirstName comes through as null, how can i
propogate that null value to the stored procedure.


Easily:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
== null? DBNull.Value: FirstName;
Or even easier:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName ?? DBNull.Value;

:)
--
Göran Andersson
_____
http://www.guffa.com
Feb 17 '07 #4
Thanks for your help, but i get compile errors.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName ??
DBNull.Value;
Error 1 ) expected C:\KI\Kinesis\Code2\CoachwareEntities\Athlete.cs 176 27
CoachwareEntities

seems to be some sort of casting error.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = (FirstName
?? DBNull.Value);

Error 3 Operator '??' cannot be applied to operands of type 'string' and
'System.DBNull' C:\KI\Kinesis\Code2\CoachwareDAL\Person.cs 94 87
CoachwareDAL

"Göran Andersson" <gu***@guffa.comwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...
Ignacio Machin ( .NET/ C# MVP ) wrote:
>Hi,

"scott ocamb" <so****@kiinc.comwrote in message
news:%2******************@TK2MSFTNGP06.phx.gbl. ..
>>Hello,
I have a function that expects the following parms. How can I handle
null values. For example, if FirstName comes through as null, how can i
propogate that null value to the stored procedure.


Easily:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName == null? DBNull.Value: FirstName;

Or even easier:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
?? DBNull.Value;

:)
--
Göran Andersson
_____
http://www.guffa.com

Feb 17 '07 #5
Right. The compiler can't find any common type for the operands, so you
have to specify it:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName ?? (object)DBNull.Value;

By specifying one operand as object, it will see that the other operand
also can be cast to that type.

scott ocamb wrote:
Thanks for your help, but i get compile errors.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName ??
DBNull.Value;
Error 1 ) expected C:\KI\Kinesis\Code2\CoachwareEntities\Athlete.cs 176 27
CoachwareEntities

seems to be some sort of casting error.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = (FirstName
?? DBNull.Value);

Error 3 Operator '??' cannot be applied to operands of type 'string' and
'System.DBNull' C:\KI\Kinesis\Code2\CoachwareDAL\Person.cs 94 87
CoachwareDAL

"Göran Andersson" <gu***@guffa.comwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...
>Ignacio Machin ( .NET/ C# MVP ) wrote:
>>Hi,

"scott ocamb" <so****@kiinc.comwrote in message
news:%2******************@TK2MSFTNGP06.phx.gbl.. .
Hello,
I have a function that expects the following parms. How can I handle
null values. For example, if FirstName comes through as null, how can i
propogate that null value to the stored procedure.

Easily:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName == null? DBNull.Value: FirstName;
Or even easier:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
?? DBNull.Value;

:)
--
Göran Andersson
_____
http://www.guffa.com


--
Göran Andersson
_____
http://www.guffa.com
Feb 17 '07 #6
thanks,

you said it was simple, it is only simple once you know how,

i learned something today, thanks : )

"Göran Andersson" <gu***@guffa.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
Right. The compiler can't find any common type for the operands, so you
have to specify it:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
?? (object)DBNull.Value;

By specifying one operand as object, it will see that the other operand
also can be cast to that type.

scott ocamb wrote:
>Thanks for your help, but i get compile errors.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
?? DBNull.Value;
Error 1 ) expected C:\KI\Kinesis\Code2\CoachwareEntities\Athlete.cs 176
27 CoachwareEntities

seems to be some sort of casting error.

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
(FirstName ?? DBNull.Value);

Error 3 Operator '??' cannot be applied to operands of type 'string' and
'System.DBNull' C:\KI\Kinesis\Code2\CoachwareDAL\Person.cs 94 87
CoachwareDAL

"Göran Andersson" <gu***@guffa.comwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...
>>Ignacio Machin ( .NET/ C# MVP ) wrote:
Hi,

"scott ocamb" <so****@kiinc.comwrote in message
news:%2******************@TK2MSFTNGP06.phx.gbl. ..
Hello,
I have a function that expects the following parms. How can I handle
null values. For example, if FirstName comes through as null, how can
i propogate that null value to the stored procedure.

Easily:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName == null? DBNull.Value: FirstName;

Or even easier:

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value =
FirstName ?? DBNull.Value;

:)
--
Göran Andersson
_____
http://www.guffa.com



--
Göran Andersson
_____
http://www.guffa.com

Feb 18 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Vicky | last post: by
22 posts views Thread by Christoph Boget | last post: by
8 posts views Thread by Jarod | last post: by
6 posts views Thread by Dean Slindee | last post: by
12 posts views Thread by Brian Henry | last post: by
10 posts views Thread by =?Utf-8?B?R3JlZw==?= | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.