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

Is your DateTime column marked to allow NULL in the database table? — GregC
– GregC, Commented Jul 8, 2014 at 12:05
Your databases server versions are not same.. production and development server varies — azhar_SE_nextbridge
– azhar_SE_nextbridge, Commented Jul 8, 2014 at 12:08
use a case statement in the insert when Date=MIN('1/1/1900') then NULL, pass '1/1/1900' as the "Empty" value — Daniel E.
– Daniel E., Commented Jul 8, 2014 at 12:16
@GregC yes i marked as allow null to my datetime column in my schema.. — msdboss
– msdboss, Commented Jul 8, 2014 at 13:13
If you're getting "method not found" then it is probably a deployment issue. Make sure you're deploying all of the assemblies that you compiled for this project. Also, not that C# is a case-sensitive language, and the casing of what you posted here does not line up. — Matt Johnson-Pint
– Matt Johnson-Pint, Commented Jul 8, 2014 at 16:07

Marc Gravell · Accepted Answer · 2014-08-13 09:24:28Z

2

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.

edited Aug 13, 2014 at 9:24

answered Aug 13, 2014 at 9:18

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel E. · Accepted Answer · 2014-08-13 09:14:59Z

1

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.

answered Aug 13, 2014 at 9:14

Daniel E.

2,0793 gold badges22 silver badges28 bronze badges

1 Comment

Marc Gravell Over a year ago

That's a really bad solution, IMO; if you mean null: send null. There is no need to invent additional sentinel values.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related