Solved

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

Posted on 2014-10-11
6
791 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ADO.NET ENTITY DATA MODEL 3 30
Class object 2 26
Name space syntax error 12 40
I need help converting a bitmap to an image in VB.Net 1 0
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…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…

911 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

21 Experts available now in Live!

Get 1:1 Help Now