Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: The Next Button

  1. #1
    New to the CF scene
    Join Date
    Oct 2010
    Thanked 0 Times in 0 Posts

    The Next Button

    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?

  • #2
    New to the CF scene
    Join Date
    Oct 2010
    Thanked 0 Times in 0 Posts
    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.

  • #3
    Regular Coder Freon22's Avatar
    Join Date
    May 2005
    Thanked 5 Times in 5 Posts
    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
    Last edited by Freon22; 10-22-2010 at 06:48 PM. Reason: Edit
    I was wondering why frisbees got bigger as they got closer, then it hit me.


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts