Hi, I am working on a database project using SQL Server 2008 and VB.NET 2010. I would like to submit code here for review. My code works but its no beautiful by any means still I would appreciate constructive criticism please. Its the complete code I have written from a Form.
The code is not in OOP style, I don't create instances of the forms I just address them by their class name and it works well for me. I also use a number of Global variables that are in a module so this much I know.
I'm more concerned about disposing of Dataset's and data adapters in the correct manner and what else I should be checking for to avoid memory leaks. Am I declaring my varaibles and the da and ds in the correct places. My code is simple I feel and seems to work well although I may need to do more error checking.
This Form has 2 datagridviews and several buttons, The first dgv is for Clients and the second is the Staff who work for those client, and there is a relation between them. 3 radio buttons filter the data by 'Active' and 3 search buttons by surname Client, ID and DOB. For some reason thehe DOB filter doesn't work I dont know why no errors are produced!!
Imports System.Data
Imports System.Data.SqlClient
Public Class Form4
'Show All Area Clients and their Workers
Dim Ans As String = ""
Dim bs, bs1, bs2 As New BindingSource
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim conn As New SqlClient.SqlConnection(connStr)
Private relation As DataRelation
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Close Form4, Show Main Menu Form
If conn.State = ConnectionState.Open Then
conn.Close()
End If
'cmd.Dispose()
ds.Dispose()
da.Dispose()
FrmMainMenu.Show()
Me.Close()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'Search by Client ID
Try
' bs.DataSource = DataGridView1.DataSource
Ans = InputBox("Search For ID..", "Search", "", 100, 100)
If Ans = "" Then
MsgBox("No ID entered")
Else
bs1.Filter = ("ID=" & Ans)
DataGridView1.DataSource = bs.DataSource
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
End Try
End Sub
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Load Clients and Open Form4
Dim x, y As Integer
Dim cmd As New SqlCommand
' Dim da As New SqlDataAdapter
' Dim ds As New DataSet
'Dim relation As New DataRelation
Dim conn As New SqlClient.SqlConnection(connStr)
Try
' Me.WindowState = FormWindowState.Maximized
Label2.Text = AreaName 'Global Variable
' MsgBox(AreaID)
'Populate first DatGridView1 with Clients
'Select AreaID
If AreaID < 7 Then
'Only Local 'ServiceApproved' HHArea CLients
cmd = New SqlCommand("SELECT * FROM VwClients WHERE AreaID = @AreaID AND ServiceApproved='TRUE' ", conn)
cmd.Parameters.AddWithValue("@AreaID", AreaID)
Else
' Load All Approved HH Clients
cmd = New SqlCommand("SELECT * FROM VwClients", conn)
End If
da = New SqlDataAdapter(cmd)
da.Fill(ds, "VwClients")
' Bind the master data connector to the Customers table.
bs1.DataSource = ds.Tables("VwClients")
'bind DataGridView1 to BindingNavigator's BindingSource
Me.BindingNavigator1.BindingSource = bs1
Me.DataGridView1.DataSource = Me.BindingNavigator1.BindingSource
Me.DataGridView1.AllowUserToAddRows = False
For Each column As DataGridViewColumn In Me.DataGridView1.Columns
column.DisplayIndex = DataGridView1.Columns.IndexOf(column)
Next
Me.DataGridView1.AutoResizeColumns()
'Me.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
' Show Clients Staff
If AreaID <> 7 Then
'Populate second DatGridView2 with Clients
If AreaID > 7 Then
' Load All Clients
cmd = New SqlCommand("SELECT * FROM VwClientsStaff", conn)
ElseIf AreaID < 7 Then
'Only Local CLients
cmd = New SqlCommand("SELECT * FROM VwClientsStaff Where AreaID= @AreaID", conn)
cmd.Parameters.AddWithValue("@AreaID", AreaID)
End If
da = New SqlDataAdapter(cmd)
da.Fill(ds, "VwClientsStaff")
' MsgBox(ds.Tables("VwClientsStaff").Rows.Count)
' Establish a relationship between the two tables.
Dim relation = New DataRelation("RelClientsStaff", ds.Tables("VwClients").Columns("ID")_
, ds.Tables("VwClientsStaff").Columns("ClientID"))
ds.Relations.Add(relation)
' Bind the details data connector to the master data connector,
' using the DataRelation name to filter the information in the
' details table based on the current row in the master table.
bs2.DataSource = bs1
bs2.DataMember = "RelClientsStaff"
'bind DataGridView2 to BindingNavigator2 BindingSource
Me.BindingNavigator2.BindingSource = bs2
Me.DataGridView2.DataSource = Me.BindingNavigator2.BindingSource
Me.DataGridView2.AllowUserToAddRows = False
'Me.BindingNavigator2.AddNewItem.Visible = False
Me.DataGridView2.AutoResizeColumns()
'Me.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
Me.DataGridView2.Columns.Remove("ClientID")
'Me.DataGridView2.Columns.Remove("HHAreaID")
Me.DataGridView2.RowHeadersWidth = 5
x = Me.Width
y = Me.DataGridView2.Columns.GetColumnsWidth(DataGridViewElementStates.Displayed)
If y >= x Then
Me.DataGridView2.Width = x
Else
Me.DataGridView2.Width = y + 10
End If
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
cmd.Dispose()
'ds.Dispose()
'da.Dispose()
End Try
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'...Open Edit Form...
Try
If Me.DataGridView1.RowCount > 0 Then
ClientID = Me.DataGridView1.CurrentRow.Cells("ID").Value
If Not IsDBNull(ClientID) Then
'MsgBox(ClientID)
Form6.Show()
Me.Close()
Else
MsgBox("Please Click on a Client Row first!")
End If
Else
MsgBox("No Data!")
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'...Search By Client Name...
Dim Ans As String = ""
Try
Ans = InputBox("Search For Surname..", "Search", "", 100, 100)
If Ans = "" Then
MsgBox("No ID entered")
Else
bs.Filter = ("Surname Like'*" & Ans & "*'")
DataGridView1.DataSource = bs.DataSource
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
End Try
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
'...Open New Client Form...
Form13.Show()
Me.Close()
End Sub
Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
If Me.RadioButton1.Checked Then
bs.Filter = ("Active = True")
Me.RadioButton1.Checked = True
Else
bs.Filter = ("Active = False")
Me.RadioButton1.Checked = False
End If
bs.DataSource = DataGridView1.DataSource
End Sub
Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
bs.Filter = ("Active = True")
bs.DataSource = DataGridView1.DataSource
End Sub
Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
bs.Filter = ("Active = False")
bs.DataSource = DataGridView1.DataSource
End Sub
Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged
bs.Filter = ""
bs.DataSource = DataGridView1.DataSource
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
'Search by DOB
Dim Ans As String
Dim aDate As Date
Try
' bs.DataSource = DataGridView1.DataSource
Ans = InputBox("Search For DOB..(format.31/12/1961", "Search", "", 100, 100)
aDate = Date.ParseExact(Ans, "dd/mm/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
aDate = aDate.Date
MsgBox(aDate)
If Ans = "" Then
MsgBox("No Date entered")
Else
bs1.Filter = ("DOB = #" & aDate & "#")
DataGridView1.DataSource = bs.DataSource
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
End Try
End Sub
End Class
The code is not in OOP style, I don't create instances of the forms I just address them by their class name and it works well for me. I also use a number of Global variables that are in a module so this much I know.
I'm more concerned about disposing of Dataset's and data adapters in the correct manner and what else I should be checking for to avoid memory leaks. Am I declaring my varaibles and the da and ds in the correct places. My code is simple I feel and seems to work well although I may need to do more error checking.
This Form has 2 datagridviews and several buttons, The first dgv is for Clients and the second is the Staff who work for those client, and there is a relation between them. 3 radio buttons filter the data by 'Active' and 3 search buttons by surname Client, ID and DOB. For some reason thehe DOB filter doesn't work I dont know why no errors are produced!!
Imports System.Data
Imports System.Data.SqlClient
Public Class Form4
'Show All Area Clients and their Workers
Dim Ans As String = ""
Dim bs, bs1, bs2 As New BindingSource
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim conn As New SqlClient.SqlConnection(connStr)
Private relation As DataRelation
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Close Form4, Show Main Menu Form
If conn.State = ConnectionState.Open Then
conn.Close()
End If
'cmd.Dispose()
ds.Dispose()
da.Dispose()
FrmMainMenu.Show()
Me.Close()
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'Search by Client ID
Try
' bs.DataSource = DataGridView1.DataSource
Ans = InputBox("Search For ID..", "Search", "", 100, 100)
If Ans = "" Then
MsgBox("No ID entered")
Else
bs1.Filter = ("ID=" & Ans)
DataGridView1.DataSource = bs.DataSource
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
End Try
End Sub
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Load Clients and Open Form4
Dim x, y As Integer
Dim cmd As New SqlCommand
' Dim da As New SqlDataAdapter
' Dim ds As New DataSet
'Dim relation As New DataRelation
Dim conn As New SqlClient.SqlConnection(connStr)
Try
' Me.WindowState = FormWindowState.Maximized
Label2.Text = AreaName 'Global Variable
' MsgBox(AreaID)
'Populate first DatGridView1 with Clients
'Select AreaID
If AreaID < 7 Then
'Only Local 'ServiceApproved' HHArea CLients
cmd = New SqlCommand("SELECT * FROM VwClients WHERE AreaID = @AreaID AND ServiceApproved='TRUE' ", conn)
cmd.Parameters.AddWithValue("@AreaID", AreaID)
Else
' Load All Approved HH Clients
cmd = New SqlCommand("SELECT * FROM VwClients", conn)
End If
da = New SqlDataAdapter(cmd)
da.Fill(ds, "VwClients")
' Bind the master data connector to the Customers table.
bs1.DataSource = ds.Tables("VwClients")
'bind DataGridView1 to BindingNavigator's BindingSource
Me.BindingNavigator1.BindingSource = bs1
Me.DataGridView1.DataSource = Me.BindingNavigator1.BindingSource
Me.DataGridView1.AllowUserToAddRows = False
For Each column As DataGridViewColumn In Me.DataGridView1.Columns
column.DisplayIndex = DataGridView1.Columns.IndexOf(column)
Next
Me.DataGridView1.AutoResizeColumns()
'Me.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
' Show Clients Staff
If AreaID <> 7 Then
'Populate second DatGridView2 with Clients
If AreaID > 7 Then
' Load All Clients
cmd = New SqlCommand("SELECT * FROM VwClientsStaff", conn)
ElseIf AreaID < 7 Then
'Only Local CLients
cmd = New SqlCommand("SELECT * FROM VwClientsStaff Where AreaID= @AreaID", conn)
cmd.Parameters.AddWithValue("@AreaID", AreaID)
End If
da = New SqlDataAdapter(cmd)
da.Fill(ds, "VwClientsStaff")
' MsgBox(ds.Tables("VwClientsStaff").Rows.Count)
' Establish a relationship between the two tables.
Dim relation = New DataRelation("RelClientsStaff", ds.Tables("VwClients").Columns("ID")_
, ds.Tables("VwClientsStaff").Columns("ClientID"))
ds.Relations.Add(relation)
' Bind the details data connector to the master data connector,
' using the DataRelation name to filter the information in the
' details table based on the current row in the master table.
bs2.DataSource = bs1
bs2.DataMember = "RelClientsStaff"
'bind DataGridView2 to BindingNavigator2 BindingSource
Me.BindingNavigator2.BindingSource = bs2
Me.DataGridView2.DataSource = Me.BindingNavigator2.BindingSource
Me.DataGridView2.AllowUserToAddRows = False
'Me.BindingNavigator2.AddNewItem.Visible = False
Me.DataGridView2.AutoResizeColumns()
'Me.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
Me.DataGridView2.Columns.Remove("ClientID")
'Me.DataGridView2.Columns.Remove("HHAreaID")
Me.DataGridView2.RowHeadersWidth = 5
x = Me.Width
y = Me.DataGridView2.Columns.GetColumnsWidth(DataGridViewElementStates.Displayed)
If y >= x Then
Me.DataGridView2.Width = x
Else
Me.DataGridView2.Width = y + 10
End If
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
cmd.Dispose()
'ds.Dispose()
'da.Dispose()
End Try
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
'...Open Edit Form...
Try
If Me.DataGridView1.RowCount > 0 Then
ClientID = Me.DataGridView1.CurrentRow.Cells("ID").Value
If Not IsDBNull(ClientID) Then
'MsgBox(ClientID)
Form6.Show()
Me.Close()
Else
MsgBox("Please Click on a Client Row first!")
End If
Else
MsgBox("No Data!")
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'...Search By Client Name...
Dim Ans As String = ""
Try
Ans = InputBox("Search For Surname..", "Search", "", 100, 100)
If Ans = "" Then
MsgBox("No ID entered")
Else
bs.Filter = ("Surname Like'*" & Ans & "*'")
DataGridView1.DataSource = bs.DataSource
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
End Try
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
'...Open New Client Form...
Form13.Show()
Me.Close()
End Sub
Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
If Me.RadioButton1.Checked Then
bs.Filter = ("Active = True")
Me.RadioButton1.Checked = True
Else
bs.Filter = ("Active = False")
Me.RadioButton1.Checked = False
End If
bs.DataSource = DataGridView1.DataSource
End Sub
Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
bs.Filter = ("Active = True")
bs.DataSource = DataGridView1.DataSource
End Sub
Private Sub RadioButton2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton2.CheckedChanged
bs.Filter = ("Active = False")
bs.DataSource = DataGridView1.DataSource
End Sub
Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged
bs.Filter = ""
bs.DataSource = DataGridView1.DataSource
End Sub
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
'Search by DOB
Dim Ans As String
Dim aDate As Date
Try
' bs.DataSource = DataGridView1.DataSource
Ans = InputBox("Search For DOB..(format.31/12/1961", "Search", "", 100, 100)
aDate = Date.ParseExact(Ans, "dd/mm/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
aDate = aDate.Date
MsgBox(aDate)
If Ans = "" Then
MsgBox("No Date entered")
Else
bs1.Filter = ("DOB = #" & aDate & "#")
DataGridView1.DataSource = bs.DataSource
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
End Try
End Sub
End Class
