Issue in passing null into sql server 2012 datetime by creating datetime? variable to my stored procedure

Question

This is my business object layer

public class Servicetransactionbol
{
    public Servicetransactionbol()
    {
        st_id = default(int);
        st_startdate = default(DateTime);
        st_enddate = default(DateTime);
    }
       private int st_id;
    private DateTime st_startdate;
    private DateTime st_enddate;

     public int STid
    {
        get { return this.st_id; }
        set { this.st_id = value; }
    }

    public DateTime STstartdate
    {
        get { return this.st_startdate; }
        set { this.st_startdate = value; }
    }

    public DateTime STenddate
    {
        get { return this.st_enddate; }
        set { this.st_enddate = value; }
    }
}

This is my business access layer

public class servicetransactionbal
{
    servicetransactiondal servicetransactionDAL = new servicetransactiondal();

    public void insertTransaction(Servicetransactionbol servicetransactionBOL)
    {
        try
        {

            servicetransactionDAL.Inserttransaction(servicetransactionBOL);
        }
        catch(Exception ex)
        {
            throw ex;
        }
        finally
        {
        }
    }
}

This is my data access layer

public class servicetransactiondal
{
   public void Inserttransaction(Servicetransactionbol servicetransactionBOL)
   {
       try
       {

           Database db = DatabaseFactory.CreateDatabase("GeniusStockTraders");
           DbCommand dbc = db.GetStoredProcCommand("Sproc_service_transaction");              
           db.AddOutParameter(dbc, "srvid", DbType.Int32, servicetransactionBOL.STid);
           db.AddInParameter(dbc, "srv_startdate", DbType.DateTime, servicetransactionBOL.STstartdate);
           db.AddInParameter(dbc, "srv_enddate", DbType.DateTime, servicetransactionBOL.STenddate);
           db.AddInParameter(dbc, "ttype", DbType.String, "insert_transaction");
           db.ExecuteNonQuery(dbc);
           dbc.Dispose();
       }
       catch (Exception ex)
       {
           throw ex;
       }
       finally
       {

       }

   }
 }

and finally this is my code behind

    SqlDateTime? enddate;
    protected void btn_Save_Click(object sender, EventArgs e)
    {
        try
        {
            Database db = DatabaseFactory.CreateDatabase("GeniusStockTraders");
            servicetransactionBOL.STid = default(int);
            servicetransactionBOL.STscript = txt_script.Text;
            servicetransactionBOL.STstartdate = Convert.ToDateTime(txt_startdate.Text);
            if (string.IsNullOrEmpty(txt_enddate.Text))
            {
                enddate = SqlDateTime.Null;
                servicetransactionBOL.STenddate = (DateTime)enddate;

            }
            else
            {
                servicetransactionBOL.STenddate = Convert.ToDateTime(txt_enddate.Text);
            }

With this code I am able to insert min value of datetime when my text field is empty but I want to insert NULL when my text field is empty. I tried by making datetime as datetime? in my business object layer then m able to insert NULL value to my enddate in LOCALHOST but when I upload same files online its throwing an exception

Method Not Found:'System.datetime GeniusstocktradersBOL.ServeicetransactionBOL.get_STenddate().

Please help me to resolve this issue.

thank you

Answer 1

A parameter with a Value of null : is not sent. If you are getting issues about variables / parameters that aren't defined, you must instead replace null with DBNull.Value. Yes, this is annoying. For example:

DateTime? someValue = ... // could be null
db.AddInParameter(dbc, "some_name", DbType.DateTime,
    (object)someValue ?? DBNull.Value);

Note that in the example you provide, both date-times are DateTime, not DateTime?, but it applies in the more general case.

As for:

Method Not Found:'System.datetime GeniusstocktradersBOL.ServeicetransactionBOL.get_STenddate().

That is nothing to do with null values, and simply suggests that you haven't fully built/deployed the dlls, i.e. that the server has a version of the dll without that property accessor.

Answer 2

use a case statement in the insert when Date=MIN('1/1/1900') then NULL, pass '1/1/1900' as the "Empty" value.

Turning this into an answer as OP said it solved the problem.