CODEWITHSUNDEEP
c#dapper
Hexxed
Hexxed

Reputation: 693

Error "Implicit conversion from data type sql_variant to datetime is not allowed" occurred while inserting data to database using dapper

I have a repository class with a method

using Dapper;
public int Execute(string query, object arguments)
    {
        return Connection.Execute(query,arguments);
    }

In another program, I try to run an insert query. 

dbOps.Execute(qry, new
                            {
                                transType = unp.TransType,
                                itemCode = unp.ItemCode,
                                itemQty = unp.ItemQty,
                                itemUM = unp.UnitMeasure,
                                itemSite = unp.ItemSite,
                                itemLoc1 = unp.ItemLocation1,
                                itemLoc2 = unp.ItemLocation2,
                                transRmk = unp.TransRmks,
                                transEffDate = unp.TransEffDate, //is DateTime
                                transDate = unp.TransDate, //is DateTime
                                cimStatus = unp.CimStatus,
                                cimBatchNo = unp.CimBatchNo,
                                transID = unp.TransID,
                                cimLoadDate = unp.CimLoadDate
                            });

unp Class:

internal class unp
    {
        public string TransType { get; set; }
        public string ItemCode { get; set; }
        public decimal ItemQty { get; set; }
        public string UnitMeasure { get; set; }
        public string ItemSite { get; set; }
        public string ItemLocation1 { get; set; }
        public string ItemLocation2 { get; set; }
        public string TransRmks { get; set; }
        public DateTime TransEffDate { get; set; }
        public DateTime TransDate { get; set; }
        public byte CimStatus { get; set; }
        public string CimBatchNo { get; set; }
        public string TransID { get; set; }
        public object CimLoadDate { get; set; }

        public string SourceFile { get; set; }
        public string JournalId { get; set; }
    }

Query String:

string qry = @"INSERT INTO [Transaction]
                                 VALUES(@transType,@itemCode,@itemQty,@itemUM,@itemSite,@itemLoc1,@itemLoc2,@transRmk,
                                 @transEffDate,@transDate,@cimStatus,@cimBatchNo,@transID,@cimLoadDate)"

Im getting an exception:

Implicit conversion from data type sql_variant to datetime is not allowed. Use the CONVERT function to run this query.

Any Ideas I can resolve this? or Do I have to impose Dapper to multiple projects?

Upvotes: 3

Views: 4452

Answers (2)

Meer
Meer

Reputation: 2845

Try this .

change type of CimLoadDate from object type to DateTime. because it is dateTime in your database

public Datetime CimLoadDate { get; set; }

Upvotes: 4

achu
achu

Reputation: 43

In your query columns of table may mismatches .Use this link it may useful

Error of Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query

Upvotes: -1

Related Questions