gogetsome
asked on
cycle through dataset
Hello, I have a dataset populated from a dataadapter (code below) the dataset could have any amount of rows from 1-10.
I want to use four buttons: first, next, previous, last to cycle/iterate through the dataset; going foward and backward... You get the idea. Like paging with an asp.net formview or gridview.
How can this be accomplished with the click events of the four buttons?
Here is my code:
Private Sub fillLifeIssuesCB()
Dim theSQL As String
Dim DA As OleDb.OleDbDataAdapter
Dim dsTitle As DataSet
theSQL = "SELECT DISTINCT ltrim(Title) as Title from LifeIssues"
DA = New OleDb.OleDbDataAdapter(the SQL, CN)
dsTitle = New DataSet
DA.Fill(dsTitle)
cboTitle.DataSource = dsTitle.Tables(0).DefaultV iew
cboTitle.DisplayMember = dsTitle.Tables(0).Columns( "Title").T oString
End Sub
Private Sub cboTitle_SelectedValueChan ged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboTitle.SelectedValueChan ged
Dim title As String = cboTitle.SelectedValue.Ite m("Title")
Dim theSql As String
Dim DA As OleDb.OleDbDataAdapter
Dim dsLifeIssues As DataSet
Dim strTitle As String = title
theSql = "SELECT ID, Title, Question, Commentary, Verse, Passage FROM LifeIssues WHERE title = '" & strTitle & "'"
DA = New OleDb.OleDbDataAdapter(the Sql, CN)
dsLifeIssues = New DataSet
DA.Fill(dsLifeIssues)
lblTitle.Text = dsLifeIssues.Tables(0).Row s(0).Item( "Title")
lblQuestion.Text = dsLifeIssues.Tables(0).Row s(0).Item( "Question" )
lblCommentary.Text = dsLifeIssues.Tables(0).Row s(0).Item( "Commentar y")
lblVerse.Text = dsLifeIssues.Tables(0).Row s(0).Item( "Verse")
lblPassage.Text = dsLifeIssues.Tables(0).Row s(0).Item( "Passage")
End Sub
I want to use four buttons: first, next, previous, last to cycle/iterate through the dataset; going foward and backward... You get the idea. Like paging with an asp.net formview or gridview.
How can this be accomplished with the click events of the four buttons?
Here is my code:
Private Sub fillLifeIssuesCB()
Dim theSQL As String
Dim DA As OleDb.OleDbDataAdapter
Dim dsTitle As DataSet
theSQL = "SELECT DISTINCT ltrim(Title) as Title from LifeIssues"
DA = New OleDb.OleDbDataAdapter(the
dsTitle = New DataSet
DA.Fill(dsTitle)
cboTitle.DataSource = dsTitle.Tables(0).DefaultV
cboTitle.DisplayMember = dsTitle.Tables(0).Columns(
End Sub
Private Sub cboTitle_SelectedValueChan
Dim title As String = cboTitle.SelectedValue.Ite
Dim theSql As String
Dim DA As OleDb.OleDbDataAdapter
Dim dsLifeIssues As DataSet
Dim strTitle As String = title
theSql = "SELECT ID, Title, Question, Commentary, Verse, Passage FROM LifeIssues WHERE title = '" & strTitle & "'"
DA = New OleDb.OleDbDataAdapter(the
dsLifeIssues = New DataSet
DA.Fill(dsLifeIssues)
lblTitle.Text = dsLifeIssues.Tables(0).Row
lblQuestion.Text = dsLifeIssues.Tables(0).Row
lblCommentary.Text = dsLifeIssues.Tables(0).Row
lblVerse.Text = dsLifeIssues.Tables(0).Row
lblPassage.Text = dsLifeIssues.Tables(0).Row
End Sub
ASKER
Thanks Appari for helping. I'm spoiled with asp.net.
The code you provided is looping through the combobox values and is throwing this error:
unable to cast object of type 'system.data.dataview' to type 'system.data.datatable'.
The value of the combobox is used to filter the select statement for dsLifeIssues dataset. The dsLifeIssues dataset is the one I want to loop through.
The code you provided is looping through the combobox values and is throwing this error:
unable to cast object of type 'system.data.dataview' to type 'system.data.datatable'.
The value of the combobox is used to filter the select statement for dsLifeIssues dataset. The dsLifeIssues dataset is the one I want to loop through.
small mistake in
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
Try
With cboTitle
Dim myCurrencyManager As CurrencyManager
myCurrencyManager = CType(.BindingContext(.Dat aSource), CurrencyManager)
If myCurrencyManager.Position = CType(.DataSource, DataTable).Rows.Count - 1 Then
myCurrencyManager.Position = 0
Else
myCurrencyManager.Position = myCurrencyManager.Position + 1
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
Try
With cboTitle
Dim myCurrencyManager As CurrencyManager
myCurrencyManager = CType(.BindingContext(.Dat
If myCurrencyManager.Position
myCurrencyManager.Position
Else
myCurrencyManager.Position
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Sub
ASKER
No that did not do anything. If I comment out the MsgBox(ex.Message) the code works well to cycle through the combobox's values. BUT, the combobox value is only used to filter (see the WHERE condition above) the data of which populates the second dataset. The second dataset is the one I want to loop through.
For example: if the user selects the abortion value of the combobox that value is used for the dslifesiiues datset to bring back the rows where the title = abortion. It is those records that I want to loop through and not the combobox.
Am I missing something here?
For example: if the user selects the abortion value of the combobox that value is used for the dslifesiiues datset to bring back the rows where the title = abortion. It is those records that I want to loop through and not the combobox.
Am I missing something here?
ASKER
One more though that might help.
If I change
txtPassage.Text = dsLifeIssues.Tables(0).Row s(0).Item( "Passage")
to
txtPassage.Text = dsLifeIssues.Tables(0).Row s(1).Item( "Passage")
where I change the row value to 1 the next abortion row is displayed.
If I change
txtPassage.Text = dsLifeIssues.Tables(0).Row
to
txtPassage.Text = dsLifeIssues.Tables(0).Row
where I change the row value to 1 the next abortion row is displayed.
try something like this
add a variable at form level
private curRowIndex as integer = 0
Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
Try
With cboTitle
If curRowIndex = 0 Then
curRowIndex = dsLifeIssues.Tables(0).Row s.Count - 1
Else
curRowIndex = curRowIndex - 1
End If
lblTitle.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Title")
lblQuestion.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Question" )
lblCommentary.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Commentar y")
lblVerse.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Verse")
lblPassage.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Passage")
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
Try
With cboTitle
If curRowIndex = dsLifeIssues.Tables(0).Row s.Count - 1 Then
curRowIndex = 0
Else
curRowIndex = curRowIndex + 1
End If
lblTitle.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Title")
lblQuestion.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Question" )
lblCommentary.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Commentar y")
lblVerse.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Verse")
lblPassage.Text = dsLifeIssues.Tables(0).Row s(curRowIn dex).Item( "Passage")
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Sub
add a variable at form level
private curRowIndex as integer = 0
Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
Try
With cboTitle
If curRowIndex = 0 Then
curRowIndex = dsLifeIssues.Tables(0).Row
Else
curRowIndex = curRowIndex - 1
End If
lblTitle.Text = dsLifeIssues.Tables(0).Row
lblQuestion.Text = dsLifeIssues.Tables(0).Row
lblCommentary.Text = dsLifeIssues.Tables(0).Row
lblVerse.Text = dsLifeIssues.Tables(0).Row
lblPassage.Text = dsLifeIssues.Tables(0).Row
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
Try
With cboTitle
If curRowIndex = dsLifeIssues.Tables(0).Row
curRowIndex = 0
Else
curRowIndex = curRowIndex + 1
End If
lblTitle.Text = dsLifeIssues.Tables(0).Row
lblQuestion.Text = dsLifeIssues.Tables(0).Row
lblCommentary.Text = dsLifeIssues.Tables(0).Row
lblVerse.Text = dsLifeIssues.Tables(0).Row
lblPassage.Text = dsLifeIssues.Tables(0).Row
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is some different approch
1) Declare currentpos variable on your form base
2) suppose you have 4 button first,next,previous,last and call this procedure on each button
Private Sub ButtonFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonFirst.Click
With Me.DataGridView1
.AutoGenerateColumns = True
.DataSource = GetDataPos(dt, 1, 10).DefaultView 'here dt = your dataset, (1=first,2=next,3=pre,4=la st),
10= your row you want to display on each page)
.AutoSizeRowsMode = DataGridViewAutoSizeRowsMo de.Display edCellsExc eptHeaders
.BorderStyle = BorderStyle.None
.EditMode = DataGridViewEditMode.EditO nEnter
.Refresh()
End With
End Sub
3) the procedure is simple its takes your dataset as parameter, your navigation flag, your differnece mostly this is
static but i wrote it dynamic
Function GetDataPos(ByVal ds As DataSet, ByVal pos As Integer, ByVal diff As Integer) As DataTable
'pos = 1 as first,2 as next,3 as previous, 4 as last
Dim dt As DataTable
dt = ds.Tables(0).Clone
Dim i As Integer
Dim startpoint As Integer, endpoint As Integer
Select Case pos
Case 1 'first
startpoint = 0
endpoint = startpoint + diff
Case 4 'last
startpoint = ds.Tables(0).Rows.Count - diff
endpoint = ds.Tables(0).Rows.Count - 1
Case 3 'next
startpoint = currentpos
endpoint = startpoint + diff
If endpoint > ds.Tables(0).Rows.Count - 1 Then
endpoint = startpoint - ds.Tables(0).Rows.Count - 1
End If
Case 2 'previous
startpoint = startpoint - diff
endpoint = currentpos
If startpoint < 0 Then
startpoint = 0
endpoint = diff
End If
End Select
Dim dr As DataRow
For i = startpoint To endpoint
dr = dt.NewRow
dr.ItemArray = ds.Tables(0).Rows(i).ItemA rray
dt.Rows.Add(dr)
Next
Return dt
End Function
may be you need some testing but you have to test inside this procedure which returns selected rows
and that you directly bind with your datagridview
-best luck
vsvb
1) Declare currentpos variable on your form base
2) suppose you have 4 button first,next,previous,last and call this procedure on each button
Private Sub ButtonFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonFirst.Click
With Me.DataGridView1
.AutoGenerateColumns = True
.DataSource = GetDataPos(dt, 1, 10).DefaultView 'here dt = your dataset, (1=first,2=next,3=pre,4=la
10= your row you want to display on each page)
.AutoSizeRowsMode = DataGridViewAutoSizeRowsMo
.BorderStyle = BorderStyle.None
.EditMode = DataGridViewEditMode.EditO
.Refresh()
End With
End Sub
3) the procedure is simple its takes your dataset as parameter, your navigation flag, your differnece mostly this is
static but i wrote it dynamic
Function GetDataPos(ByVal ds As DataSet, ByVal pos As Integer, ByVal diff As Integer) As DataTable
'pos = 1 as first,2 as next,3 as previous, 4 as last
Dim dt As DataTable
dt = ds.Tables(0).Clone
Dim i As Integer
Dim startpoint As Integer, endpoint As Integer
Select Case pos
Case 1 'first
startpoint = 0
endpoint = startpoint + diff
Case 4 'last
startpoint = ds.Tables(0).Rows.Count - diff
endpoint = ds.Tables(0).Rows.Count - 1
Case 3 'next
startpoint = currentpos
endpoint = startpoint + diff
If endpoint > ds.Tables(0).Rows.Count - 1 Then
endpoint = startpoint - ds.Tables(0).Rows.Count - 1
End If
Case 2 'previous
startpoint = startpoint - diff
endpoint = currentpos
If startpoint < 0 Then
startpoint = 0
endpoint = diff
End If
End Select
Dim dr As DataRow
For i = startpoint To endpoint
dr = dt.NewRow
dr.ItemArray = ds.Tables(0).Rows(i).ItemA
dt.Rows.Add(dr)
Next
Return dt
End Function
may be you need some testing but you have to test inside this procedure which returns selected rows
and that you directly bind with your datagridview
-best luck
vsvb
ASKER
Thanks for your efforts Appari. It know works.
Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
Try
With cboTitle
Dim myCurrencyManager As CurrencyManager
myCurrencyManager = CType(.BindingContext(.Dat
If myCurrencyManager.Position
myCurrencyManager.Position
Else
myCurrencyManager.Position
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
Try
With cboTitle
Dim myCurrencyManager As CurrencyManager
myCurrencyManager = CType(.BindingContext(.Dat
myCurrencyManager.Position
If myCurrencyManager.Position
myCurrencyManager.Position
Else
myCurrencyManager.Position
End If
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
End Sub