Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

first, previous, next, last record buttons

Status
Not open for further replies.
JackBurton07

JackBurton07

IS-IT--Management
Oct 17, 2007
75
GB
Hi I want to create first, previous, next, last record buttons to navigate through my database however we are not permitted to use binding, only myrecordset instructions

I can do the first record button however the rest are baffling.

heres my code so far:
private sub firstrecord_btn_click

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Environment.CurrentDirectory & "\client.mdb"
con.Open()
sql = "select* from client"
da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "clientds")

con.Close()
mydatarows = ds.Tables("clientds").Select("id_no='145'")
txtSurname.Text = mydatarows(0).Item("surname")
txtdob.Text = mydatarows(0).Item("dob")
txtFirstName.Text = mydatarows(0).Item("firstname")
txthometel.Text = mydatarows(0).Item("hometel")
txtbuscode.Text = mydatarows(0).Item("businesscode")
txthomecode.Text = mydatarows(0).Item("homecode")
txtcellcode.Text = mydatarows(0).Item("cellcode")
txtbustel.Text = mydatarows(0).Item("businesstel")
txtcelltel.Text = mydatarows(0).Item("celltel")
txthomeemail.Text = mydatarows(0).Item("homeemail")
txtbusinessemail.Text = mydatarows(0).Item("businessemail")
txtcellemail.Text = mydatarows(0).Item("cellemail")
txtOnInit.Text = mydatarows(0).Item("oninit")
txtstartdate.Text = mydatarows(0).Item("clientstart")
txtInvestment1.Text = mydatarows(0).Item("investment1")
txtID_No.Text = mydatarows(0).Item("id_no")
cboProduct1.Text = mydatarows(0).Item("product1")
cboCountry.Text = mydatarows(0).Item("country")
txtHouseNo.Text = mydatarows(0).Item("houseno")
txtAddress1.Text = mydatarows(0).Item("address1")
txtAddress2.Text = mydatarows(0).Item("address2")
txtTown.Text = mydatarows(0).Item("address3")
txtPcode.Text = mydatarows(0).Item("address4")
cboPrivateCorp.Text = mydatarows(0).Item("privatecorp")
cboContactPref.Text = mydatarows(0).Item("prefcontact")
txt_Notes.Text = mydatarows(0).Item("notes")
letterstartdate.Text = mydatarows(0).Item("clientstart")
letterproduct1.Text = mydatarows(0).Item("product1")
letterinvestment1.Text = mydatarows(0).Item("investment1")

txtdaysrem.Text = getnoofdays(txtstartdate.Text, txtterm1.Text)

This is ok - Idont know how to do the other buttons


Your help would be very very greatly appreciated

thanks

jb
 
Sort by date Sort by votes
Last Button
txtSurname.Text = mydatarows(mydatarows.Length-1).Item("surname")
etc., etc.

Next Button
SomeCounterInteger += 1
txtSurname.Text = mydatarows(SomeCounterInteger).Item("surname")
etc., etc.

You also want to add logic not to go outside the bounds of your array, and to handle null values in your datatable/textboxes.
 
Upvote 0 Downvote
Hi Have a look through this

Public Class fContacts
Dim inc As Integer
Dim RecordNumber As Integer
Dim maxrows As Integer
Dim myDataSet As New DataSet

Private Sub fContacts_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
GetContacts()
End Sub

Sub GetContacts()

Dim objConn As New SqlConnection
objConn.ConnectionString = strConn

Dim objcmd As SqlCommand
Dim myAdapter As New SqlDataAdapter


'Open the Database
Try

objcmd = New SqlCommand("spContacts", objConn)
objcmd.CommandType = CommandType.StoredProcedure
myAdapter.SelectCommand = objcmd
myAdapter.SelectCommand.Connection = objConn
myAdapter.Fill(myDataSet, "tContacts")

maxrows = myDataSet.Tables("tContacts").Rows.Count
inc = 0
RecordNumber = 1

navigateRecords()

Catch ex As Exception
MsgBox(ex.Message)
End Try
objConn.Close()

End Sub

Private Sub cNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cNext.Click
inc = inc + 1
RecordNumber += 1
navigateRecords()

End Sub


Private Sub cPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cPrevious.Click
inc = inc - 1
RecordNumber -= 1
navigateRecords()

End Sub

Private Sub cLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cLast.Click

inc = maxrows - 1
RecordNumber = maxrows
navigateRecords()

End Sub

Private Sub cFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cFirst.Click
inc = 0
RecordNumber = 1
navigateRecords()

End Sub

Sub navigateRecords()
cContactID.Text = myDataSet.Tables("tContacts").Rows(inc).Item("ContactID")
' Add the global ContactID here
ContactID = myDataSet.Tables("tContacts").Rows(inc).Item("ContactID")
'Continue Binding Data
cSurname.Text = Trim(myDataSet.Tables("tContacts").Rows(inc).Item("Surname").ToString)
cForename.Text = Trim(myDataSet.Tables("tContacts").Rows(inc).Item("Forename").ToString)
cAddress1.Text = Trim(myDataSet.Tables("tContacts").Rows(inc).Item("Address1").ToString)
cAddress2.Text = Trim(myDataSet.Tables("tContacts").Rows(inc).Item("Address2").ToString)
cAddress3.Text = Trim(myDataSet.Tables("tContacts").Rows(inc).Item("Address3").ToString)


If RecordNumber = 1 Then
cFirst.Enabled = False
cPrevious.Enabled = False
cLast.Enabled = True
cNext.Enabled = True
Else
cFirst.Enabled = True
cPrevious.Enabled = True
cLast.Enabled = True
cNext.Enabled = True
End If

If RecordNumber = maxrows Then
cLast.Enabled = False
cNext.Enabled = False
cFirst.Enabled = True
cPrevious.Enabled = True
Else
cLast.Enabled = True
cNext.Enabled = True
End If

cRecordCount.Text = RecordNumber & " of " & maxrows

End Sub

Cheers
 
Upvote 0 Downvote
thanks for this im trying to configure it to adodb, can you help?
 
Upvote 0 Downvote
Hi try this

Sub GetContacts()

Dim con As New SqlConnection
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Environment.CurrentDirectory & "\client.mdb"


Dim myadapter As New OleDbDataAdapter


'Open the Database
Try

con.Open()
sql = "select * from client"
myadapter = New OleDb.OleDbDataAdapter(sql, con)

myadapter.Fill(ds, "clientds")


maxrows = myDataSet.Tables("tContacts").Rows.Count
inc = 0
RecordNumber = 1

navigateRecords()

Catch ex As Exception
MsgBox(ex.Message)
End Try
objConn.Close()

End Sub
 
Upvote 0 Downvote
myadapter = New OleDb.OleDbDataAdapter(sql, con)

It stops at the above line - the sql

systems.data.sql is a namespace and cannot be used in an expression
 
Upvote 0 Downvote
sql is a reserved word try below.


Sub GetContacts()

Dim con As New SqlConnection
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Environment.CurrentDirectory & "\client.mdb"


Dim myadapter As New OleDbDataAdapter
dim sqlString as string


'Open the Database
Try

con.Open()
sqlString = "select * from client"
myadapter = New OleDb.OleDbDataAdapter(sqlString, con)

myadapter.Fill(ds, "clientds")


maxrows = myDataSet.Tables("tContacts").Rows.Count
inc = 0
RecordNumber = 1

navigateRecords()

Catch ex As Exception
MsgBox(ex.Message)
End Try
objConn.Close()

End Sub
 
Upvote 0 Downvote
probably
Code: 
Dim con As New [s]SqlConnection[/s] Oledb.OledbConnection

________________________________________________________
Zameer Abdulla
Help to find Missing people
 
Upvote 0 Downvote
The issue below occurs ( I had to also change " New sqlconnection" to " New sqlClient.sqlconnection", this may be incorrect)


Error 1

Overload resolution failed because no
accessible 'New' can be called with these arguments:
'Public Sub New(selectCommandText As String, selectConnection As System.Data.OleDb.OleDbConnection)': Value of type 'System.Data.SqlClient.SqlConnection' cannot be converted to 'System.Data.OleDb.OleDbConnection'.

'Public Sub New(selectCommandText As String, selectConnectionString As String)': Value of type 'System.Data.SqlClient.SqlConnection' cannot be converted to 'String'.


 
Upvote 0 Downvote
OK here is updated version of the code(using Northwind AccessDB.) posted by saintedmunds
Code: 
Imports System.Data.OleDb
Public Class fContacts
    Dim inc As Integer
    Dim RecordNumber As Integer
    Dim maxrows As Integer
    Dim myDataSet As New DataSet
    Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Password="""";User ID=Admin;Data Source=""C:\Zameer\Northwind.mdb"";"

    Private Sub fContacts_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        GetContacts()
    End Sub

    Sub GetContacts()
        Dim objConn As New OleDbConnection
        objConn.ConnectionString = strConn

        Dim objcmd As OleDbCommand
        Dim myAdapter As New OleDbDataAdapter


        'Open the Database
        Try

            objcmd = New OleDbCommand("QryEmployees", objConn)
            objcmd.CommandType = CommandType.StoredProcedure
            myAdapter.SelectCommand = objcmd
            myAdapter.SelectCommand.Connection = objConn
            myAdapter.Fill(myDataSet, "Employees")
            maxrows = myDataSet.Tables("Employees").Rows.Count
            inc = 0
            RecordNumber = 1

            navigateRecords()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        objConn.Close()

    End Sub

    Private Sub cNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cNext.Click
        inc = inc + 1
        RecordNumber += 1
        navigateRecords()

    End Sub


    Private Sub cPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cPrevious.Click
        inc = inc - 1
        RecordNumber -= 1
        navigateRecords()

    End Sub

    Private Sub cLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cLast.Click

        inc = maxrows - 1
        RecordNumber = maxrows
        navigateRecords()

    End Sub

    Private Sub cFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cFirst.Click
        inc = 0
        RecordNumber = 1
        navigateRecords()

    End Sub

    Sub navigateRecords()
        Me.txtEmployeeID.Text = Trim(myDataSet.Tables("Employees").Rows(inc).Item("EmployeeID"))
        Me.txtLastName.Text = Trim(myDataSet.Tables("Employees").Rows(inc).Item("LastName"))
        Me.txtFirstName.Text = Trim(myDataSet.Tables("Employees").Rows(inc).Item("FirstName"))
        '..... so on

        If RecordNumber = 1 Then
            cFirst.Enabled = False
            cPrevious.Enabled = False
            cLast.Enabled = True
            cNext.Enabled = True
        Else
            cFirst.Enabled = True
            cPrevious.Enabled = True
            cLast.Enabled = True
            cNext.Enabled = True
        End If

        If RecordNumber = maxrows Then
            cLast.Enabled = False
            cNext.Enabled = False
            cFirst.Enabled = True
            cPrevious.Enabled = True
        Else
            cLast.Enabled = True
            cNext.Enabled = True
        End If

        cRecordCount.Text = RecordNumber & " of " & maxrows

    End Sub


End Class

________________________________________________________
Zameer Abdulla
Help to find Missing people
 
Upvote 0 Downvote
Fantastic - its working! Thanks so much!

Just one more quick q : how can I modify maxrows to increment when I add a record to the database?

 
Upvote 0 Downvote
Just run "Sub GetContacts()" again..that is all

________________________________________________________
Zameer Abdulla
Help to find Missing people
 
Upvote 0 Downvote
When I'm cycling through the records I notice that my textfields arent staying as false enabled

Is there any override for the next button to take priority over the other validation in the tex fields.

e.g
Private Sub TxtName_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles txtFirstName.Validating



If txtFirstName.Text.Length = 0 Then
MsgBox("Please enter the client's first name", MsgBoxStyle.Exclamation, "Empty Field")
txtSurname.Enabled = False

txtFirstName.Focus()


Else
Dim counter As Integer
Dim numTrue As Boolean

numTrue = False
fntk.Visible = True
Me.txtSurname.Enabled = True 'tab moves down to next text box for user to input
txtSurname.Focus()

Me.txtFirstName.Enabled = True
Me.txthometel.Enabled = False
Me.txtbustel.Enabled = False
Me.txtcelltel.Enabled = False
Me.txtHomeEmail.Enabled = False
Me.txtBusinessEmail.Enabled = False
Me.txtCellEMail.Enabled = False
Me.txtOnInit.Enabled = False
Me.txtstartdate.Enabled = False
Me.txtInvestment1.Enabled = False

Me.txtterm1.Enabled = False


Me.txtdob.Enabled = False
cboContactPref.Enabled = False
cboProduct1.Enabled = False



Me.txtOnInit.Enabled = False

cboCountry.Enabled = False
Me.txtHouseNo.Enabled = False
Me.txtAddress1.Enabled = False
Me.txtAddress2.Enabled = False
Me.txtTown.Enabled = False
Me.txtPcode.Enabled = False
cboPrivateCorp.Enabled = False
cboPrivateCorp.Enabled = False
Me.txt_Notes.Enabled = False
For counter = 0 To txtFirstName.Text.Length - 1
If IsNumeric(txtFirstName.Text.Chars(counter)) Then
numTrue = True
counter = txtFirstName.Text.Length
End If
Next
If numTrue = True Then

txtFirstName.Focus()
MsgBox("Please enter only alphabetic characters for the first name", MsgBoxStyle.Exclamation, "Invalid Format")
txtSurname.Enabled = False

End If
End If
End Sub






 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question
COUNT all items in trteeview in last level 1
Replies
4
Views
639
2009luca
2009luca
sal21
  • Locked
  • Question
delete first line in txt and rewrite
Replies
2
Views
473
sal21
sal21
sal21
  • Locked
  • Question
LAST DATE OF MODIFIED FILE on WEB 1
Replies
4
Views
482
strongm
strongm
sal21
  • Locked
  • Question
FILL RICHTEXTBOX WITH RECORDSET
Replies
4
Views
607
strongm
strongm
sal21
  • Locked
  • Question
UPDATE DAO RECORDSET 1
Replies
11
Views
922
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top