Error while trying to exec stored procedure parameters Entity Framework EDMX model in C#

Question

I am trying to execute my stored procedure which takes 13 parameters and some can accept nulls and some are always required. I am getting an error for 2 date parameters that do accept null.

I am getting the following error

System.Data.SqlClient.SqlException
The parameterized query '(@recordType nvarchar(12),@lotCreation bit,@licensePlateCreation' expects the parameter '@lotManufactureDate', which was not supplied.

Here is the stored procedure code that edmx model creates when I call the stored procedure from SQL Server database, also here the parameter named licensePlateLookupCode from the stored procedure allows nulls but in the code the emdx model created it doesn't show like it can take nulls - do you know why?

It should be like Nullable<string> licensePlateLookupCode and not like it is string licensePlateLookupCode.

  public virtual int AddFeedbackRequestsAgentInsert(string recordType, 
     Nullable<bool> lotCreation, Nullable<bool> licensePlateCreation, 
     Nullable<int> finishedGoodLineId, Nullable<int> lotid, string 
     lotLookupCode, Nullable<System.DateTime> lotManufactureDate, 
     Nullable<System.DateTime> lotExpirationDate, Nullable<decimal> 
     packagedAmount, Nullable<int> packagingId, string 
     licensePlateLookupCode, Nullable<int> licensePlateId, Nullable<int> 
     licensePlateLocationId)
    {
        var recordTypeParameter = recordType != null ?
            new ObjectParameter("recordType", recordType) :
            new ObjectParameter("recordType", typeof(string));

        var lotCreationParameter = lotCreation.HasValue ?
            new ObjectParameter("lotCreation", lotCreation) :
            new ObjectParameter("lotCreation", typeof(bool));

        var licensePlateCreationParameter = licensePlateCreation.HasValue ?
            new ObjectParameter("licensePlateCreation", licensePlateCreation) :
            new ObjectParameter("licensePlateCreation", typeof(bool));

        var finishedGoodLineIdParameter = finishedGoodLineId.HasValue ?
            new ObjectParameter("finishedGoodLineId", finishedGoodLineId) :
            new ObjectParameter("finishedGoodLineId", typeof(int));

        var lotidParameter = lotid.HasValue ?
            new ObjectParameter("lotid", lotid) :
            new ObjectParameter("lotid", typeof(int));

        var lotLookupCodeParameter = lotLookupCode != null ?
            new ObjectParameter("lotLookupCode", lotLookupCode) :
            new ObjectParameter("lotLookupCode", typeof(string));

        var lotManufactureDateParameter = lotManufactureDate.HasValue ?
            new ObjectParameter("lotManufactureDate", lotManufactureDate) :
            new ObjectParameter("lotManufactureDate", typeof(System.DateTime));

        var lotExpirationDateParameter = lotExpirationDate.HasValue ?
            new ObjectParameter("lotExpirationDate", lotExpirationDate) :
            new ObjectParameter("lotExpirationDate", typeof(System.DateTime));

        var packagedAmountParameter = packagedAmount.HasValue ?
            new ObjectParameter("packagedAmount", packagedAmount) :
            new ObjectParameter("packagedAmount", typeof(decimal));

        var packagingIdParameter = packagingId.HasValue ?
            new ObjectParameter("packagingId", packagingId) :
            new ObjectParameter("packagingId", typeof(int));

        var licensePlateLookupCodeParameter = licensePlateLookupCode != null ?
            new ObjectParameter("licensePlateLookupCode", licensePlateLookupCode) :
            new ObjectParameter("licensePlateLookupCode", typeof(string));

        var licensePlateIdParameter = licensePlateId.HasValue ?
            new ObjectParameter("licensePlateId", licensePlateId) :
            new ObjectParameter("licensePlateId", typeof(int));

        var licensePlateLocationIdParameter = licensePlateLocationId.HasValue ?
            new ObjectParameter("licensePlateLocationId", licensePlateLocationId) :
            new ObjectParameter("licensePlateLocationId", typeof(int));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("AddFeedbackRequestsAgentInsert", recordTypeParameter, lotCreationParameter, licensePlateCreationParameter, finishedGoodLineIdParameter, lotidParameter, lotLookupCodeParameter, lotManufactureDateParameter, lotExpirationDateParameter, packagedAmountParameter, packagingIdParameter, licensePlateLookupCodeParameter, licensePlateIdParameter, licensePlateLocationIdParameter);
    }

Here is where I am calling that stored procedure and then executing it after user click the submit button, I am getting an error for the lotmanufacturer and lotexpiration date parameters because they are NULLS I think, but in the actual database stored procedure it can take nulls or not

    private void Btn_Submit_Click(object sender, EventArgs e)
    {
        // db context variable
        var context = _manufacturingDbContext;

        // ** Variables to insert to FootPrint stored procedure datex_footprint_integration.AddFeedbackRequestsAgentInsert with Record Type (FinishedGood)
        const string recordType = "FinishedGood";
        const bool lotCreation = false;
        const bool licensePlateCreation = true;           
        var finishedGoodLineId = context.FinishedGoodLineIdByOrderAndFinishedGoodAndLot(Cmb_MfgOrder.Text, Cmb_FgLookupCode.Text, Cmb_LotLookupCode.Text).FirstOrDefault();         
        var lotId = context.LotIdByManufacturingOrderAndFinishedGood(Cmb_MfgOrder.Text, Cmb_FgLookupCode.Text,Cmb_LotLookupCode.Text).FirstOrDefault();
        var doNotCreateLot = null;
        DateTime? lotManufactureDate = null;
        DateTime? lotExpirationDate = null;
        var packagedAmount = Convert.ToDecimal(Txt_PackagedAmount.Text);
        const int packagedId = 3;
        var licensePlateLookupCode = Txt_LicensePlateLookupCode.Text;
        int? licensePlateId = null;
        const int licensePlateLocationId = 51372;            

        // Call SQL Server SPROC dbo.AddFeedbackRequestsAgentInsert and enter data to FootPrint Task

        context.Database.ExecuteSqlCommand("EXEC dbo.AddFeedbackRequestsAgentInsert " +
                                           "@recordType, @lotCreation, @licensePlateCreation, @finishedGoodLineId, @lotid, @lotLookupCode, @lotManufactureDate," +
                                           "@lotExpirationDate, @packagedAmount, @packagingId, @licensePlateLookupCode, @licensePlateId, @licensePlateLocationId",
                             new SqlParameter("@recordType", recordType),
                                            new SqlParameter("@lotCreation", lotCreation),
                                            new SqlParameter("@licensePlateCreation", licensePlateCreation),
                                            new SqlParameter("@finishedGoodLineId", finishedGoodLineId),
                                            new SqlParameter("@lotid", lotId),
                                            new SqlParameter("@lotLookupCode", doNotCreateLot),
                                            new SqlParameter("@lotManufactureDate", lotManufactureDate),
                                            new SqlParameter("@lotExpirationDate", lotExpirationDate),
                                            new SqlParameter("@packagedAmount", packagedAmount),
                                            new SqlParameter("@packagingId", packagedId),
                                            new SqlParameter("@licensePlateLookupCode", licensePlateLookupCode),
                                            new SqlParameter("@licensePlateId", licensePlateId),
                                            new SqlParameter("@licensePlateLocationId", licensePlateLocationId)
                                            );

        context.SaveChanges();
}

Here is the actual stored procedure - as you can see @lotManufactureDate and @lotExpirationDate do allow nulls:

CREATE PROCEDURE [dbo].[AddFeedbackRequestsAgentInsert]
         @recordType NVARCHAR(30),
         @lotCreation BIT,
         @licensePlateCreation BIT,
         @finishedGoodLineId INT,
         @lotid INT NULL,
         @lotLookupCode NVARCHAR(256) NULL,
         @lotManufactureDate DATETIME NULL,
         @lotExpirationDate DATETIME NULL,
         @packagedAmount DECIMAL(28,8),
         @packagingId INT,
         @licensePlateLookupCode NVARCHAR(256) NULL,
         @licensePlateId INT NULL,
         @licensePlateLocationId INT NULL

So, I don't understand why I am getting that error of expected when I am passing for those 2 date parameters with null dates the same thing happens if the lotlookupcode parameter if I pass null I get the same error that is expecting lotlookupcode. Can you see what could be the issue here?

I made a new change to my code and now I don't get the error as my previous request description but now when I call the stored procedure to execute I don't see nothing on the database, can look below that based on the parameters I am providing is correct based on the emdx model?

I am calling this function from the model browser import function stored procedure, when I bit submit button I don't receivie nothing on the database is the parameters looks correct based above emdx model and stored procedure?

    var context = _manufacturingDbContext;


    const string recordType = "FinishedGood";
    const bool lotCreation = false;
    const bool licensePlateCreation = true;           
    var finishedGoodLineId = context.FinishedGoodLineIdByOrderAndFinishedGoodAndLot(Cmb_MfgOrder.Text, Cmb_FgLookupCode.Text, Cmb_LotLookupCode.Text).FirstOrDefault();         
    var lotId = context.LotIdByManufacturingOrderAndFinishedGood(Cmb_MfgOrder.Text, Cmb_FgLookupCode.Text,Cmb_LotLookupCode.Text).FirstOrDefault();
    var doNotCreateLot = null;
    DateTime? lotManufactureDate = null;
    DateTime? lotExpirationDate = null;
    var packagedAmount = Convert.ToDecimal(Txt_PackagedAmount.Text);
    const int packagedId = 3;
    var licensePlateLookupCode = Txt_LicensePlateLookupCode.Text;
    int? licensePlateId = null;
    const int licensePlateLocationId = 51372;    


        //calling stored procedure and send data to sproc based on the variables above
        context.AddFeedbackRequestsAgentInsert(recordType, lotCreation, licensePlateCreation, finishedGoodLineId,
            lotId, lot, lotManufactureDate, lotExpirationDate, packagedAmount, packagedId, licensePlateLookupCode,
            licensePlateId, licensePlateLocationId);

}

Answer 1

So, you updated the Stored Proc in the database, but your data models are not aware of those changes.. what you need to do is to update the Entity FW in your .net application as well.. 1- Refresh (update) the EDMX from the database..

2- Right click on the conceptual model (mode.context.tt) and (model.tt) and click on (run custom tool).. that will update your C# mapped data models to see these added new params.

Answer 2

You may call AddFeedbackRequestsAgentInsert method generated by EF directly and pass the parameters to it.you may not need to call ontext.Database.ExecuteSqlCommand

still, if you want to use ontext.Database.ExecuteSqlCommand, you can use below code to pass the null on nullable date parameters

        new SqlParameter("@lotManufactureDate", lotManufactureDate.HasValue ? lotManufactureDate : DBNull.Value),
        new SqlParameter("@lotManufactureDate", lotExpirationDate.HasValue ? lotExpirationDate : DBNull.Value),

OR

        new SqlParameter("@lotManufactureDate", lotManufactureDate.HasValue ? lotManufactureDate : default(DateTime)),
        new SqlParameter("@lotManufactureDate", lotExpirationDate.HasValue ? lotExpirationDate : default(DateTime)),

Answer 3

This is my answer all I had to do is fix some part of my sql stored procure and most important I just had to call my import function created from emdx model and add the correct paremeters to it, after checking in my database data is inserting correcly based on code solution, any question if you see a better approach please let me know.

    public void ExecuteStoredProcedure()
    {
        try
        {
            // db context variable
            var context = _manufacturingDbContext;

            const string recordType = "FinishedGood";
            const bool lotCreation = false;
            const bool licensePlateCreation = true;
            var finishedGoodLineId = context.FinishedGoodLineIdByOrderAndFinishedGoodAndLot(Cmb_MfgOrder.Text, Cmb_FgLookupCode.Text, Cmb_LotLookupCode.Text).FirstOrDefault();
            var lotId = context.LotIdByManufacturingOrderAndFinishedGood(Cmb_MfgOrder.Text, Cmb_FgLookupCode.Text, Cmb_LotLookupCode.Text).FirstOrDefault();
            string lot = null;
            DateTime? lotManufactureDate = null;
            DateTime? lotExpirationDate = null;
            var packagedAmount = Convert.ToDecimal(Txt_PackagedAmount.Text);
            const int packagedId = 3;
            var licensePlateLookupCode = Txt_LicensePlateLookupCode.Text;
            int? licensePlateId = null;
            const int licensePlateLocationId = 51372;


            // Call SQL Server SPROC datex_footprint_integration.AddFeedbackRequestsAgentInsert and enter data to FootPrint Task
            var run = context.AddFeedbackRequestsAgentInsert(recordType, lotCreation, licensePlateCreation, finishedGoodLineId,
                lotId, "", lotManufactureDate, lotExpirationDate, packagedAmount, packagedId, licensePlateLookupCode,
                licensePlateId, licensePlateLocationId);
            context.SaveChanges();
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
            throw;
        }       
    }