Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-10-11
6
Medium Priority
?
994 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 12

Accepted Solution

by:
louisfr earned 1200 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 800 total points
ID: 40374960
Also, set a breakpoint in the exception error handler, your exception if there is one is just swallowed!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

581 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