Solved

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

Posted on 2014-10-11
6
872 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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 70

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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
LVL 34

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 34

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 34

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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