fedeteka fedeteka - 29 days ago 11
Vb.net Question

SQLiteDataReader pick up one field from the next record?

With a sql like this

Dim sql = "SELECT * FROM TablePDF order by NameX asc"


I loop through the names of my database and make a iTextsharp table with the data.

While (readerProtocoloTablePDF.Read())

NameXPDF = (readerProtocoloTablePDF("NameX"))
...
...
...


If the records looks like

Audi

Alfa Romeo

Aston Martin

BMW

Bugatti

When I read "Aston Martin" I need to know this is THE LAST NAME starting with "A" so the row of this record will have a special bottom line to close the A letter

This is because I need to change the border or the cell of the last record and insert a blank page before the start of the new letter

The options are

cellHRectangleBorder = 12 'just left and right border
cellHRectangleBorder = 14 'lef, right AND BOTTOM border (Last record of a letter)


On the table I use code like

cellH3.Border = cellHRectangleBorder


And if it is the last record of a letter also I need a blank page

pdfDoc.NewPage()


I know how to detect the change of the letter while reading BMW, but this is AFTER the A ended.

Is there a way to just pick up one field of the next record without breaking the normal sql reader?

Or I need one loop inside another or maybe first a While - reader loop just to detect when a letter change and then another While reader loop to create the table knowing from beforehand the numbers of fields with each letter?

Answer

I can only suggest a pseudocode that you should transform in your actual code with the real variable names, but essentially you don't add a line when you read it, but you add it on the following read

Dim lastName as String = ""
Dim lastLetter As string = ""
While (readerProtocoloTablePDF.Read())
    ' Read the Name and store it
    NameXPDF = (readerProtocoloTablePDF("NameX"))
    Dim curLetter = NameXPDF(0).ToString()

    ' Skip first loop
    if lastLetter <> ""  Then 
       ' After the first loop check if there is a change in the first letter
       if lastLetter <> curLetter then
           ' Yes, add the previous loop name as the last line and jump page
           AddTheLastLineOfThePage(lastName)
           NewPage()
       else 
           ' No add a normal line with the previous name read
           AddNormalLine(lastName)
       End if
    End if
    ' Save the current name read for the next loop
    lastName = NameXPDF
    lastLetter = lastName(0).ToString()
 Loop

 ' Do not forget to add the last line
 AddTheLastLineOfThePage(lastName)
Comments