Implicit conversion from data type datetime to int is not allowed

Question

I have a problem with a stored procedure while running from Entity Framework, using Visual Studio 2014 and SQL Server 2015.

In C# I'm trying to execute it like this:

db.Database.ExecuteSqlCommand(Query,Param.ToArray())

This is the stored procedure:

ALTER PROCEDURE [dbo].[InsertPurchaseDetails]
    @SupplierId int,
    @GatePassId int = null,
    @GrnDate date = null,
    @InvoiceNo varchar(50) = null,
    @InvoiceDate date = null,
    @Dc varchar(50) = null,
    @InvoiceValue Decimal(9,2) = null,
    @Packing decimal(9,2) = null,
    @Forwarding decimal(9,2) = null,
    @Others decimal(9,2) = null,
    @Freight decimal(9,2) = null,
    @Discount decimal(9,2) = null,
    @AccHeaderId smallint = null,
    @Narriation varchar(150) = null 
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @id AS int
    DECLARE @toady AS date
    SET @toady = GETDATE()

    IF @SupplierId IS NULL OR @GrnDate IS NULL
    BEGIN
        RAISERROR (15600, -1, -1, 'InsertPurchaseDetails')
    END

    BEGIN TRAN
        BEGIN TRY

        INSERT INTO dbo.Purchases (SupplierId, GatePassId, GrnDate, InvoiceNo, InvoiceDate, Dc, InvoiceValue, Packing, Forwarding, Others, Freight, Discount, AccHeaderId, Narriation, CreatedAt)
        VALUES (@SupplierId, @GatePassId, @GrnDate, @InvoiceNo, @InvoiceDate, @Dc, @InvoiceValue, @Packing, @Forwarding, @Others, @Freight, @Discount, @AccHeaderId, @Narriation, @toady)

        SET @id = SCOPE_IDENTITY()      

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION

        INSERT INTO dbo.DB_ERRORS 
        VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE());

        SELECT ERROR_NUMBER(), ERROR_MESSAGE();
    END CATCH
END

I see this SQL query being executed in SQL Server Profiler:

exec sp_executesql N'InsertPurchaseDetails @SupplierId,@GrnDate',N'@SupplierId int,@GrnDate datetime',@SupplierId=2,@GrnDate='2021-06-29 00:00:00'

and I get this Error:

Msg 257, Level 16, State 3, Procedure InsertPurchaseDetails, Line 0
Implicit conversion from data type datetime to int is not allowed. > Use the CONVERT function to run this query.

And what part of the error message do you not understand? It seems pretty clear. Do you have a question? — Gordon Linoff, Jun 30 '21 at 00:05
when i run the stored procedure getting error - Implicit conversion from data type datetime to int is not allowed but i dont know from where this error arrives. — Prem Kumar, Jun 30 '21 at 00:07
There is no SQL Server **2015** version - only 2012, 2014, 2016, 2017 and 2019 - take your pick — marc_s, Oct 12 '21 at 05:36

score 1 · Answer 1 · answered Jun 30 '21 at 12:12

The reason you are getting this is that the command is not being sent correctly.

It is being sent CommandType: CommandType.Text, so it is being passed as an ad-hoc batch.

The batch is as follows

InsertPurchaseDetails @SupplierId,@GrnDate

This means that the paramters are being passed by position, and @GrnDate variable is being passed into the @GatePassId parameter

When executed with parameters, is passed directly to sp_executesql, and looks like this in the profiler (note this exact text is not passed, the data is passed in binary format).

exec sp_executesql N'InsertPurchaseDetails @SupplierId,@GrnDate',N'@SupplierId int,@GrnDate datetime',@SupplierId=2,@GrnDate='2021-06-29 00:00:00'

What you need is CommandType: CommandType.StoredProcedure.

Then the command will be sent something like this (again the values are sent as binary, not text)

exec InsertPurchaseDetails @SupplierId=2,@GrnDate='2021-06-29 00:00:00'

How you would get this to happen depends on what version of EF you are using. Also, the query text would just be InsertPurchaseDetails, with no text for the parameters, just pass those through as SqlParameters.

Thanks @charlieface but i am getting the following error while adding CommandType.StoredProcedure - Exception: No mapping exists from object type System.Data.SqlClient.SqlParameter[] to a known managed provider native type. how to solve this i am new to asp.net please suggest — Prem Kumar, Jun 30 '21 at 16:09
List Param = new List(){ new SqlParameter { ParameterName = "@SupplierId", Value = Convert.ToInt32(form.SupplierId)}, .... .... }; db.Database.ExecuteSqlCommand("InsertPurchaseDetails", Param.toArray(),CommandType.StoredProcedure); — Prem Kumar, Jun 30 '21 at 16:40
Hum, not sure why that's not working, you may need to use `SqlCommand` directly, see [here](https://stackoverflow.com/a/28612514/14868997) — Charlieface, Jun 30 '21 at 18:59

score 0 · Answer 2 · answered Jun 30 '21 at 00:42

0

As per your sp, there are 3 date variables . GrnDate, InvoiceDate, and today. GrnDate and InvoiceDate are passing from your code. Check the values the are passing to GrnDate and InvoiceDate. The issue is with the values you are passing, not with the sp.

answered Jun 30 '21 at 00:42

Sajitha Wattage

79
4

i have given invoice date as null @InvoiceDate date = null & i am not pass InvoiceDate until there is some value, I think there is no problem with stored procedure too. when run from management studio query exec InsertPurchaseDetails @SupplierId=2,@GrnDate='2021-06-29 00:00:00' it is working, but if i execute from entity dbcontext.database.ExecuteSqlCommand it is not working – Prem Kumar Jun 30 '21 at 02:13
Then definitely the issue is with GrnDate. Try this way, hard code the '2021-06-29 00:00:00' to your grndate variable passing to sp from code. if it's working, then the value you are passing is the issue. – Sajitha Wattage Jun 30 '21 at 04:27

Implicit conversion from data type datetime to int is not allowed

2 Answers2