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
Solved

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

Posted on 2014-10-11
6
827 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

856 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