1

I'm inserting data into the Receipts table in my database. This table has a structured relationship to Stores linked on Stores.Id = Receipts.StoreId

Occasionally, the insert meets with the following error:

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Receipts_dbo.Stores_StoreId".

I did 5000 posts to the database and this error only occurred 10 times in that batch. The data for all 5000 posts was identical (see code below).

EDIT

I've run a second batch of 5000 posts and this time all of them went through without error effectively making the chance of an error occurring 1000:1

I've verified that there is a store in the Stores table with the Id value specified in the ajax post data.

Can anyone tell me why only 1/500th of the posts I'm sending experiences this error?

// function gets the receipt number from the loop that calls it
function CreateData(receiptNumber) {
    var StoreId = 5;
    var ReceiptValue = 100.50;
    var Cashback = 10.50;

    var Receipt = {
        "StoreId": StoreId,
        "ReceiptNumber": receiptNumber,
        "ReceiptValue": ReceiptValue,
        "Cashback": Cashback };

    return Receipt;
}

// function initiates the loop for x times
function LoopPostData ( iterations ) {

    for ( var i = 1; i <= iterations; i++ ) {
        PostData( i );
    }
}

function PostData ( iteration ) {
    var PostCompleted = false,
        ReceiptNumber = "TST-"+ pad( iteration, 4 );

    UpdatePostsTable( "- Attempting to post "+ ReceiptNumber );

    $.ajax( {
        type: "post",
        url: "http://localhost:54354/api/Receipt",
        data: CreateData( ReceiptNumber ),
        success: function () {
            var msg = "-"+ ReceiptNumber +" was saved on the API";

            UpdateSuccessTable( msg ); // indicate the post was saved
        },
        statusCode: {
            400: function () {
                var msg = "-"+ ReceiptNumber +" was sent to API but not saved";

                UpdateErrorsTable( msg ); // indicate the post couldn't be saved

                PostCompleted = true;
            }
        },
        error: function () {
            if ( PostCompleted ) return;

            var msg = "-"+ ReceiptNumber +" was not sent to the API";

            UpdateErrorsTable( msg ); // indicate the post was sent to the API
        }
    } );
}

EDIT

Here's some more info.

Here's the code that actually does the insert into the database. It uses Entity Framework

private void PostReceipt(CreateReceiptViewModel Source)
{
    PocketPlootoEntities db = new PocketPlootoEntities();

    Receipt Receipt = new Receipt
    {
        Calculated = false,
        Cashback = Source.Cashback,
        Deleted = false,
        ReceiptDate = DateTime.Now,
        ReceiptNumber = Source.ReceiptNumber,
        ReceiptValue = Source.ReceiptValue,
        Redeemed = false,
        Store = db.Stores.FirstOrDefault(x => x.Id == Source.StoreId),
        StoreId = Source.StoreId,
        ValidUntil = DateTime.Now.AddDays(7)
    };

    db.Receipts.Add(Receipt);
    db.SaveChanges();
}

This is hit by the ajax post above.

Here's the Stores table (bear in mind that the ajax is posting to the store with Id 5 (Roman's Pizza)

Here's the Stores table

Based on this, I can't believe that the error is occurring because there is no linked record in the Stores table for the new Receipt.

EDIT 2

I pasted the POCO classes that Entity Framework Code-First used to create the tables as well as the SQL code to create them as well.

http://pastebin.com/7JDnbU2A

To save some time, here's the Foreign Key Constraint in question per the error message:

ALTER TABLE [dbo].[Receipts]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Receipts_dbo.Stores_StoreId] FOREIGN KEY([StoreId])
REFERENCES [dbo].[Stores] ([Id])
ON DELETE CASCADE
GO

EDIT 3

I've updated my server code only to make EF log its queries to the Output Windows in Visual Studio.

Here's one of the INSERT commands it executed (this command was selected randomly)

INSERT [dbo].[Receipts]([StoreId], [UserId], [ReceiptNumber], 
       [ReceiptValue], [ReceiptDate], [Cashback], [ValidUntil], 
       [Calculated], [Redeemed], [Deleted], [Invoice_Id])
VALUES (@0, NULL, @1, @2, @3, @4, @5, @6, @7, @8, NULL)
SELECT [Id]
FROM [dbo].[Receipts]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()


-- @0: '1' (Type = Int32)
-- @1: 'TST-0006' (Type = String, Size = -1)
-- @2: '100.5' (Type = Decimal, Precision = 18, Scale = 2)
-- @3: '2016/07/28 6:38:38 PM' (Type = DateTime2)
-- @4: '10.5' (Type = Decimal, Precision = 18, Scale = 2)
Opened connection at 2016/07/28 6:38:38 PM +02:00
-- @5: '2016/08/04 6:38:38 PM' (Type = DateTime2)
Started transaction at 2016/07/28 6:38:38 PM +02:00
-- @6: 'False' (Type = Boolean)
-- @7: 'False' (Type = Boolean)
-- @8: 'False' (Type = Boolean)
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Ortund
  • 8,095
  • 18
  • 71
  • 139
  • I think you are trying to insert a `storeId`, that does not exist in `Store` table, into `Receipts` table... – Kaf Jul 28 '16 at 13:46
  • Show us the FK definition and the two table definitions, including the PKs and any constraints. Also any triggers on either table. – RBarryYoung Jul 28 '16 at 13:49
  • @Kaf every `receipt` from `CreateData( ReceiptNumber )` has same `StoreID = 5` . Ortund: You probably should debug your API. Maybe the fields are assigned in the correct order. – Juan Carlos Oropeza Jul 28 '16 at 13:51
  • @RBarryYoung would a diagram of the database help? – Ortund Jul 28 '16 at 14:17
  • @JuanCarlosOropeza Entity Framework does the insert according to the data model so all I'm doing is defining an instance of that model – Ortund Jul 28 '16 at 14:23
  • I see your code and look OK, so the problem is probably on your EF side. Try to debug what query is being send to your DB. Check this [**one**](http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework) – Juan Carlos Oropeza Jul 28 '16 at 14:25
  • 3
    You've indicated that you're having a database issue but only provided us with your front-end code. – Chris Pickford Jul 28 '16 at 15:01
  • No, I can already figure out what the diagram looks like, we need the FK and table definitions to help you with your problem. – RBarryYoung Jul 28 '16 at 15:07
  • @RBarryYoung I've updated the question to include the Foreign Key Constraint as it would have been created at the time when the tables were created by Entity Framework – Ortund Jul 28 '16 at 16:08
  • @JuanCarlosOropeza to debug what query is being sent to the DB would be difficult because in a batch of 5000 inserts, the error only happened 10 times. – Ortund Jul 28 '16 at 16:10
  • could there be an issue with this not returning what's expected? db.Stores.FirstOrDefault(x => x.Id == Source.StoreId) Maybe get that value ahead of time and set it instead of using it inline, see if that helps? – Nikki9696 Jul 28 '16 at 16:35
  • @Ortund what is the problem, save that string to a textfile and check it. Again I dont think JS has any error. So you have to look somewhere else – Juan Carlos Oropeza Jul 28 '16 at 16:40
  • what is this `Store = db.Stores.FirstOrDefault(x => x.Id == Source.StoreId),` ?? You have an object store in the receipt? – Juan Carlos Oropeza Jul 28 '16 at 18:39
  • `ajax is posting to the store with Id 5 (Roman's Pizza)` but your sql sample say.... `-- @0: '1' (Type = Int32)` – Juan Carlos Oropeza Jul 28 '16 at 18:50
  • If this is an environment where you have access to change DB schema, I suggest you remove the foreign key, run through your script and simply run a query to see whether you see any receipts with store Id that is not in Store table. This will at least show that you are sending wrong keys and you can debug the API layer to see what is sending wrong data. This is probably not a DB issue. – Nachi Jun 26 '17 at 19:12

0 Answers0