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
Larry RungrenDirector of TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
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 RungrenDirector of TechnologyAuthor Commented:
Separate datasets, separate sql commands,  How doe one fill 2 datasets?
Anil GolamariCommented:
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
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Athar SyedCommented:
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 RungrenDirector of TechnologyAuthor Commented:
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 SyedCommented:
@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.
Larry RungrenDirector of TechnologyAuthor Commented:
is there a special "imports" needed to used getdataset?
Athar SyedCommented:
@Jeff_Kingston The function is defined in the post itself, under Advice.
Larry RungrenDirector of TechnologyAuthor Commented:
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
Larry RungrenDirector of TechnologyAuthor Commented:
This happens when I uncomment the call to the function and comment out the original adapter code below that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry RungrenDirector of TechnologyAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.