VB2008: IndexOutOfRangeException was unhandled when attempting to read values from SQL into a list collection

AltaSens
AltaSens used Ask the Experts™
on

I am attempting to load a single column of data from a SQL Server table into a list.collection and when I execute the code, I receive the error message "IndexOutOfRangeException was unhandled".

I played around with the idea of using an array but went with a list collection because :

1. as I understand it, a list is dynamic and can resize itself automatically
2. adding items to a list seem simpler and can be done without having to create a for-next loop which I am unsure how to code

The code segment is shown below ---------------------------

Public ProjectList As New List(Of String)(100)

connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.CloseConnection)
            'ProjectList = New String(intNumRows) {}

            Do While reader.Read
                'ProjectList.Add(reader("ProjectName"))  ', reader("ProjectDescription")))
                Debug.Write(reader("ProjectName"))

            Loop

            MsgBox("Error in Form.Initialize module" & vbCr & vbCr & "Please record this error message and contact the IT Helpdesk", vbExclamation, "ProjecTrack : Error")

            Close()

        Catch ex As SqlException
            Throw ex

        Finally
            connection.Close()

        End Try

Although I don't understand much of this, the exception detail is listed below ---------------------------

System.IndexOutOfRangeException was unhandled
  Message="ProjectName"
  Source="System.Data"
  StackTrace:
       at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
       at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
       at System.Data.SqlClient.SqlDataReader.get_Item(String name)
       at ProjecTrack.frmTimeEntry.frmTimeEntry_initialize() in C:\VSProjects\WindowsApplication1\WindowsApplication1\Form1.vb:line 189
       at ProjecTrack.frmTimeEntry.TimeEntry_Load(Object sender, EventArgs e) in C:\VSProjects\WindowsApplication1\WindowsApplication1\Form1.vb:line 30
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.Form.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
       at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
       at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
       at System.Windows.Forms.Control.set_Visible(Boolean value)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at ProjecTrack.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

---------------------------

I appreciate any guidance you can provide.  THANK YOU!

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015
Commented:
You're code is slightly hard to read. There is a check box titled "Attach Code Snippet" below where you post your message which you can check to insert code snippets that get formatted for display ;)

I am assuming you initialized your "selectCommand" object--I don't see where you did in the code you posted so I'll assume you initialized elsewhere. I imagine you also set your query text for the select command (because I don't see anything mentioned regarding invalid query in your exception message). Confirm these two statements and I will proceed with another possibility.

If the query you send to the DB does not return any data, then you will get an error when you try to access the columns of the reader--because there are no columns!! Try checking the HasRows property before attempthing to access the rows/columns of the reader.
If reader.HasRows Then
    Do While reader.Read
        'ProjectList.Add(reader("ProjectName"))  ', reader("ProjectDescription")))
        Debug.Write(reader("ProjectName"))
    Loop
End If

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial