Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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
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.
However, if you really really need more than one DataSet, below is again yet another snippet :)
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
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
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
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
And then simple call the function
You can carry on similarly creating other DataSets and use them to your requirement.
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
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
You can carry on similarly creating other DataSets and use them to your requirement.
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_s
Dim workdate As Date
sqlCnn = New SqlConnection(connectionSt
If e.KeyChar = Convert.ToChar(13) Then
'Using ds = GetDataSet(String.Format("
' 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_s
_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
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_s
Dim workdate As Date
sqlCnn = New SqlConnection(connectionSt
If e.KeyChar = Convert.ToChar(13) Then
'Using ds = GetDataSet(String.Format("
' 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_s
_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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial