CODEWITHSUNDEEP
c#nexusdb
Pancheti
Pancheti

Reputation: 217

Unable to insert date-time as null values to Nexus Database from c# dapper code

Getting below issue while inserting the DateTime value as null into Nexus Db using a dapper class to nexus Database.

public const string SqlQuery = @"INSERT INTO Test(test1, test2, test3, 
Date1,Date2))
                                                       Values(?test1?,? 
test2?,?date1?,?date2?)";

 public void InsertTest(string test1,string test2, DateTime? date1,DateTime? 
date2)
{
var params= new DynamicParameters(
            new
            {
test1= "",
test2 ="",
Date1 = cDate.HasValue ? cDate.Value.Date : (DateTime?)null,
Date2 = cDate1.HasValue ? cDate2.Value.Date : (DateTime?)null,

}
ExecConn(SqlQuery , params);
}

ERROR [HY000] The query returned an error (ODBC State: HY000)

Error: Invalid argument to date encode

Query: t 60000; INSERT INTO Test(test1, test2, test3, Date1,Date2) Values(:Param1,:Param2,:Param3,:Param4,:Param5)

Upvotes: 2

Views: 841

Answers (2)

Mounika
Mounika

Reputation: 51

Try to use DateTime.MinValue instead of (DateTime?)null

public void InsertTest(string test1,string test2, DateTime? date1,DateTime? 
date2)
{
 var params= new DynamicParameters(
        new
        {enter code here
 test1= "",
 test2 ="",`enter code here`
 Date1 = cDate.HasValue ? cDate.Value.Date :   DateTime.MinValue.Date,
 Date2 = cDate1.HasValue ? cDate2.Value.Date :   DateTime.MinValue.Date,

 }
 ExecConn(SqlQuery , params);
 }

Upvotes: 2

BugFinder
BugFinder

Reputation: 17868

Normally at the db layer (for oracle and MSSQL) we have to do something like this

(PS this is grabbed from my code where I also had to allow for the fact that some halfwit types things like 31/2/2018 in excel!)

This turns it into a DBNull.Value if actually null or invalid. Which should then work.

       private object dtfix(object o)
        {
            if (!(o is DateTime))
            {
                return null;
            }
            else
            {
                try
                {
                    DateTime x = (DateTime)o;
                    x.AddDays(1);
                }
                catch
                {
                    return null;
                }
                return o;
            }
        }

param = new SqlParameter("duedate", SqlDbType.Date);
param.Value = dtfix(myparm) ?? DBNull.Value;

Upvotes: 0

Related Questions