Introduction
I wasn’t really sure if Google was not being effective, or if it was just me. I had been surfing the web for days searching for a simple solution to a simple problem: to be able to navigate through a database, using VCR-style controls (I can’t
believe I’ve just said VCR…I meant DVD controls.)
In ASP.NET, the easiest way to move from record to record is putting a datagrid or a datatable, which was exactly what I didn’t wanted to use.
I also came across a C# soft that did pretty much the same as Database Navigator, but the code was extremely difficult to understand (at least for me…of course).
Having a deadline and no solution in hand, this is what I’ve developed, which by the way, works nice:
The first attempt
As a first approach, I created the 4 basic buttons and a dataset bounded to different textboxes.
I also created an index that pointed to the current record position, which I stored in a session variable, and changed as I pressed the different navigation buttons;
It generated a new SQL instruction, requesting only the particular record I had to put on the screen at that precise moment, and everything was looking great, until I deleted one record in the middle of the db, and all I got was an error screen. I realized that that way wasn’t going to work.
The solution
After trying many different options, I thought of using a datagrid to hold all the records, but hidden to the user, and displaying the data only in textboxes: now I could place all our information anywhere on the form, and not in a table, as it’s implemented by default… And YES! this time it worked!
Installation:
Create a new ASP.NET VB blank project, named ‘databaseNavigator’; IIS's default folder and our project should be in:
C:\Inetpub\wwwroot\databaseNavigator’.
Simply overwrite the content of the directory with the ones included in the ZIP file.
Database:
In this example I have also included a small MS-Access database, but I created the application using MySQL.
I use the wizard, because I think it’s much easier (although it also has some dark areas, I know…I will write an article about that in the near future)
Points of Interest
DatabaseNavigator stores information in two session variables, actualRecordPosition and totalNumberOfRecords.
totalNumberOfRecords gets it's value during Page_Load(), counting all the records from our table:
dbConnection.Open()
SQL.CommandText = "SELECT COUNT(*) FROM membersTable"
Session("totalNumberOfRecords") = SQL.ExecuteScalar
dbConnection.Close()
dbAdapter.Dispose()
then the adapter is disposed. DatabaseNavigator is one-way communication only (readonly from the database). I am about to finish a two-way communications version, that includes INSERT, UPDATE and DELETE commands.
following is the code of the NEXT button, what basically it does is to increment the counter of the record position, unless we are standing at the end of the database.
Private Sub NEXTbutton_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles NEXTbutton.Click
Dim ARPTemp As Integer = CType(Session("actualRecordPosition"), Integer)
Dim TNRTemp As Integer = CType(Session("totalNumberOfRecords"), Integer)
If ARPTemp < TNRTemp Then
ARPTemp += 1
End If
Session("actualRecordPosition") = ARPTemp
updateInfo()
End Sub
Finally, the update() subroutine updates all the information in the screen:
Private Sub updateInfo()
hiddenDatagrid.SelectedIndex = (Session("actualRecordPosition".ToString) - 1)
TotalNrOfRecordsTextbox.Text = Session("totalNumberOfRecords".ToString)
currPositionInDbTextbox.Text = (Session("actualRecordPosition".ToString))
selectedIdInDbTextbox.Text = hiddenDatagrid.SelectedItem.Cells(0).Text
idTextbox.Text = hiddenDatagrid.SelectedItem.Cells(0).Text
firstNameTextbox.Text = hiddenDatagrid.SelectedItem.Cells(1).Text
lastNameTextbox.Text = hiddenDatagrid.SelectedItem.Cells(2).Text
ageTextbox.Text = hiddenDatagrid.SelectedItem.Cells(3).Text
Final words
Well, I hope this article helped you, the way writing it did to me.
If you have any comment, feel free to contact me: julianarevalo [at] hotmail.com. Good luck, and thanks for reading!.
