Sending Null Value to Sql Server

[Guest]

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.ToDateTime(GetDate(input.Substring(137,8)));
departTime = input.Substring(145,4);
}

SqlConnection connection = new SqlConnection();

ConnectionMgmt cn = new ConnectionMgmt();
string connectionString =ConfigurationSettings.AppSettings["Connection
String"];
connection = cn.GetConnection();
object oRes = new object();
oRes = SqlHelper.ExecuteScalar(connectionString,
CommandType.StoredProcedure, "insertDailyBatch_sp",
new SqlParameter("@depart_date", departDate),
new SqlParameter("@depart_time", departTime),
int nRes = Convert.ToInt32(oRes);
return nRes;

 

[Marina]

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

 

[richlm]

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

 

[Guest]

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

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

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.ToDateTime(GetDate(input.Substring(137,8)));
departTime = input.Substring(145,4);
}

SqlConnection connection = new SqlConnection();

ConnectionMgmt cn = new ConnectionMgmt();
string connectionString =ConfigurationSettings.AppSettings["Connection
String"];
connection = cn.GetConnection();
object oRes = new object();
oRes = SqlHelper.ExecuteScalar(connectionString,
CommandType.StoredProcedure, "insertDailyBatch_sp",
new SqlParameter("@depart_date", departDate),
new SqlParameter("@depart_time", departTime),
int nRes = Convert.ToInt32(oRes);
return nRes;