none
writing a common to query mdb database and return values RRS feed

  • Question

  • 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

    Friday, August 10, 2018 7:51 PM

Answers

  • Yes there are better ways. First off, stay away from DataSet and DataAdapter when dealing with single tables as they are overkill. Create your connect via a “using” statement for each method rather than a single connection.

    Functions need to have a type e.g. Public Function return_single_value_from_db(…) As String. By not having this and having code compile indicates that Option Strict is Off and should be On.

    All queries when dealing with parameters never should have string concatenation other than to wrap the line for easy of reading, otherwise use parameters.

    Below is an example for working with MS-Access with backend classes.

    https://code.msdn.microsoft.com/CRUD-data-operations-for-4783d8dd


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Coderv9 Thursday, August 30, 2018 3:27 PM
    Friday, August 10, 2018 8:25 PM
    Moderator

All replies

  • Here is an example from my sandbox. Note that you can use binding and drop the stream methods if you were so inclined to do so.

    Imports System.Data.OleDb
    
    Public Class AccessConn
        Dim dt As New DataTable
        Dim bs As New BindingSource
        Private Sub AccessConn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM images", conn)
                    DA.Fill(dt)
                    bs.DataSource = dt
                    DataGridView1.DataSource = bs
    
                    PictureBox1.DataBindings.Add("Image", bs, "image", True)
                End Using
            End Using
    
        End Sub
    
        Private Sub Btn_BrowseForFile_Click(sender As Object, e As EventArgs) Handles Btn_BrowseForFile.Click
            Dim fb As New OpenFileDialog
            fb.InitialDirectory = "c:\"
            fb.Filter = "jpg files (*.jpg)|*.jpg|BMP files (*.bmp)|*.bmp"
            fb.RestoreDirectory = True
            If fb.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                PictureBox1.Image = Image.FromFile(fb.FileName)
            End If
        End Sub
    
        Private Sub Btn_Update_Click(sender As Object, e As EventArgs) Handles Btn_Update.Click
            Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
                                                            Data Source=C:\DATA\Access\AccessDB.accdb;")
                Using DA As New OleDbDataAdapter("SELECT * FROM images", conn)
                    Dim cb As New OleDbCommandBuilder(DA)
                    cb.QuotePrefix = "["
                    cb.QuoteSuffix = "]"
                    DA.Update(dt)
                End Using
            End Using
        End Sub
    End Class


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Friday, August 10, 2018 8:22 PM
    Friday, August 10, 2018 8:22 PM
  • Yes there are better ways. First off, stay away from DataSet and DataAdapter when dealing with single tables as they are overkill. Create your connect via a “using” statement for each method rather than a single connection.

    Functions need to have a type e.g. Public Function return_single_value_from_db(…) As String. By not having this and having code compile indicates that Option Strict is Off and should be On.

    All queries when dealing with parameters never should have string concatenation other than to wrap the line for easy of reading, otherwise use parameters.

    Below is an example for working with MS-Access with backend classes.

    https://code.msdn.microsoft.com/CRUD-data-operations-for-4783d8dd


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Coderv9 Thursday, August 30, 2018 3:27 PM
    Friday, August 10, 2018 8:25 PM
    Moderator
  • Thank you karen. Let me check if i can figure this out from your sample code.

    Coderv9

    Sunday, August 12, 2018 4:43 AM
  • Hi,

    Is your problem solved? If so, please mark the useful replies as answers. 

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 15, 2018 7:27 AM