Sending Null Value to Sql Server

[Robert]

I need to be able to send a null value into a Sql Server datetime field. The
follolwing code is a snippet of what I am using. I am parsing a flat file
from the main frame and inserting it into a Sql DB. The "departDate can be a
null value in Sql Server. I cannot figure out how to assign a null value to
a SqlDateTime variable and use that variable as part of a SqlParameter, The
error I keep getting is: SqlDateTime overflow. Must be between 1/1/1753
12:00:00 AM and 12/31/9999 11:59:59 PM. Again, I need for the value to be
null.


SqlDateTime departDate;
string departTime = string.Empty;

string depart = input.Substring (137,8);
string departString = depart.Trim();

if(departString == "")
{
departDate = I WANT TO ASSIGN A NULL VALUE HERE
departTime = string.Empty;
}
else
{
departDate = Convert.ToDateT ime(GetDate(inp ut.Substring(13 7,8)));
departTime = input.Substring (145,4);
}

SqlConnection connection = new SqlConnection() ;

ConnectionMgmt cn = new ConnectionMgmt( );
string connectionStrin g =ConfigurationS ettings.AppSett ings["Connection
String"];
connection = cn.GetConnectio n();
object oRes = new object();
oRes = SqlHelper.Execu teScalar(connec tionString,
CommandType.Sto redProcedure, "insertDailyBat ch_sp",
new SqlParameter("@ depart_date", departDate),
new SqlParameter("@ depart_time", departTime),
int nRes = Convert.ToInt32 (oRes);
return nRes;


--
Robert Hill

[Marina]

Re: Sending Null Value to Sql Server

You need to set the parameter's value to DBNull.Value.

"Robert" <rhill938@hotma il.com> wrote in message
news:3CFAA37B-0A5E-422D-9A9F-9E104D444A6E@mi crosoft.com...[color=blue]
>I need to be able to send a null value into a Sql Server datetime field.
>The
> follolwing code is a snippet of what I am using. I am parsing a flat file
> from the main frame and inserting it into a Sql DB. The "departDate can
> be a
> null value in Sql Server. I cannot figure out how to assign a null value
> to
> a SqlDateTime variable and use that variable as part of a SqlParameter,
> The
> error I keep getting is: SqlDateTime overflow. Must be between 1/1/1753
> 12:00:00 AM and 12/31/9999 11:59:59 PM. Again, I need for the value to be
> null.
>
>
> SqlDateTime departDate;
> string departTime = string.Empty;
>
> string depart = input.Substring (137,8);
> string departString = depart.Trim();
>
> if(departString == "")
> {
> departDate = I WANT TO ASSIGN A NULL VALUE HERE
> departTime = string.Empty;
> }
> else
> {
> departDate = Convert.ToDateT ime(GetDate(inp ut.Substring(13 7,8)));
> departTime = input.Substring (145,4);
> }
>
> SqlConnection connection = new SqlConnection() ;
>
> ConnectionMgmt cn = new ConnectionMgmt( );
> string connectionStrin g =ConfigurationS ettings.AppSett ings["Connection
> String"];
> connection = cn.GetConnectio n();
> object oRes = new object();
> oRes = SqlHelper.Execu teScalar(connec tionString,
> CommandType.Sto redProcedure, "insertDailyBat ch_sp",
> new SqlParameter("@ depart_date", departDate),
> new SqlParameter("@ depart_time", departTime),
> int nRes = Convert.ToInt32 (oRes);
> return nRes;
>
>
> --
> Robert Hill
>[/color]

[richlm]

Re: Sending Null Value to Sql Server

You need to assign the value "System.Convert .DBNull" to your SqlParameter.

[Robert]

Re: Sending Null Value to Sql Server

Thank you. You are exactly right. The issue I am having is that I am in a
loop and I need to assign DBNull.Value to a variable prior to using it in the
SqlParameter, such as:

SomeDataType foo = DBNull.Value;

then

new SqlParameter("@ depart_date", foo),




--
Robert Hill



"Marina" wrote:
[color=blue]
> You need to set the parameter's value to DBNull.Value.
>
> "Robert" <rhill938@hotma il.com> wrote in message
> news:3CFAA37B-0A5E-422D-9A9F-9E104D444A6E@mi crosoft.com...[color=green]
> >I need to be able to send a null value into a Sql Server datetime field.
> >The
> > follolwing code is a snippet of what I am using. I am parsing a flat file
> > from the main frame and inserting it into a Sql DB. The "departDate can
> > be a
> > null value in Sql Server. I cannot figure out how to assign a null value
> > to
> > a SqlDateTime variable and use that variable as part of a SqlParameter,
> > The
> > error I keep getting is: SqlDateTime overflow. Must be between 1/1/1753
> > 12:00:00 AM and 12/31/9999 11:59:59 PM. Again, I need for the value to be
> > null.
> >
> >
> > SqlDateTime departDate;
> > string departTime = string.Empty;
> >
> > string depart = input.Substring (137,8);
> > string departString = depart.Trim();
> >
> > if(departString == "")
> > {
> > departDate = I WANT TO ASSIGN A NULL VALUE HERE
> > departTime = string.Empty;
> > }
> > else
> > {
> > departDate = Convert.ToDateT ime(GetDate(inp ut.Substring(13 7,8)));
> > departTime = input.Substring (145,4);
> > }
> >
> > SqlConnection connection = new SqlConnection() ;
> >
> > ConnectionMgmt cn = new ConnectionMgmt( );
> > string connectionStrin g =ConfigurationS ettings.AppSett ings["Connection
> > String"];
> > connection = cn.GetConnectio n();
> > object oRes = new object();
> > oRes = SqlHelper.Execu teScalar(connec tionString,
> > CommandType.Sto redProcedure, "insertDailyBat ch_sp",
> > new SqlParameter("@ depart_date", departDate),
> > new SqlParameter("@ depart_time", departTime),
> > int nRes = Convert.ToInt32 (oRes);
> > return nRes;
> >
> >
> > --
> > Robert Hill
> >[/color]
>
>
>[/color]