CODEWITHSUNDEEP
sql-serverasp.net-mvcentity-frameworkstored-procedures
Prem Kumar
Prem Kumar

Reputation: 81

Implicit conversion from data type datetime to int is not allowed

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.

Upvotes: 0

Views: 1557

Answers (2)

Charlieface
Charlieface

Reputation: 72287

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.

Upvotes: 1

Sajitha Wattage
Sajitha Wattage

Reputation: 79

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.

Upvotes: 0

Related Questions