connect search, retrieve and update mdb database best practice

[Coderv9]

Hi am looking for the best and secure way to connect a mdb database.

My application will require several database call like name search, address search and several updates / deletion tasks.

I have wrote the same sample code and need to know if its the best practice (safe, secure and efficient)

below is the module and a sample form we use.

appreciate if any one can help with this.

Module Module1
Public gen_db_connect As System.Data.OleDb.OleDbConnection
Public cmd As System.Data.OleDb.OleDbCommand
Public db_path As String
Public connect_status As Boolean = False
Public Sub connect_my_database()
If connect_status = False Then
Try
gen_db_connect = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db_path & ";Jet OLEDBatabase Password=papa@123")
gen_db_connect.Open()
connect_status = True
Catch ex As Exception
MsgBox(Err.Description, MsgBoxStyle.Exclamation, Err.Number)
If gen_db_connect.State = ConnectionState.Open Then gen_db_connect.Close()
End Try
End If
End Sub
Public Sub disconnect_my_database()
Try
If connect_status = False Then
Exit Sub
Else
gen_db_connect.Close()
connect_status = False
End If
Catch ex As Exception
MsgBox(Err.Description, MsgBoxStyle.Exclamation, Err.Number)
End Try
End Sub
End Module


Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
db_path = "D:\sand\mydb.mdb"
End Sub
Private Sub Search_TextBox_TextChanged(sender As Object, e As EventArgs) Handles Search_TextBox.TextChanged
Dim qry As String
Try
connect_my_database()
Dim dt As New DataSet
qry = "Select ID, tier,tkt_weight,Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10 from tbl_data where Tag LIKE @t1_value"
dt = query_db(qry, Trim(Search_TextBox.Text))
Dim MaxRows As Integer = dt.Tables("result_table").Rows.Count
If MaxRows >= 1 Then
With d_results
.Visible = True
.DataSource = dt
.DataMember = "result_table"
End With
Search_status_01.Visible = False
Search_status_01.Text = ""
Else
Search_status_01.Text = "No Results"
Search_status_01.ForeColor = Color.Red
Search_status_01.Visible = True
d_results.Visible = False
Exit Sub
End If
Catch ex As Exception
MsgBox(ex.Message, vbExclamation)
End Try
End Sub
Private Function query_db(query As String, input_value As String)
Try
connect_my_database()
Dim dt As New DataSet
Dim da As New OleDb.OleDbDataAdapter
dt.Clear()
cmd = New OleDb.OleDbCommand(query, gen_db_connect)
cmd.Parameters.Add("@t1_value", OleDb.OleDbType.Char).Value = "%" & input_value & "%"
da.SelectCommand = cmd
da.Fill(dt, "result_table")
Return dt
da.Dispose()
dt.Dispose()
disconnect_my_database()
Catch ex As Exception
MsgBox(ex.Message, vbExclamation)
Return vbNull
End Try
End Function
End Class





Coderv9

Continue reading...