Visual Studio 2015 VB.net How to find record in MS Access database and disable button in form?

Question

i am trying to load a form, but when loading must go to a access database and check if a record with the button text and today date exist, if it exist then disable the button. I have about 50 button in the form. NOTE: check my codes below, they works for 4 or 5 but the other ones not working. i have no error message. Maybe if someone have a better way to do this i will be very thanks full .This is my loading form

Imports System.Data.OleDb

Public Class Wells Dim provider As String Dim dataFile As String Dim connString As String Public myConnection As OleDbConnection = New OleDbConnection Public dr As OleDbDataReader Public da As OleDbDataAdapter Dim Errors As String

Private Sub Wells_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    lblUserActive.Text = UserActive
    ZoneActive = Nothing
    WellActive = Nothing

    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    dataFile = "f:\wortendyke\WortendykeTables.accdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
    Errors = ""

    Try
        If Button19.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button19.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button19.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button19.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try


    Try
        If Button23.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button23.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button23.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button23.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button17.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button17.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button17.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button17.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button3.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button3.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button3.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button3.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try
    Try
        If Button20.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button20.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button20.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button20.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button1.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button1.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button1.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button1.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button2.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button2.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button2.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button2.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button18.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button18.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button18.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button18.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button7.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button7.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button7.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button7.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button16.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button16.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button16.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button16.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button15.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button15.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button15.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button15.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button14.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button14.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button14.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button14.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button13.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button13.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button13.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button13.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button12.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button12.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button12.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button12.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button11.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button11.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button11.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button11.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button10.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button10.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button10.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button10.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button9.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button9.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button9.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button9.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button8.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button8.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button8.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button8.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button6.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button6.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button6.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button6.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button5.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button5.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button5.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button5.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try

    Try
        If Button4.Enabled = True Then

            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM maintable WHERE location='" & Button4.Text & "' AND pumpingdate=#" & dateActive & "#"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()
            If Button4.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
                Button4.Enabled = False
                myConnection.Close()
                Me.Refresh()
            End If

        End If

    Catch ex As Exception

    End Try



End Sub

Someone that matters Someone that matters · Accepted Answer · 2015-12-15T15:04:53

You need to add something to you catch block so you can see the error messages

Try
    If Button4.Enabled = True Then

        myConnection.Open()
        Dim str As String
        str = "SELECT * FROM maintable WHERE location='" & Button4.Text & "' AND pumpingdate=#" & dateActive & "#"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        dr = cmd.ExecuteReader
        dr.Read()
        If Button4.Text = dr("location").ToString And dateActive = dr("pumpingdate").ToString Then
            Button4.Enabled = False
            myConnection.Close()
            Me.Refresh()
        End If

    End If

Catch ex As Exception
  msgbox(ex.tostring)
End Try

That way you can see what the problem is

As far as a better way, you could do that a couple ways, most of them would be more efficient and easier to maintain. But the first thing I would suggest is separating your data logic from your display logic for example create a function that returns a boolean if a button is suppose to be disabled

Public function isDisabled(ButtonName as string, dateActive as date) as boolean
   Dim retval as boolean = false
    myConnection.Open()
        Dim str As String
        str = "SELECT * FROM maintable WHERE location='" & ButtonName & "' AND pumpingdate=#" & dateActive & "#"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        dr = cmd.ExecuteReader
        dr.Read()

        if dr.rows.count > 0 then  
            retval = true 
        else
            retval = false 
        end if 
        myConnection.Close()

        return retval
  end function

Then you can do something like this on your load

 try
    if button4.enabled =true then 
        button4.enabled = isdisabled(button4.text, activedate)
    end if 
 catch exp as exception
    msgbox(exp.tostring)
 end if

There is alot more but that's a start

*Some of the syntax might be wrong I'm doing this from memory on a phone

Visual Studio 2015 VB.net How to find record in MS Access database and disable button in form?

1 Answers