VB,NET - System.IndexOutOfRangeException: 'Cannot find table 0.'

I am trying to populate a combo box based off a SQL data base. when I run my code I receive the following error. System.IndexOutOfRangeException: 'Cannot find table 0.'
Below is my code. What Is this newbie missing?

Created Class Called SQLControl.vb to connect to db

Imports System.Data.SqlClient
Public Class SQLControl

    Private DBCon As New SqlConnection("Server=xxx;Database=xxx;Integrated Security=True")
    Private DBCmd As SqlCommand

    'DB Data
    Public DBDA As SqlDataAdapter
    Public DBDT As DataTable
    Public SQLDS As DataSet

    'Query Parameters
    Public params As New List(Of SqlParameter)

    'Query Stats

    Public RecordCount As Integer
    Public Exception As String

    Public Sub New()
    End Sub

    'Allow Connection String Override
    Public Sub New(connectionstring As String)
        DBCon = New SqlConnection(connectionstring)
    End Sub

    'Execute Query Sub
    Public Sub ExecQuery(Query As String)

        ' Reset Query Stats
        RecordCount = 0
        Exception = ""

        Try
            DBCon.Open()
            'Create DB COmmand
            DBCmd = New SqlCommand(Query, DBCon)

            'Load Params into db command

            params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

            'Clear Param List
            params.Clear()

            'Execute Command and Fill Dataset

            DBDT = New DataTable
            DBDA = New SqlDataAdapter(DBCmd)
            SQLDS = New DataSet
            RecordCount = DBDA.Fill(DBDT)



        Catch ex As Exception
            'Capture Error
            Exception = "ExecQuery Error: " & vbNewLine & ex.Message
        Finally
            'close connection
            If DBCon.State = ConnectionState.Open Then DBCon.Close()
        End Try
    End Sub

    'Add Params
    Public Sub AddParam(Name As String, Value As Object)
        Dim NewParam As New SqlParameter(Name, Value)
        params.Add(NewParam)
    End Sub

    ' Error Checking
    Public Function HasExeception(Optional Report As Boolean = False) As Boolean
        If String.IsNullOrEmpty(Exception) Then Return False
        If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
        Return True
    End Function
End Class


Form has the following code:
Public Class Form3
    Public SQL As New SQLControl
    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        GetCameras()
    End Sub

    Private Sub GetCameras()
        'Query Cameras Table
        SQL.ExecQuery("Select CameraMake From Cameras")

        'If Records Are Found, Add Them to the combobox
        If SQL.RecordCount > 0 Then
            For Each r As DataRow In SQL.SQLDS.Tables(0).Rows
                ComboBox1.Items.Add(r("CameraMake"))
            Next

            ComboBox1.SelectedIndex = 0
        ElseIf SQL.Exception <> "" Then
            MsgBox(SQL.Exception)


        End If
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

    End Sub
End Class
DJ PAsked:
Who is Participating?
 
Nitin SontakkeDeveloperCommented:
You got to debug and / or start resolving issue end to end. Question everything and make no assumptions whatsoever. First of all the entire approach of getting data and binding looks hugely convoluted to me. The process is actually very simple. Anyway. Let us keep that aside for a moment.

Second, where does the Grid come into picture here. You are populating the drop down box, I believe.

Did you already fired that query in SQL Server Management studio and got the expected output.

Did you debug and check that the line which actually adds items to combo-box did in fact hit.
0
 
MishaProgrammerCommented:
May be the problem in your DataSet ? It doesn't have a Table[0].
Can you debug this? Can you execute your own SQL query and check the result?
0
 
Nitin SontakkeDeveloperCommented:
You are not sending Dataset to get filled to the Fill method. You are directly sending the Datatable. So access table directly, instead.

For Each r As DataRow In SQL.DBDT.Rows

Open in new window


Or send Dateset to Fill as parameter, of course!
0
 
DJ PAuthor Commented:
Thanks. It got rid of the error but I can't see any data in my grid.
0
 
DJ PAuthor Commented:
Thanks. I was able to find my issue. Grid now loading.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.