Solved

Data table in data set not found?  . . .vb.net

Posted on 2014-10-11
6
770 Views
Last Modified: 2014-10-12
The code in Form1 below, at line 48 produces 'Cannot find table 0.' error:

Lin 48:   ...txtCustomerID.Text = SQL.ds.Tables(0).Rows(0).Item("CustomerID")

Q1: How can I list the data tables in my dataset (ds)?
Q2: If there is none, why there is none after a line like "da = New SqlDataAdapter(cmd)" has been executed?
Q3: How can I handle error at line 48 of Form1.vb?

Public Class Form1

    Private SQL As New SqlControl

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        GetCustomers()
    End Sub

    Private Sub GetCustomers()

        ' CLEAR THE COMBO-BOX
        cboCustomer.Items.Clear()

        ' QUERY CUSTOMER TABLE
        SQL.ExecQuery("SELECT top 3 CustomerID, FirstName + ' ' +" & _
                 " LastName as CustName From tblCustomers Union" & _
                 " SELECT '0', '<All>' From tblCustomers" & _
                 " Order By CustomerID")

        ' IF RECORDS CUSTOMERS FOUND, ADD THEM TO THE COMBO-BOX
        If SQL.RecordCount > 0 Then

            ' POPULATE CUSTOMER COMBO-BOX
            For Each r As DataRow In SQL.ds.Tables(0).Rows
                cboCustomer.Items.Add(r("CustName"))
            Next

            ' SET THE COMBO-BOX DEFAULT SETTING TO <All>
            cboCustomer.SelectedIndex = 0

        ElseIf SQL.exception <> "" Then
            ' REPORT ERROR
            MessageBox.Show(SQL.exception)
        End If

    End Sub

    Private Sub GetCustomerID(custmer As String)

        ' ADD A SEARCH PARAMETER
        SQL.AddPram("@CustName", cboCustomer)

        ' NEW QUERY
        SQL.ExecQuery("SELECT CustomerID From tblCustomers" & _
                     " WHERE FirstName + ' ' + LastName = @CustName")

        ' IF CUSTOMER ID FOUND, SEND IT TO TEXT BOX
        If SQL.RecordCount > 0 Then txtCustomerID.Text = SQL.ds.Tables(0).Rows(0).Item("CustomerID")

    End Sub

    Private Sub cboCustomer_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboCustomer.SelectedIndexChanged

        GetCustomerID(cboCustomer.Text)
    End Sub
End Class

Open in new window

Below, SqlControl.vb Class, at line 44, I assume creates a data table by executing: da = New SqlDataAdapter(cmd)
Imports System.Data
Imports System.Data.SqlClient

Public Class SqlControl

    ' CONNECTION STRING
    Dim strCnn As String = "Data Source=.\SQLEXPRESS12; Database=WSCGSoftware;Integrated Security=SSPI"

    ' CONNECTION 
    Public cnn As SqlConnection = New SqlConnection(strCnn)

    ' DATAADAPTER
    Public da As SqlDataAdapter

    ' DATASET
    Public ds As DataSet

    ' QUERY PARAMETERS
    Public params As New List(Of SqlParameter)

    ' QUERY STAT
    Public RecordCount As Integer
    Public exception As String

    Public Sub ExecQuery(query As String)

        ' COMMAND
        Dim cmd As SqlCommand = New SqlCommand

        Try
            cnn.Open()

            ' CREATE QUERY
            cmd = New SqlCommand(query, cnn)

            ' LOAD PARAMETERS TO SQLCOMMAND
            params.ForEach(Sub(x) cmd.Parameters.Add(x))

            ' CLEAR PARAMETERS
            params.Clear()

            ' EXECUTE COMMAND AND FILL DATASET
            ds = New DataSet
            da = New SqlDataAdapter(cmd)
            RecordCount = da.Fill(ds)

        Catch ex As Exception
            exception = ex.Message
        Finally
            cnn.Close()
        End Try

    End Sub

    Public Sub AddPram(name As String, value As Object)
        Dim newPram As New SqlParameter(name, value)
        params.Add(newPram)
    End Sub

End Class

Open in new window

0
Comment
Question by:Mike Eghtebas
  • 3
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
louisfr earned 300 total points
ID: 40374953
da.Fill(ds) should create the DataTable.
Put a breakpoint on line 45 of SqlControl, and then use F10 to execute the line. Check that it doesn't go into the exception handler, and that RecordCount is not 0.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40374956
I don't see anything adding quotes around the name

Can you try:
        SQL.ExecQuery("SELECT CustomerID From tblCustomers" & _
                     " WHERE FirstName + ' ' + LastName = '@CustName'")

Open in new window

0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 200 total points
ID: 40374960
Also, set a breakpoint in the exception error handler, your exception if there is one is just swallowed!
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40375437
Hi Eric,

Per your suggestion:
    Private Sub GetCustomerID(custmer As String)

        Try
            ' ADD A SEARCH PARAMETER
            SQL.AddPram("@CustName", cboCustomer)

            ' NEW QUERY
            SQL.ExecQuery("SELECT CustomerID From tblCustomers" & _
                         " WHERE FirstName + ' ' + LastName = '@CustName'")

            ' IF CUSTOMER ID FOUND, SEND IT TO TEXT BOX
            If SQL.RecordCount > 0 Then txtCustomerID.Text = SQL.ds.Tables(0).Rows(0).Item("CustomerID")
        Catch ex As Exception
            MessageBox.Show(ex.Message + vbNewLine + Err.Number.ToString)
        End Try
 
    End Sub

Open in new window

The error happens at line 12 (The same error:  'Cannot find table 0.' error number 9.

Please not that it says cannot find Table 0 where is ought to be made when we execute:

Line 44, executing: da = New SqlDataAdapter(cmd)

Could you possibly help me with the my questions below?

Q1: How can I list the data tables in my dataset (ds)?
Q2: If there is none, why there is none after a line like "da = New SqlDataAdapter(cmd)" has been executed?
Q3: How can I handle error at line 48 of Form1.vb?

Thanks,

Mike
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40375499
Hi Louisfr,

I just saw your good comment:
    Public Sub ExecQuery(query As String)

        ' COMMAND
        Dim cmd As SqlCommand = New SqlCommand

        RecordCount = 0

        Try
            cnn.Open()

            ' CREATE QUERY
            cmd = New SqlCommand(query, cnn)

            ' LOAD PARAMETERS TO SQLCOMMAND
            params.ForEach(Sub(x) cmd.Parameters.Add(x))

            ' CLEAR PARAMETERS
            params.Clear()

            ' EXECUTE COMMAND AND FILL DATASET
            ds = New DataSet
            da = New SqlDataAdapter(cmd)
            RecordCount = da.Fill(ds)

        Catch ex As Exception
            exception = ex.Message
        Finally
            cnn.Close()
        End Try

    End Sub

Open in new window


The error happens at line 23: (RecordCount = da.Fill(ds).

Thanks,

Mike
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40375562
I got it;

    Private Sub GetCustomerID(customer As String)

        Try
            ' ADD A SEARCH PARAMETER
            SQL.AddPram("@CustName", customer)    ' it was cboCustomer
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now