Testing for the last record

[bajanpoet]

How do you test a record to see if it is the last record? I want to append one string to the end of my script string if the record is the last one, and a continue string if it is not.

I'm showing the entire procedure below so that you guys can get a full understanding of what I'm trying to achieve.

Private Sub BuildScript()
        'Build the SQL script to add the customers previously stored in the CUSTOMERS table
        Dim SelectString As String = "SELECT CustNum FROM Customers WHERE Location = " & LocationCode
        Dim ODBCCon As New OleDbConnection(Testing_ODBC_ConnectionString)
        Dim ODBCCommand As New OleDbCommand(SelectString, ODBCCon)

        ODBCCon.Open()
        Dim r As OleDbDataReader = ODBCCommand.ExecuteReader
        ScriptString = StartScript 'Start with beginning of script
        While r.Read
            AddCustomer(r(2)) 'Add customer pulled from database
            'if record is the last record
            'End While     'exit loop
            'else
            ScriptString = ScriptString & ScriptContinue 'Format script to continue adding customers 
            'endif
        End While
        ScriptString = ScriptString & EndScript 'Append end of Script
        MessageBox.Show(ScriptString)
    End Sub

[QVeen72]

Hi,

I dint get u, U already r appending outside the loop, what u want to do here..?

REgards
Veena

[bajanpoet]

I want to test for the last record.

I am trying to build a script which adds customer numbers into a long string (example giving me a script that has a structure like BEGIN STATEMENT 'Customer 1 OR Customer 2 OR Customer 3' END STATEMENT) . The script has constants representing the beginning of the script (STARTSCRIPT) as well as a constant used to connect two customers with an OR statement (CONTINUE) and an ending constant that closes off the script with the appropriate number of ending brackets (ENDSCRIPT)

The while loop cycles through the whole table and I'm looking for a way to automatically join each customer with one constant (the 'OR' statement) but add the END statement at the end of the script.

If I do it without testing for the last record and breaking out of the loop before the 'last' OR statement, the way I have it written it will be like BEGIN STATEMENT Customer 1 OR Customer 2 OR Customer 3 OR ENDSTATEMENT. I want to cut out that last unnecessary OR before the ENDSTATEMENT, which is just closing brackets.

I just don't know how to test a record to see if it is the last record or not - that's all I need. In the below snippet I don't actually test for the last record (line # 3 - it's commented out.)

While r.Read
            AddCustomer(r(2)) 'Add customer pulled from database
            'if record is the last record
            'End While     'exit loop
            'else
            ScriptString = ScriptString & ScriptContinue 'Format script

[bajanpoet]

Ok, so I figured it out.

I added a MaxRecords variable that stored the # of records in the dataset and an INC variable that incremented at every iteration. When the inc variable = MaxRecords, then that record is the last record in the dataset, so I append the end of the script rather than the continue string.

Dim SelectString As String = "SELECT CustNum FROM Customers WHERE Location = '" & LocationCode & "'"
Dim ODBCCommand As New OleDbCommand(SelectString, ODBCCon)
Dim MaxRows, inc As Integer
Dim Da As OleDb.OleDbDataAdapter
Dim DS As New DataSet

  ODBCCon.Open()
  Da = New OleDb.OleDbDataAdapter(SelectString, ODBCCon)
  Da.Fill(DS, "CustomerDS")

  MaxRows = DS.Tables("CustomerDS").Rows.Count 'Store the # of records i
   inc = 0 'initalizing the counter

   Dim r As OleDbDataReader = ODBCCommand.ExecuteReader

   ScriptString = StartScript 'Start with beginning of script
   While r.Read
           inc = inc + 1
          AddCustomer(r(0)) 'Add customer pulled from database
          If inc = MaxRows Then 'if record is the last record
              ScriptString = ScriptString & EndScript 'Append end of Script
              Exit While
          Else
              ScriptString = ScriptString & ScriptContinue 'Append 'OR' to script
          End If
    End While