system Index Out of Range Exception when reading column

[emyk]

I am trying to write the sql return results into a file. The SQL query works fine when running it against SQL query editor.
I am getting system Index Out of Range Exception ERROR on the first column when running this program. it fails after ExecuteReader command on the first column. Any pointer or direction on how to solve this issue greatly appreciated.

here is my code:

Imports System.Net.Mail
Imports System.IO
Imports System.Data.SqlClient.SqlConnection


Module Module1


    Dim sqCmd As New SqlClient.SqlCommand
    Dim sdrRow As SqlClient.SqlDataReader
    Dim cmdObj
    Dim dbLOCATION
    Dim dbDamage
    Dim dbFirstName

    Sub Main()
      

        Dim dbPDI_LOCATION As String
        Dim dbPDI_INSERTTS As String
        Dim dbPDI_DAMAGE_DETAILS As String
        Dim dbPDI_CONTACT_FIRSTNAME As String
        Dim dbCONTACT_LASTNAME As String
        Dim dbPDI_CONTACT_PHONENUMBER As String
        Dim dbPDI_CONTACT_EMAIL As String
        Using connObj As New SqlClient.SqlConnection("Server=server;Database=databasename;User Id=username; Password=password;")
            
            Using cmdObj As New SqlClient.SqlCommand("Select  (t.PDI_LOCATION)As LOCATION, (t.PDI_INSERTTS) As IncidentOpened, " _
            & ",t.PDI_CONTACT_FIRSTNAME + ' ' + t.PDI_CONTACT_LASTNAME as reportedby, " _
            & "  FROM " _
            & "  (" _
            & "  SELECT  (PDI_LOCATION), (PDI_INSERTTS),PDI_STATUS, " _
            & "  ROW_NUMBER() over (partition by PDI_LOCATION ORDER BY PDI_INSERTTS DESC) AS TEST" _
            & "  FROM [TABLE1])AS t" _
            & "  inner join TABLE2 i" _
            & "  on i.PDI_INSERTTS = t.PDI_INSERTTS" _
            & "  WHERE t.TEST=1" _
            & "  AND t.PDI_STATUS = 0" _
            & "  order by IncidentOpened desc", connObj)
                connObj.Open()



                Using readerObj As SqlClient.SqlDataReader = cmdObj.ExecuteReader
                    'This will loop through all returned records 
                    Dim objStreamWriter As StreamWriter

                    Dim EMAILTXT = "C:\email.txt"
                    objStreamWriter = New StreamWriter(EMAILTXT)
                    While readerObj.Read
                        If (readerObj.Read()) Then



                            dbPDI_LOCATION = readerObj("PDI_LOCATION").ToString
                            dbPDI_INSERTTS = readerObj("PDI_INSERTTS").ToString
                            
                            objStreamWriter.WriteLine("LOCATION: " & dbPDI_LOCATION & " " & dbPDI_INSERTTS)
                             
                        End If
                    End While
                    objStreamWriter.Close()
                 
            End Using
            connObj.Close()
            End Using

    End Sub


End Module

[Traveller]

If your query returns nothing, what happens when your code executes "readerObj.Read"? Is that where you are getting your error? If so, you might want to check for that prior to trying to read the query return.

Also, according to your code, you are calling readerObj.Read twice before you do anything with it. It is called first at your While statement and then again in your If statement. If it increments each time it is called and there is only one increment, then the second call will be out of range. I suggest you remove the If statment since it is unnecessary.

[jmcilhinney]

Debug your code. The first two questions to ask when you get an IndexOutOfRangeException should be obvious: what index and what range? The debugger will break on the line that causes the exception and you can probably just mouse over a variable to answer the first and use some logic to determine the second. You can then work backwards through the code to work out why you got that invalid value.

[emyk]

Issue resolved:
The out of range issue was caused due to a column name mismatch between the select and the readerobject column name. For the select I had

(select (t.PDI_LOCATION) As LOCATION

and for reader object I had

dbPDI_LOCATION = readerObj("PDI_LOCATION").ToString,

so to fix the issue I renamed

dbPDI_LOCATION = readerObj("PDI_LOCATION").ToString

to

dbPDI_LOCATION = readerObj("LOCATION").ToString

That took care of the issue.

thanks