Display the FirstRecord, PreviousRecord, NextRecord and LastRecord

[P.manidas]

Dear sir/Madam,

In my codes CmdDisplay button display only first record from the table and button2 add the all name (Ename field) to combobox1 from the table. How can i show FirstRecord, PreviousRecord, NextRecord and LastRecord using OleDbCommand and OleDBdataReader. Please guide me.

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\emp.mdb;")
        cn.Open()
End Sub



Private Sub CmdDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdDisplay.Click

cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader()

If dr.Read() = True Then
	TextBox1.Text = dr("empno")
	TextBox2.Text = dr("Ename")
	TextBox3.Text = dr("Department")
End If

End Sub


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
cmd = New OleDbCommand("select * from table1", cn)
dr = cmd.ExecuteReader()

Do While dr.Read()
	ComboBox1.Items.Add(dr("ename"))
Loop
End Sub

[sandeepparekh9]

see if this helps : http://www.akadia.com/services/dotnet_databinding.html

or this (its with example) :http://www.vb-helper.com/howto_net_bind_collection.html

[P.manidas]

Dear SandeepParekh9,

Thanks for replying sir, may be both the link are nice example as pointed out by respected member Debasisdas but for me it is hard to understand because i am totally new in VB.net language. I have no idea about currency manager, Binding Context, data binding etc.

However, i am trying to understand to find out the solution as per my requirement.

Earlier I have done it using DBCommandbuider, DataAdapter, Datatable, Dataset etc. But when i have tried it using dataReader and DBcommand i am getting pause. Because i am not getting the logic/Command how to or where to use the row_Position for moving next or previous record.

Sir, if any other solution is there please guide me again.

[debasisdas]

Then try this .

[sandeepparekh9]

well ..

check this out : http://www.startvbdotnet.com/ado/Simplebinding.aspx

and this too : http://msdn.microsoft.com/en-us/library/ms973824.aspx

[P.manidas]

Dear Debasisdas and SandeepParekh9,

Thanks for replying once again. Actually, i have not asked for data binding here on my original question. I have just asked for displaying next, previous,..... records using OleDBCommand and OleDBDataReader.

Although, all the information mentioned by you, will also help me when i will go for databindig. Thanks for that.

[sandeepparekh9]

you know P.manidas if you use OleDbCommand and OleDBDataReader then each time you press next,previos you will have to go through all the rows in table for each time you press this button.. it will be really costly in sense of time and memory..

[P.manidas]

Dear SandeepParekh9,

Thanks for replying again sir, that means we can't specify the record position by using those two. If it is right then it will not be good idea for using OledbCommand and OledbDataReader to display next or previous... records.

[sandeepparekh9]

practically its possible .. but time consuming as i said earlier

[Luc001]

Hi,

An easier way is to use the BindingNavigator Class. Once you are able to bind to your data source, the rest is a walk-over. I'm posting a sample code I used before to demonstrate how it works.

This code uses the Northwind database so it is easy to recreate. You need for buttons: btnFirst, btnPrevious, btnNext and btnLast:

Imports System.Data.SqlClient

Public Class Form1

  'Private bindingSource1 As BindingSource
  Private bindingSource1 As BindingSource = New BindingSource()

  Private employeeTable As New DataTable
  Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
  Dim connection As New SqlConnection(connectionString)

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    'connection.ConnectionString = connectionString
    Dim employeeAdapter As New SqlDataAdapter _
      (New SqlCommand("Select * FROM Employees " _
              & "ORDER BY LastName", connection))
    connection.Open()
    employeeAdapter.Fill(employeeTable)
    connection.Close()
    ' Set the DataSource property of the BindingSource to the employee table.
    bindingSource1.DataSource = employeeTable

    ' Set up the binding to the ReportsTo column.
    Dim reportsToBinding As Binding = _
      TextBox2.DataBindings.Add("Text", bindingSource1, "ReportsTo", _
        True)

    ' Set the NullValue property for this binding.
    reportsToBinding.NullValue = "No Manager"


    ' Set up the remaining binding.
    TextBox1.DataBindings.Add("Text", bindingSource1, "LastName", True)

  End Sub

  ' Move through the data when the button is clicked.
  Private Sub btnNext_Click(ByVal sender As Object, _
    ByVal e As EventArgs) Handles btnNext.Click

    bindingSource1.MoveNext()

  End Sub

  ' Move through the data when the button is clicked.
  Private Sub btnPrev_Click(ByVal sender As Object, _
    ByVal e As EventArgs) Handles btnPrev.Click

    bindingSource1.MovePrevious()


  End Sub



  Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
    bindingSource1.MoveFirst()
  End Sub

  Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
    bindingSource1.MoveLast()
    connection.Open()
    Dim employeeAdapter As New SqlDataAdapter _
      (New SqlCommand("Select * FROM Employees " _
              & "ORDER BY LastName", connection))
    employeeTable.Clear()
    employeeAdapter.Fill(employeeTable)
    connection.Close()
    Dim count As Integer = employeeTable.Rows.Count
    
  End Sub

  End Class

[Luc001]

Hi,

For more explanation about Bindingsource, look here, and here.

[P.manidas]

Dear Luc001,

Thanks for replying sir, but i was not asking for data binding as i had mentioned earlier also. Thanks sir.

Dear SandeepParekh9,

I have done it with my own logic. But off course, it is not good idea because there are few drawback is there. Any way, i have done it. (Codes are attached)

As you had mentioned "practically its possible". Is there other ways, please let me know sir?

dim Myslno as integer=0

Private Sub BtnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNext.Click
        MySlno = MySlno + 1
        ST = "select * from table1 where empno="
        ST = ST + "'" + MySlno.ToString + "'"
        cmd = New OleDbCommand(ST, cn) ' where empno=" + "'" + TextBox1.Text + "'", cn)
        dr = cmd.ExecuteReader()

        dr.Read()
        If dr.HasRows Then
            TextBox1.Text = dr("empno")
            TextBox2.Text = dr("Ename")
            TextBox3.Text = dr("Department")
        Else
            MySlno = MySlno - 1
        End If
        dr.Close()
    End Sub

    Private Sub BtnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPrevious.Click
        If MySlno <> 0 Then
            MySlno = MySlno - 1
        End If
        ST = "select * from table1 where empno="
        ST = ST + "'" + MySlno.ToString + "'"
        cmd = New OleDbCommand(ST, cn) ' where empno=" + "'" + TextBox1.Text + "'", cn)
        dr = cmd.ExecuteReader()

        dr.Read()
        If dr.HasRows Then
            TextBox1.Text = dr("empno")
            TextBox2.Text = dr("Ename")
            TextBox3.Text = dr("Department")
        Else
            MySlno = MySlno + 1
        End If
        dr.Close()
    End Sub

    Private Sub BtnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click
        ST = "select * from table1"
        cmd = New OleDbCommand(ST, cn) ' where empno=" + "'" + TextBox1.Text + "'", cn)
        dr = cmd.ExecuteReader()

        dr.Read()

        TextBox1.Text = dr("empno")
        TextBox2.Text = dr("Ename")
        TextBox3.Text = dr("Department")
        MySlno = dr("empno")
        dr.Close()
    End Sub

    Private Sub BtnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnLast.Click
        ST = "select * from table1"
        cmd = New OleDbCommand(ST, cn) ' where empno=" + "'" + TextBox1.Text + "'", cn)
        dr = cmd.ExecuteReader()

        dr.Read()
        Do While dr.Read
            TextBox1.Text = dr("empno")
            TextBox2.Text = dr("Ename")
            TextBox3.Text = dr("Department")
            MySlno = dr("empno")
        Loop
        dr.Close()
    End Sub

[sahcinsalokhe]

Next n Previous button coding not working..........

[G_Waddell]

Hi
You may want to use a dataadapter instead of a datareader. Datareader is a forward only connected recordset. Datadapter will give you access to a dataset which alows you to go back and forth and is disconnected.