I have the following SQL query which I am loading in to a DataSet:
SELECT i1.* , i2.* From tblMMLettersImportTable i1 Join tblMMLettersImportTable i2 on i1.SectionID + 1 = i2.SectionID Where i2.startpage - i1.endpage <> 1
Idea is to check that the index for various sections of a document lead one page on to the other with no gaps. I.e section 2 ends on page 5 and section 3 starts on page 6.
I'm happy that the SQL works, however by joining on itself the field "SectionID" is duplicated. In SQL easy enough, just use i1. or i2. to reference the correct one.
The issue comes when I load this in to a VB.net Dataset. I need to raise an error message with something like:
MessageBox.Show("There is a page gap between sections " & row.item("i1.sectionID") & " and " & row.item("i2.sectionID")
I get the error message Column 'i1.intline' does not belong to table Table. Makes sense as that is not its name in the dataset. I've considered using the column number to reference the item to pull out, however the SQL Table tblMMLettersImportTable is deleted, created and populated dynamically depending on the type of Letter/document being produced so I cannot always guarantee that the columns will always numbered the same. This is also why i1.* and i2.* is used instead of listing each column.
Is there a way that I can reference 2 items in a DataSet that have the same item name with VB.Net?