View Full Version : The Next Button

10-21-2010, 06:15 AM
Hi guys, I've been coding using ASP.net, VB.net and using and an Access Database for about 3 months. I've been working on a project management website.
I've gotten increasingly better and better at manipulating and displaying data from the database using webforms but I've come to a bit of a hitch, here is the code for my next button...

Protected Sub btnNext_OnClick(ByVal sender As Object, ByVal e As System.EventArgs)
Dim counter As Integer

Dim projectID As Integer = Int32.Parse(lblProjectID.Text)
Dim ProjectActivity As String = ""
Dim Comments As String = ""
Dim Contractor As String = ""
Dim Supervisor As String = ""
Dim ActivityStartYear As Date
Dim ActivityFinishYear As Date

Dim currentProjectID As String

If lblFirstRun.Text = "0" Then
counter = 1
lblCounter.Text = counter
lblFirstRun.Text = "1"
End If

Dim lastConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|MOPPS.mdb;")
Dim lastSQL As String = "SELECT MAX(activityID) FROM ProjectActivities"
Dim lastCommand As New OleDbCommand(lastSQL, lastConn)
Dim lastDataReader As OleDbDataReader
Dim lastID As Integer
Dim activityID As Integer
Using lastConn
Using lastCommand
lastDataReader = lastCommand.ExecuteReader()
While (lastDataReader.Read())
lastID = lastDataReader.GetValue(0).ToString()
lblLASTID.Text = lastID
End While
End Using
End Using

counter = Int32.Parse(lblCounter.Text)

Dim stopper As Integer = 0
If counter < lastID Then
If cbScanAll.Checked Then

While stopper = 0
Dim objConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|MOPPS.mdb;")
Dim objSQL As String = "SELECT ProjectID, Activity, Comments, Contractor, Supervisor, ActivityStart, ActivityFinish FROM ProjectActivities WHERE ActivityID=@activityID;"
Dim objCommand As New OleDbCommand(objSQL, objConnection)
Dim objDataReader As OleDbDataReader

Using objConnection
Using objCommand
objCommand.Parameters.AddWithValue("activityID", counter)
objDataReader = objCommand.ExecuteReader()
While (objDataReader.Read())
currentProjectID = objDataReader.GetValue(0).ToString()
If currentProjectID = projectID Then
stopper += 1

ProjectActivity = objDataReader.GetValue(1).ToString()
ddlProjectActivities.SelectedValue = ProjectActivity
Comments = objDataReader.GetValue(2).ToString()
tbComments.Text = Comments
Contractor = objDataReader.GetValue(3).ToString()
ddlContractors.SelectedValue = Contractor
Supervisor = objDataReader.GetValue(4).ToString()
ddlSupervisors.SelectedValue = Supervisor
ActivityStartYear = objDataReader.GetValue(5).ToString()
ActivityFinishYear = objDataReader.GetValue(6).ToString()

Dim startDay As String = Convert.ToDateTime(ActivityStartYear).ToString("dd")
Dim startMonth As String = Convert.ToDateTime(ActivityStartYear).ToString("MM")
Dim startYear As Integer = Convert.ToDateTime(ActivityStartYear).ToString("yyyy")
startYear = startYear - 2000
ddlActivityStartDay.SelectedValue = startDay
ddlActivityStartMonth.SelectedValue = startMonth
tbActivityStartYear.Text = startYear

Dim finishDay As String = Convert.ToDateTime(ActivityFinishYear).ToString("dd")
Dim finishMonth As String = Convert.ToDateTime(ActivityFinishYear).ToString("MM")
Dim finishYear As Integer = Convert.ToDateTime(ActivityFinishYear).ToString("yyyy")
finishYear = finishYear - 2000
ddlActivityFinishDay.SelectedValue = finishDay
ddlActivityFinishMonth.SelectedValue = finishMonth
tbActivityFinishYear.Text = finishYear
counter += 1
lblCounter.Text = counter
counter += 1
End If
End While
End Using
End Using
End While
End If
End If
End Sub

Basically I want to allow the user to scroll through all the activities of a certain ProjectID and populate textboxes and select the value of drop down lists with the results.
And it kind of works
The problem is two part:
1) When you get to the end (in this case there are 13 records and the 13th is for this particular project) I have one of a number of different problems...
1. I don't see the last matching record
2. If you click next while viewing the last record you get an endless loop
3. If you click back while viewing the last record you have to click back twice because the counter goes one two far. (This is the closest I've gotten to it actually working well...)

2) I'm using Visual Studio 2005. I haven't tried running it outside of this. However, it's really slow. Sometimes taking 10 seconds to find the next record, which completely defeats the purpose.

A much better coder than me suggested I use Pagination. I've done some research and I couldn't really see how it would apply to this.

So, help a brother out.
What am I doing wrong and/or what is the better way of doing this?

10-22-2010, 03:47 AM
Well I don't know what it means if I have 799 views and no replies.

It couldn't be that bad could it?

I can provide more information if it's needed.

10-22-2010, 06:28 PM
I am not a VB person I like C# but lets see if I can help a little. First your big problem the endless loop? When you run this sub I see that you are setting

Dim stopper As Integer = 0

Then in the While statement you are saying

While stopper = 0

So the While statement will run until the stopper doesn't = 0

I see where you are changing the stopper value in this If statement

If currentProjectID = projectID Then
stopper += 1

But what will happen if currentProjectID doesn't equal projectID yelp an endless loop.

On the time it takes to find the next record the 10 seconds thing. You have two problems the way I see it. One you are using an Access Database thats strike one the next thing is all these Using statements.

Using statements are nice they close your connections so you don't have to when they are done. To me they are a little slow, I like to open and close my own connections. I also see you are using Using statements within Using statements. Now before anyone starts hitting me about Using statements I think in so cases they are great. I'm not sure if they are what is slowing down the run time in this case.

Anyway you are using Visual Studio 2005, so run your sub in the debug mote. Put a break point on the sub or on one of your Using and/or While statements. Then step through your code one line at a time. Read the values and see where it is messing up when you click back while viewing the last record.

I hope some of this help and maybe a good VB coder will response to this thread soon.

Edit: Here is something that I forgot to say. When I am coding I like to see if my reader has any rows. If it doesn't then I know that it is empty. I am sure VB has a HasRows you may need to look into this.

if (objDataReader.HasRows)
while (objDataReader.Read())
//do work here