I have used the following code to compare two tables (Table1 contains the full list of ID numbers, Table2 could be completely empty) if table2 does not contain the same ID numbers as Table1 then the missing numbers are inserted into Table2.
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT Table1_tbl.StuRecId, Table2.RecID FROM Table1 LEFT JOIN Table2 ON Table1.StuRecId = Table2.RecID", dbConnection
while not rs.eof
if isnull(rs("RecID")) then
strSQL = "INSERT INTO Table2 (RecID) VALUES (" & rs("StuRecId") & ")"
dbConnection.execute strSQL
else
end if
rs.movenext
wend
rs.close : set rs = Nothing
This code works fine when added into an ASP snippet on the list page or on the ListPage:Before Display event, however this stage is too late as at this point the initial list page has already been created and if Table2 didn't have a corresponding RecID value the records will not be linked properly. This is rectified if the page is refreshed though as at that point the code has already run and the missing ID numbers copied over.
Ideally I think the code should be run from the Before SQL Query event, however using this code as is results in the page not loading (HTTP 500 Internal Server Error).
Can someone please help me to modify this code so it can be run from the Before SQL Query event.
Thanks