JOSE LEON
Reputation: 19
Visual Studio 2015 VB.net How to find record in MS Access database and disable button in form?
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
Upvotes: 0
Views: 1315
Answers (1)
Ahh ... It's a programming thi
Reputation: 641
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
Upvotes: 0
Related Questions
- Disable Button on Access Form Based on Drop Down Selection
- Delete record from Access database vb.net
- Finding Records in a Database using a Textbox and Search Button in VB.Net
- Need to lock/disable button in access
- how to delete records from ms access in vb.net
- Deleting data from an MS Access Database with C# via button click
- Query MS Access Database from Form button with Visual Basic for Applications
- How to hide/Unhide a MS Access DB form using vb.net
- How to prevent to open a MS Access Database while reading data from forms
- How to delete a specific record from an access database with asp?