writing a common to query mdb database and return values

[Coderv9]

Hi is there an effective way to query database and return values ?

i figured a logic to return and save single values but now we need to import tables for grid list similar fashion. Please help.

i know its simple but sorry that am just a starter.


Public Function return_single_value_from_db(required_value As String, look_table As String, look_name As String, look_name_value As String)
Dim qry As String
Try
Dim dt As New DataSet
Dim da As New OleDb.OleDbDataAdapter
qry = "Select " & required_value & " from " & look_table & " Where " & look_name & "= '" & look_name_value & "'"
connect_my_database()
cmd = New OleDb.OleDbCommand(qry, gen_db_connect)
da.SelectCommand = cmd
da.Fill(dt, "search_results")
disconnect_my_database()
Dim MaxRows As Integer = dt.Tables("search_results").Rows.Count
If MaxRows > 0 Then
Return CStr(dt.Tables("search_results").Rows(0).Item(required_value).ToString)
Else
Return "Error"
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Someting went wrong")
WriteLog(ex.Message & vbCrLf & Err.Description)
Return "Error"
Finally
disconnect_my_database()
End Try
End Function

Public Function save_single_value_to_db(update_field As String, update_table As String, update_name As String, update_name_value As String, new_val As String)
Dim qry As String
Try
Dim dt As New DataSet
Dim da As New OleDb.OleDbDataAdapter
qry = "update [" & update_table & "] set [" & update_field & "] = @update_value WHERE [" & update_name & "] = '" & update_name_value & "'"
connect_my_database()
cmd = New OleDb.OleDbCommand(qry, gen_db_connect)
cmd.Parameters.AddWithValue("@update_value", new_val)
cmd.ExecuteNonQuery()
cmd.Dispose()
Return True
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Someting went wrong")
WriteLog(ex.Message & vbCrLf & Err.Description)
Return False
Finally
disconnect_my_database()
End Try
End Function


here is an example function which am trying to simply. This queries database and return results which are then displayed to a child form.



Private Sub d_results_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles d_results.CellDoubleClick
Dim qry, t1_value, t2_value As String
Try
Dim dt, dt1 As New DataSet
Dim da As New OleDb.OleDbDataAdapter
t1_value = d_results(0, e.RowIndex).Value.ToString
t2_value = d_results(1, e.RowIndex).Value.ToString
qry = "Select DISTINCT ID,Tier1,Tier2,Tier3,KB_Link,Attachment_Link,Impact,Urgency,Priority," _
& "Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10 from g_data where ID = @t1_value AND Issue = @t2_value"
connect_my_database()
cmd = New OleDb.OleDbCommand(qry, gen_db_connect)
cmd.Parameters.AddWithValue("@t1_value", d_results(0, e.RowIndex).Value.ToString)
cmd.Parameters.AddWithValue("@t2_value", d_results(1, e.RowIndex).Value.ToString)
da.SelectCommand = cmd
da.Fill(dt, "search_results")
Dim MaxRows As Integer = dt.Tables("search_results").Rows.Count
My.Application.DoEvents()
Dim add_f As New result_form
add_f.MdiParent = Welcome_Window
add_f.Show()
My.Application.DoEvents()
If MaxRows = 0 Then Exit Sub
add_f.tier1_text.Text = CStr(dt.Tables("search_results").Rows(0).Item("Tier1"))
add_f.tier2_text.Text = CStr(dt.Tables("search_results").Rows(0).Item("Tier2"))
add_f.tier3_text.Text = CStr(dt.Tables("search_results").Rows(0).Item("Tier3"))
add_f.kb_link.Text = CStr(dt.Tables("search_results").Rows(0).Item("KB_Link"))
atta_link = CStr(dt.Tables("search_results").Rows(0).Item("Attachment_Link"))
If atta_link = "NA" Or atta_link = "" Then
add_f.at_link.Visible = False
add_f.Label5.Visible = False
End If
add_f.record_value.Text = CStr(dt.Tables("search_results").Rows(0).Item("ID"))

add_f.Impact_text.Text = CStr(dt.Tables("search_results").Rows(0).Item("Impact"))
add_f.urgency_text.Text = CStr(dt.Tables("search_results").Rows(0).Item("Urgency"))
add_f.priority_text.Text = CStr(dt.Tables("search_results").Rows(0).Item("Priority"))

add_f.q1_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q1"))
If add_f.q1_label.Text = "" Or add_f.q1_label.Text = "NA" Then
add_f.q1_label.Visible = False
add_f.q1_text.Visible = False
Else
add_f.q1_label.Visible = True
add_f.q1_text.Visible = True
End If

add_f.q2_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q2"))
If add_f.q2_label.Text = "" Or add_f.q2_label.Text = "NA" Then
add_f.q2_label.Visible = False
add_f.q2_text.Visible = False
Else
add_f.q2_label.Visible = True
add_f.q2_text.Visible = True
End If

add_f.q3_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q3"))
If add_f.q3_label.Text = "" Or add_f.q3_label.Text = "NA" Then
add_f.q3_label.Visible = False
add_f.q3_text.Visible = False
Else
add_f.q3_label.Visible = True
add_f.q3_text.Visible = True
End If

add_f.q4_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q4"))
If add_f.q4_label.Text = "" Or add_f.q4_label.Text = "NA" Then
add_f.q4_label.Visible = False
add_f.q4_text.Visible = False
Else
add_f.q4_label.Visible = True
add_f.q4_text.Visible = True
End If

add_f.q5_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q5"))
If add_f.q5_label.Text = "" Or add_f.q5_label.Text = "NA" Then
add_f.q5_label.Visible = False
add_f.q5_text.Visible = False
Else
add_f.q5_label.Visible = True
add_f.q5_text.Visible = True
End If

add_f.q6_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q6"))
If add_f.q6_label.Text = "" Or add_f.q6_label.Text = "NA" Then
add_f.q6_label.Visible = False
add_f.q6_text.Visible = False
Else
add_f.q6_label.Visible = True
add_f.q6_text.Visible = True
End If

add_f.q7_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q7"))
If add_f.q7_label.Text = "" Or add_f.q7_label.Text = "NA" Then
add_f.q7_label.Visible = False
add_f.q7_text.Visible = False
Else
add_f.q7_label.Visible = True
add_f.q7_text.Visible = True
End If

add_f.q8_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q8"))
If add_f.q8_label.Text = "" Or add_f.q8_label.Text = "NA" Then
add_f.q8_label.Visible = False
add_f.q8_text.Visible = False
Else
add_f.q8_label.Visible = True
add_f.q8_text.Visible = True
End If

add_f.q9_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q9"))
If add_f.q9_label.Text = "" Or add_f.q9_label.Text = "NA" Then
add_f.q9_label.Visible = False
add_f.q9_text.Visible = False
Else
add_f.q9_label.Visible = True
add_f.q9_text.Visible = True
End If

add_f.q10_label.Text = CStr(dt.Tables("search_results").Rows(0).Item("Q10"))
If add_f.q10_label.Text = "" Or add_f.q10_label.Text = "NA" Then
add_f.q10_label.Visible = False
add_f.q10_text.Visible = False
Else
add_f.q10_label.Visible = True
add_f.q10_text.Visible = True
End If

'Function to read the rich text value from database
Dim sol_id As Integer = Val(CStr(dt.Tables("search_results").Rows(0).Item("ID")))
qry = "Select Solution_text from g_data where ID = @sol_id"
connect_my_database()
cmd = New OleDb.OleDbCommand(qry, gen_db_connect)
cmd.Parameters.AddWithValue("@sol_id", sol_id)

'Decompress code starts
Dim imageobj = cmd.ExecuteScalar
disconnect_my_database()

Dim bCompressed As Byte()
Console.Write(imageobj)
bCompressed = DirectCast(imageobj, Byte())
Dim bDecompressed() As Byte
Using compressedStream = New IO.MemoryStream(bCompressed)
Using zipStream = New IO.Compression.GZipStream(compressedStream, IO.Compression.CompressionMode.Decompress)
Using resultStream = New IO.MemoryStream()
zipStream.CopyTo(resultStream)
zipStream.Close()
bDecompressed = resultStream.ToArray
End Using
End Using
End Using
add_f.solution_text.Clear()
add_f.solution_text.Rtf = System.Text.ASCIIEncoding.ASCII.GetString(bDecompressed)
'decompress code end's here

Button2_Click(sender, e)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Someting went wrong")
Finally
disconnect_my_database()
End Try
End Sub




Coderv9

Continue reading...