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:
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




Reply With Quote
