Avatar of Larry Rungren
Larry Rungren
Flag for United States of America asked on

Data Set issue vb.net

I have a block of code that requires 2 data sets to be filled and data from each data set will be display on a windows form.

When I use only one data set all is fine, but when I introduce the second dataset an try to display a field I get

dataset index out of range can't find table(0)

Separate data sets ???  Help appreciated.
datasetcode.txt
Visual Basic.NET.NET Programming

Avatar of undefined
Last Comment
Larry Rungren

8/22/2022 - Mon
Randy Poole

You are only filling dt if ds does not contain values, so it will not have any tables or rows, on top of that, if dt does have a table and rows, then ds does not contain any rows.
Larry Rungren

ASKER
Separate datasets, separate sql commands,  How doe one fill 2 datasets?
Anil Golamari

Is below query you are trying to run after checking first dataset is returning any values outside this code base when you try to run it from a management studio?

Can you try to put a break point at this line and see whether it is able to enter into 2nd dataset code block.

select * from meet_master where meeting = '" & txtProductCode.Text & "'", sqlCnn
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Athar Syed

DataSet has capability to hold multiple tables within it (Duh! set of data!). Instead of using multiple DataSets you can use a single DataSet with multiple Tables in it. Below is a snippet to get you started.

Public Sub MultipleTablesInSingleDataSet()
  Dim _ds As New DataSet("AdventureWorks")

  Try
    Using _cn As New SqlConnection("Data Source=DBServer;Initial Catalog=AdventureWorks;User ID=dbuserid;Password=dbpasswd")
      _cn.Open()
      Using _adapterDepartment As New SqlDataAdapter("SELECT * FROM HumanResources.Department", _cn)
        _ds.Tables.Add(New DataTable("Department"))
        _adapterDepartment.Fill(_ds.Tables(0))
      End Using
      Using _adapterEmployee As New SqlDataAdapter("SELECT * FROM HumanResources.Employee", _cn)
        _ds.Tables.Add(New DataTable("Employee"))
        _adapterEmployee.Fill(_ds.Tables(1))
      End Using
      Using _adapterEmployeeAddress As New SqlDataAdapter("SELECT * FROM HumanResources.EmployeeAddress", _cn)
        _ds.Tables.Add(New DataTable("EmployeeAddress"))
        _adapterEmployeeAddress.Fill(_ds.Tables(2))
      End Using
      Using _adapterEmployeeDepartmentHistory As New SqlDataAdapter("SELECT * FROM HumanResources.EmployeeDepartmentHistory", _cn)
        _ds.Tables.Add(New DataTable("EmployeeDepartmentHistory"))
        _adapterEmployeeDepartmentHistory.Fill(_ds.Tables(3))
      End Using
      _cn.Close()
    End Using
  Catch ex As SqlException
    MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
  Catch ex As Exception
    MsgBox(ex.Message, MsgBoxStyle.Critical, "App Error")
  End Try

End Sub

Open in new window


However, if you really really need more than one DataSet, below is again yet another snippet :)
Public Sub MultipleDataSets()
  Dim _dsDepartment As New DataSet("Department")
  Dim _dsEmployee As New DataSet("Employee")
  Dim _dsEmployeeAddress As New DataSet("EmployeeAddress")
  Dim _dsEmployeeDepartmentHistory As New DataSet("EmployeeDepartmentHistory")

  Try
    Using _cn As New SqlConnection("Data Source=DBServer;Initial Catalog=AdventureWorks;User ID=dbuserid;Password=dbpasswd")
      _cn.Open()
      Using _adapter As New SqlDataAdapter()
        Using _cmd As New SqlCommand("SELECT * FROM HumanResources.Department", _cn)
          _adapter.SelectCommand = _cmd
          _adapter.Fill(_dsDepartment)
        End Using
        Using _cmd As New SqlCommand("SELECT * FROM HumanResources.Employee", _cn)
          _adapter.SelectCommand = _cmd
          _adapter.Fill(_dsEmployee)
        End Using
        Using _cmd As New SqlCommand("SELECT * FROM HumanResources.EmployeeAddress", _cn)
          _adapter.SelectCommand = _cmd
          _adapter.Fill(_dsEmployeeAddress)
        End Using
        Using _cmd As New SqlCommand("SELECT * FROM HumanResources.EmployeeDepartmentHistory", _cn)
          _adapter.SelectCommand = _cmd
          _adapter.Fill(_dsEmployeeDepartmentHistory)
        End Using
      End Using
      _cn.Close()
    End Using
  Catch ex As SqlException
    MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
  Catch ex As Exception
    MsgBox(ex.Message, MsgBoxStyle.Critical, "App Error")
  End Try
End Sub

Open in new window

Larry Rungren

ASKER
These solutions work great if all the requirements for data retrieval are known at the start of the app.  In my case I enter and retrieve data and then based on user input I want to retrieve data from other table(s).  This happens many times in the input process but I can't seem to be able to populate separate datasets dynamically.
EX;  I read a record fromtermsummarycontrol based on the value of meeting retrieved from meet_master, if none the continue and based on meeting value run aan embedded crysatal report, then deal with other multiple tables all based on existence of records and the values, if any, from those tables. this was really a fairly simple task in VB6.  If you want I c an send you a chunk of the VB code to show explicit example of what was done in the original app
Athar Syed

@Jeff_Kingston the code I provided was only a demo snippet of how it is possible to populate multiple DataSet. You can create DataSet(s) as per requirement in your application.

Your Code:
1. Open TermSummaryControl into ds
2. If ds has no rows then populate Meet_Master in dt.
3. Then you try to set txtGroup.Text to a value from dt.

Problem:
If ds has values, then dt is never populated and hence you get error when you access dt.Tables(0)!!!

Solution:
1. Populate dt along with ds
2. Do a check if dt IsNot Nothing Then set value to txtGroup or else don't set the value.

Advice:
To reduce your Adapters and Connections put it into a separate Function
Public Function GetDataSet(query As String) As DataSet
  Dim _return As DataSet = Nothing
  Try
    Using _cn As New SqlConnection("Connection_String")
      _cn.Open()
      Using _cmd As New SqlCommand(query, _cn)
        Using _adapter As New SqlDataAdapter(_cmd)
          _return = New DataSet()
          _adapter.Fill(_return)
        End Using
      End Using
      _cn.Close()
    End Using
  Catch ex As SqlException
    ' log sql specific error
  Catch ex As Exception
    ' log geenral error 
  End Try
  Return _return
End Function

Open in new window


And then simple call the function
Using ds = GetDataSet(String.Format("SELECT * FROM termsummarycontrol WHERE (product_code='{0}'", txtProductCode.Text))
  If ds IsNot Nothing Then
    ' Do your work here because you know the DataSet is not empty
    If ds.Tables.Count() > 0 And ds.Tables(0).Rows.Count() > 0 Then
      ' Now you know you have at least table and it has at least one row
    End If
  End If
End Using

Open in new window


You can carry on similarly creating other DataSets and use them to your requirement.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Larry Rungren

ASKER
is there a special "imports" needed to used getdataset?
Athar Syed

@Jeff_Kingston The function is defined in the post itself, under Advice.
Larry Rungren

ASKER
I I use the function and the call from you post I am getting the error

Variable '<variablename>' hides a variable in an enclosing block.  referring to ds?

        Dim ds As New DataSet
        Dim dt As New DataSet
        Dim SummarycontrolAdp As SqlDataAdapter
        Dim MeetMasterAdp As SqlDataAdapter
        Dim connectionString = "Data Source=mcle_sql2008\mcle_sql2008;Initial Catalog=imis_mcle_prod;User ID=sa;Password=donaldduck"
        Dim workdate As Date
        sqlCnn = New SqlConnection(connectionString)

        If e.KeyChar = Convert.ToChar(13) Then

            'Using ds = GetDataSet(String.Format("SELECT * FROM termsummarycontrol WHERE product_code = '" & txtProductCode.Text & "'"))
            '    If ds IsNot Nothing Then
            '        ' Do your work here because you know the DataSet is not empty
            '        If ds.Tables.Count() > 0 And ds.Tables(0).Rows.Count() > 0 Then
            '            ' Now you know you have at least table and it has at least one row
            '        End If
            '    End If
            'End Using

            SummarycontrolAdp = New SqlDataAdapter("select * from termsummarycontrol where product_code = '" & txtProductCode.Text & "'", sqlCnn)

            Try
                SummarycontrolAdp.Fill(ds)
                If ds.Tables(0).Rows.Count = 0 Then
                    MsgBox("Does not exist in Term Summary. Create a record?", vbYesNo)
                    'If vbYesNo = 4 Then
                    '    MeetMasterAdp = New SqlDataAdapter("select * from meet_master where meeting = '" & txtProductCode.Text & "'", sqlCnn)
                    '    Try
                    '        MeetMasterAdp.Fill(dt)
                    '    Catch ex As Exception
                    '        If dt.Tables(1).Rows.Count = 0 Then
                    '            MsgBox("No program group available. Selection of other Seminars  not possible.. Continue?", vbYesNo)
                    '        End If
                    '    End Try
                    'End If



  Public Function GetDataSet(query As String) As DataSet
        Dim _return As DataSet = Nothing
        Try
            Using _cn As New SqlConnection("Data Source=mcle_sql2008\mcle_sql2008;Initial Catalog=imis_mcle_prod;User ID=sa;Password=donaldduck")
                _cn.Open()
                Using _cmd As New SqlCommand(query, _cn)
                    Using _adapter As New SqlDataAdapter(_cmd)
                        _return = New DataSet()
                        _adapter.Fill(_return)
                    End Using
                End Using
                _cn.Close()
            End Using
        Catch ex As SqlException
            ' log sql specific error
        Catch ex As Exception
            ' log geenral error
        End Try
        Return _return
    End Function
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Larry Rungren

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Larry Rungren

ASKER
Been away for a while, so I'm late getting back to you.
After some twists and turns your help has allowed me to successfully solve the problem

Thanks for the assist