Record Navigation in VB.NET: How To?

palehorse

Active member
Joined
Jan 28, 2005
Messages
34
Programming Experience
1-3
I am able to do a search for a certain record in my Access Database, however, I also want the capability to click a button to navigate through each record and have the program know when I am at the last record so it can't go further, or know when I am at the first record so it can't go backwards.

I was hoping to get the most simple expanation on this.
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
Are you using dataBindings to display the data in textboxs?

Unfortunately, ADO.Net isn't too simple.

I always suggest using the DataForm Wizard to create a form with all the coding and a schema file, then you can view the code and see how it's done. Be sure to select the Navigation Controls option.

The simple answer is to compare the BindingManagerBase's Position and Count properties. That is if you're using DataBindings.
 

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
Ok I have the same problem, It is quite long code so bare with it.

VB.NET:
[size=2][color=#0000ff]
[size=2][color=#0000ff]Private[/color][/size][size=2][color=#000000] reader [/color][/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#000000] OleDbDataReader[/color][/size]
Function[/color][/size][size=2] Search()
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] cmd [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand
 
[/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbConnection1.ConnectionString = connString
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbConnection1.Open()
 
cmd.Connection = OleDbConnection1
 
cmd.Parameters.Add("@Enrolmentnumber", [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBox1.Text)
 
cmd.CommandType = CommandType.Text
 
cmd.CommandText = "select * from student where (Enrolmentnumber = @Enrolmentnumber)"
 
reader = cmd.ExecuteReader
 
reader.Read()
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxSal.Text = reader.Item("Salutation")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxCounty.Text = reader.Item("County")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxForename.Text = reader.Item("Forename")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxHousenumber.Text = reader.Item("Housenumber")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxPostCode.Text = reader.Item("Postcode")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxStreet.Text = reader.Item("Street")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxSurname.Text = reader.Item("Surname")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxTelephoneNo.Text = reader.Item("TelephoneNumber")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxTownCity.Text = reader.Item("TownCity")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxLocation.Text = reader.Item("Location")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxjobType.Text = reader.Item("Jobtype")
 
[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] i [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String
 
[/color][/size][size=2]i = reader.Item("Placed")
 
[/size][size=2][color=#0000ff]If[/color][/size][size=2] i = [/size][size=2][color=#0000ff]True[/color][/size][size=2][color=#0000ff]Then
 
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].CheckBoxPlaced.Checked = [/size][size=2][color=#0000ff]True
 
[/color][/size][size=2][color=#0000ff]Else
 
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].CheckBoxPlaced.Checked = [/size][size=2][color=#0000ff]False
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If
 
[/color][/size][size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception
 
NotificationWindow1.Notify(ex.ToString, 5000)
 
[/size][size=2][color=#0000ff]Finally
 
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbConnection1.Close()
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Function[/color][/size]

[size=2][color=#0000ff]Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] UtilityToolBar1_ForwardPressed([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] UtilityToolBar1.ForwardPressed
 
[/size][size=2][color=#0000ff]If[/color][/size][size=2] reader.Read() [/size][size=2][color=#0000ff]Then
 
[/color][/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxForename.Text = reader("Forename")'This doesn't work
 
[/size][size=2][color=#0000ff]Else
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub
 
[/color][/size][size=2][color=#0000ff]
[/color]Now for some reason when I click on the next button it doesn't work and gives me an error 'Additional information: Invalid attempt to Read when reader is closed'

I can't see where or why the reader is closed though.[/size]
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Personally I'd read it all into a datatable and nav. through that instead of going to the DB each time.

TPM
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
If you use the DataForm Wizard to create the form you can view the code and see how it's done.
To use the dataForm Wizard, add a new item to your project and select the Data Form Wizard template.
 

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
Ok i've used that dataform wizard, it was an AddIn correct?

Well i can use it fine but I have no idea where the code is kept. I can't find that anywhere.
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
This is what I'd do:
Dim a global int (row #)
Load the datatable
Then in your Nav. event (onclick etc) + or - the row # and for each item use datatable.rows(row #).item(columnnumber of item)
You'll need to add a row check in there as well to make sure the row isn't less that 0 or greater than the number of rows in your datatable.

Or instead you could bind the data to a datagrid and use that for the navigation and load the data from that. I do that all the time, as it's very use friendly. eg I have a contacts table to nav through, I hide all the columns exept the name. Then all the user has to do is scroll down untill they find the person they want and click on them. all the textboxes etc then load from the hiden columns of the datagrid.

Hope that helps

TPM
 

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
This is how I've done it now,

VB.NET:
[size=2][color=#0000ff]Private[/color][/size][size=2] reader [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader

[/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Form1_Load([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Object[/color][/size][size=2], [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] [/size][size=2][color=#0000ff]MyBase[/color][/size][size=2].Load

[/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbConnection1.Open()

reader = [/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbCommand1.ExecuteReader

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub

[/color][/size][size=2][/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Button2_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] Button2.Click

[/size][size=2][color=#0000ff]If[/color][/size][size=2] reader.Read [/size][size=2][color=#0000ff]Then

[/color][/size][size=2][/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxEn.Text = reader("EnrolmentNumber")

[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxSurname.Text = reader("Surname")

[/size][size=2][color=#0000ff]Else

[/color][/size][size=2][/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxEn.Text = "EOF"

[/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxSurname.Text = "EOF"

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub

[/color][/size][size=2][/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Form1_Closed([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Object[/color][/size][size=2], [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] [/size][size=2][color=#0000ff]MyBase[/color][/size][size=2].Closed

[/size][size=2][color=#0000ff]Me[/color][/size][size=2].OleDbConnection1.Close()

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size]
[size=2][color=#0000ff]
[/color][/size]
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
The DBA in me shudders when looking at that. Keeping a connection open the entire time the app. is running is not a good idea.
 
Top Bottom