13 Replies - 1395 Views - Last Post: 27 November 2013 - 04:31 AM Rate Topic: -----

#1 Mr_P   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 07-July 13

Enable button based on event

Posted 24 November 2013 - 04:29 AM

Hi

I need some help as I am a new user to vb.net with enabling a button based on an event. The scenario is I have a listview which populates 100 records at a time based on a access query.

I then have 2 buttons, 'btnNextPostings' and 'btnPreviousPostings' which are disabled at form_load. The idea is when the list is populated the next button becomes active which shows the next 100 postings. Think of it as navigating backwards and forwards 100 records at a time. You will see from my code, that I enable the next button 'btnNextPostings' when I populate the list.

I would be grateful if someone could help with this or point me in the direction for further help. Many thanks

Sub filllistview()
        Try
            'creatconn()
            oledbCnn.ConnectionString = My.Settings.storageConnectionString
            oledbCnn.Open()
            Dim oledbCmd As OleDbCommand = New OleDbCommand("Select TOP 100 * from Postings WHERE Customer = '" & newvar & "' ORDER BY Date DESC", oledbCnn)
            dr = oledbCmd.ExecuteReader()

            Dim tmpColor As Color = Color.Green

            'clear items in the list before populating with new values
            ListView1.Items.Clear()

            While dr.Read()

                ListView1.Items.Add(CDate(dr(4)).ToShortDateString()).UseItemStyleForSubItems = False
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(1).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(11).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(7).ToString())
                With ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(CDbl(dr(5)).ToString("C"))
                    If CDbl(dr(5)) < 0 Then
                        .ForeColor = Color.Red
                        .BackColor = Color.Gainsboro
                        '.Font = New Font(Font.FontFamily, Font.Size, FontStyle.Bold)
                    Else
                        .ForeColor = tmpColor
                    End If
                End With
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(14).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(3).ToString())


            End While

            'autosize each column in listview
            For Each col As ColumnHeader In ListView1.Columns
                col.Width = -2
            Next col

            'refresh the list with new data
            ListView1.Refresh()

            'Enable the posting previous button
            btnNextPostings.Enabled = True

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            dr.Close()
            oledbCnn.Close()
        End Try
    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Enable button based on event

#2 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Enable button based on event

Posted 24 November 2013 - 04:46 AM

I am not sure what you are asking here. You talk about enabling a button but you already have this statement:

btnNextPostings.Enabled = True

So.. what is your question?

"based on event" - what event? Just put the above statement inside the appropriate event-code.

This post has been edited by andrewsw: 24 November 2013 - 04:47 AM

Was This Post Helpful? 0
  • +
  • -

#3 Mr_P   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 07-July 13

Re: Enable button based on event

Posted 24 November 2013 - 10:23 AM

Hi Andrew

Good point. Ok, lets assume that both buttons are disabled at form_load. When the list is populated from a click event, it displays 100 records out of 17000 in total. What I am trying to do is much like pagination. This would then enable the nextbutton to display another 200 records. Does that make sense. Thanks

View Postandrewsw, on 24 November 2013 - 04:46 AM, said:

I am not sure what you are asking here. You talk about enabling a button but you already have this statement:

btnNextPostings.Enabled = True

So.. what is your question?

"based on event" - what event? Just put the above statement inside the appropriate event-code.


Perhaps this will make it clearer Andrew.

1) Enable Next button but only if there are more than 100 records in database. This means that i need to run a query first to get the count of total rows. Store this count in a class level variable and then when i populate the list, if this count is greater than 100 then enable the next button. I then also need to maintain the current page number count. When user presses the Next button, it loads next 100 rows and then check if you still need to enable the Next button by checking if PageNumber*100 is less than row count.

2) I need to enable the Previous button if current page number is not 1.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Enable button based on event

Posted 24 November 2013 - 10:30 AM

Assuming this is for MS Access then it is a little awkward to paginate records. You need something like:

SELECT *
FROM (
    SELECT Top 5 sub.ClientCode
    FROM (
        SELECT TOP 15 tblClient.ClientCode
        FROM tblClient
        ORDER BY tblClient.ClientCode
    ) sub
   ORDER BY sub.ClientCode DESC
) subOrdered
ORDER BY subOrdered.ClientCode

where 15 is the StartPos + PageSize, and 5 is the PageSize. From SO topic

You'll need to store a value to indicate which page you are currently on.

BTW Date is not a good field-name and you should enclose it in square brackets (for MS Access). Preferably, you should change this field-name: Date of what..?
Was This Post Helpful? 1
  • +
  • -

#5 Mr_P   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 07-July 13

Re: Enable button based on event

Posted 24 November 2013 - 01:25 PM

Thanks andrew. Shall experiment. Is there a programmable way of doing it? Thanks
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Enable button based on event

Posted 24 November 2013 - 01:34 PM

View PostMr_P, on 24 November 2013 - 08:25 PM, said:

Thanks andrew. Shall experiment. Is there a programmable way of doing it? Thanks

I'm not sure what you mean by this. The sql-statement could be stored as a string and you would insert the numbers into this string to retrieve the records for the page that you want.
Was This Post Helpful? 0
  • +
  • -

#7 Mr_P   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 07-July 13

Re: Enable button based on event

Posted 24 November 2013 - 01:50 PM

Could you show with an example please based on my code. Thanks
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Enable button based on event

Posted 24 November 2013 - 03:58 PM

You already know how to concatenate strings, you've done it here:

Dim oledbCmd As OleDbCommand = New OleDbCommand("Select TOP 100 * from Postings WHERE Customer = '" & newvar & "' ORDER BY Date DESC", oledbCnn)

Was This Post Helpful? 0
  • +
  • -

#9 Mr_P   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 07-July 13

Re: Enable button based on event

Posted 26 November 2013 - 05:04 AM

Andrew
I am getting an error in my statement of:

Quote

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.


Dim Row_Per_Page As Integer = 4
    Dim TotRows As Integer = 17
    Dim Page_Number As Integer = 2

    Dim oledbCmd As OleDbCommand = New OleDbCommand("Select TOP '" & Row_Per_Page & "' *, Count(*) As '" & TotRows & "' From [Select Top('" & TotRows & "' - (('" & Page_Number & "' - 1) * '" & Row_Per_Page & "'))From Postings Order By [Date] DESC] Order By [Date] ASC", oledbCnn)


I have checked and double checked and cannot find the error. Can you help. Thanks
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Enable button based on event

Posted 26 November 2013 - 07:10 AM

Instead of building it directly into the OleDBCommand's arguments, create a string variable and pass that as an argument. This way, you can MsgBox(yourString) or Debug.Print(yourString) and look over the statement to see if it looks correct.

Dim myStatement as String = "Select TOP '" & Row_Per_Page & "' *, Count(*) As '" & TotRows & "' From [Select Top('" & TotRows & "' - (('" & Page_Number & "' - 1) * '" & Row_Per_Page & "'))From Postings Order By [Date] DESC] Order By [Date] ASC"
Debug.Print(myStatement) '<--- This will give you the string that was built in your debug window to review
Dim oledbCmd As OleDbCommand = New OleDbCommand(myStatement, oledbCnn)


Right off the bat, I notice that you are wrapping Row_Per_Page inside apostrophes which would produce an error.
Was This Post Helpful? 0
  • +
  • -

#11 Mr_P   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 07-July 13

Re: Enable button based on event

Posted 26 November 2013 - 08:26 AM

problem is charlie, as a new user to vb.net (6 weeks) I wouldn't know if it was correct or not. I got this code from a site where there was a tutorial on pagination in vb.net. I shall try your way and report back. Many thanks
Was This Post Helpful? 0
  • +
  • -

#12 Mr_P   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 07-July 13

Re: Enable button based on event

Posted 26 November 2013 - 08:32 AM

Charlie

I have revised my code and now getting the following error.

Quote

The Microsoft Office Access database engine cannot find the input table or query 'Select Top(17 - ((2 - 1) * 4))From Postings Order By Date DESC'. Make sure it exists and that its name is spelled correctly.


Dim myStatement As String = "Select TOP " & Row_Per_Page & ", Count(*) As " & TotRows & " From [Select Top(" & TotRows & " - ((" & Page_Number & " - 1) * " & Row_Per_Page & "))From Postings Order By Date DESC] order By Date ASC"
            Debug.Print(myStatement) '<--- This will give you the string that was built in your debug window to review
            Dim oledbCmd As OleDbCommand = New OleDbCommand(myStatement, oledbCnn)

Was This Post Helpful? 0
  • +
  • -

#13 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1730
  • View blog
  • Posts: 5,709
  • Joined: 25-September 09

Re: Enable button based on event

Posted 26 November 2013 - 01:26 PM

That calculation that you're embedding needs to be performed and the result placed in the string.

For example:
Dim CurrentPage As Integer = (17-((2-1)*4))
Dim myStatement As String = "SELECT TOP " & CurrentPage.ToString & ", ....

The resulting myStatement would hold SELECT TOP 13, .....

I'm not really sure this is what you are wanting to do but do you see the reason for your error? If not, read up on how to use TOP Predicate in a Select Statement
Was This Post Helpful? 0
  • +
  • -

#14 Mr_P   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 07-July 13

Re: Enable button based on event

Posted 27 November 2013 - 04:31 AM

Charlie

That link dosen't really give much of an explanation in regards to my problem that I can see. I have coded as you suggested and still getting same error. Is this code correct now? so I can move forward and try to find what is causing this.

Quote

The Microsoft Office Access database engine cannot find the input table or query 'Select TOP (17 - ((2 - 1) * 4) From Postings Order By Customer ASC'. Make sure it exists and that its name is spelled correctly.


Dim myStatement As String = "Select TOP " & Row_Per_Page.ToString & " *, Count(*) AS " & TotRows.ToString & " From [Select TOP (" & TotRows.ToString & " - ((" & Page_Number.ToString & " - 1) * " & Row_Per_Page.ToString & ") From Postings Order By Customer ASC] Order By Customer ASC"
            Debug.Print(myStatement) '<--- This will give you the string that was built in your debug window to review
            Dim oledbCmd As OleDbCommand = New OleDbCommand(myStatement, oledbCnn)

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1