ASP.NET C# loop to add records to multiple SQL tables stops unexpectedly without error

Question

I have some code that

Looks at the max ID in Table A
Fetches the records from Table B with ID > Max ID from the Table A
Loops through each record that it needs to add (are these the records from Table A????)
Finds all records in Table C that is related to the ID from Table B and inserts those records into Table D.

For some reason the loop stops unexpectedly after fetching and inserting some records with no error. It stops at random stages. Sometimes it adds 20 records sometimes 100 and sometimes 1000+. Is it to do with my logic or is it a server setting maybe? The web application .Net Framework 4.5 and is hosted on a Windows 2019 Server.

Here is the logic I follow. I specifically don't add the code as it is quite elaborate and I thought I'd share the logic I follow rather to get some suggestions on why this might be happening.

GetNewRecords()
  MaxIdA = Select max id in Table A
  Select all records in Table B with id > MaxIdA
  if reader has rows
    While reader.Read
    NextID = reader[0]
    AddNewRecordToTableB()
    Select all records in Table C where IdLink = NextID
    if reader2 has rows
      While reader2.Read
        NewDetail = reader2[..]
        AddNewRecordToTableD()
    Connection2 Dispose


AddNewRecordToTableB()
  Insert into TableB ….


AddNewRecordToTableD()
  Insert into Tabled ….

Do you do any exception handling? If not, add exception handling. If so, for debugging, try re-throwing the exception (ex: throw ex;) — It all makes cents
– It all makes cents, Commented Jul 19, 2021 at 18:53
You could do this all in a single SQL batch, no need to loop it in code — Charlieface
– Charlieface, Commented Jul 19, 2021 at 19:36
I'd look to see if it's running out of memory or timing out or something like that. — Neal Burns
– Neal Burns, Commented Jul 19, 2021 at 19:44
@CaiusJard I'm not sure how to do it in SQL. When the record is added into table B, there is a identity field that creates a new id for the record entered. I need to add this new id to the records being added in Table D. Table B is the header file while Table D has the detail lines for the order in Table B. — CP van Vuuren
– CP van Vuuren, Commented Jul 20, 2021 at 5:44

Caius Jard · Accepted Answer · 2021-07-20 05:52:56Z

1

I have some code that looks at the max ID in a table and then fetches the records from another table that has ID > Max ID from the first table. The code then loops through each record that it needs to add and finds all records in Table C that is related to the ID from Table B and adds those records all to Table D.

It sounds like this:

INSERT INTO d(column,list,here)
SELECT column,list,here --columns from b and c here
FROM
  b 
  JOIN c ON b.id = c.b_id
WHERE 
  b.id > (SELECT MAX(id) FROM a)

column list lengths must be same in insert and select

I'm not sure if you need the join; if no data is needed from B (if C contains all the required data) you can skip B:

INSERT INTO d(column,list,here)
SELECT column,list,here --columns from c here
FROM
  c
WHERE 
  c.b_id > (SELECT MAX(id) FROM a)

edited Jul 20, 2021 at 5:52

answered Jul 20, 2021 at 5:47

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ASP.NET C# loop to add records to multiple SQL tables stops unexpectedly without error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest