CODEWITHSUNDEEP
c#asp.netsql-server
CP van Vuuren
CP van Vuuren

Reputation: 3

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

I have some code that

  1. Looks at the max ID in Table A
  2. Fetches the records from Table B with ID > Max ID from the Table A
  3. Loops through each record that it needs to add (are these the records from Table A????)
  4. 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 ….

Upvotes: 0

Views: 180

Answers (1)

Caius Jard
Caius Jard

Reputation: 74660

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)

Upvotes: 1

Related Questions