Jump to content


Check out our Community Blogs





- - - - -

Null Dates in SQL Stored Procedure called from C# – Conversion Error DateTime2 DateTime

  Posted by Barnsite, 29 November 2012 · 1397 views

programming
One issue with calling stored procedures from C# is with null date parameters. If you try to pass an unassigned DateTime type to the parameter you will receive an error regarding conversion of DateTime2 to DateTime data type.
One way around this is to use the Nullable DateTime in C#
Lets assume we have the following table (dbo.Fault):
WO_NUMBER nvarchar(50) not null
COMP_DT smalldatetime null
And an Insert Stored Procedure


CREATE PROC [dbo].[up_add_fault]
(
@WO_NUMBER AS VARCHAR(50),
@COMP_DT AS SMALLDATETIME
)AS

INSERT INTO dbo.FAULT
(
WO_NUMBER,
COMP_DT
)
VALUES
(
@WO_NUMBER,
@COMP_DT
)
END


If we try to send an unassigned DateTime varible to the @COMP_DT parameter we will get the error, as an unassigned DateTime value is not null.
So we need to declare our variable as a nullable DateTime type. In C# we use DateTime? to do this. We can then test the HasValue of the variable, and depending on the result either pass the variable, or pass DBNull.Value


private DateTime? compDate;
private string woNumber;

//do something to assign values to the varibles here...
//call saveFault

saveFault(woNumber, compDate)


private void saveFault(string woNumber, DateTime? compDate)
{
//Set up database connection & stored proc
OleDbConnection dbCon = new OleDbConnection(dbConnectionString);
dbCon.Open();
string dbSQL = "dbo.up_add_fault";
OleDbCommand dbCom = new OleDbCommand(dbSQL, dbCon);
dbCom.CommandType = CommandType.StoredProcedure;

//add the WO_NUMBER parameter
dbCom.Parameters.AddWithValue("@WO_NUMBER", woNumber);

//test the compDate to see if it has a value - if yes use it else use use DBNull
if (compDate.HasValue)
{
dbCom.Parameters.AddWithValue("@COMP_DT", compDate);
}
else
{
dbCom.Parameters.AddWithValue("@COMP_DT", DBNull.Value);
}

//Execute the Stored Proc
dbCom.ExecuteNonQuery();
dbCon.Close();
}

http://feeds.wordpre...dpress.com/481/ Posted Image

Source

  • 0



Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download