Link to home
Create AccountLog in
Avatar of Larry Rungren
Larry RungrenFlag 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
Avatar of Randy Poole
Randy Poole
Flag of United States of America image

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.
Avatar of Larry Rungren

ASKER

Separate datasets, separate sql commands,  How doe one fill 2 datasets?
Avatar of 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
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

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
@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.
is there a special "imports" needed to used getdataset?
@Jeff_Kingston The function is defined in the post itself, under Advice.
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
ASKER CERTIFIED SOLUTION
Avatar of Larry Rungren
Larry Rungren
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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