Record Navigation in VB.NET: How To?

[palehorse]

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]

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]

Ok I have the same problem, It is quite long code so bare with it.

[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]

Personally I'd read it all into a datatable and nav. through that instead of going to the DB each time.

TPM

 

[palehorse]

Would you mind explaining how to do that? I am not sure how to go about doing that.

 

[levyuk]

yeah what he said

 

[Paszt]

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]

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]

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]

This is how I've done it now,

[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]

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.

 

[levyuk]

I know but thats just the noes from my uni