Vb.net and SQL query

Peter.L

New member
Joined
Jun 10, 2021
Messages
2
Programming Experience
Beginner
Hello community.

I am fairly new to programing and I started a small project with Visual Studio 2015, and created a small application in WinForms. The applications main purpose is database control via Microsoft Access, using a simple OleDbDataAdapter connection, using and SQL query to load up multiple lists in comboboxes.

My question is with the below queries if there is a better way to call it? As in to make a single query to run all 3 in the same procedure.

If this is the wrong section to post this, it can be moved.

Thank you in advance!

Retrieves some parameters from Access:
Try
            Dim sql As String = ("SELECT param2 FROM tblParameters WHERE param1 LIKE '%' & 'TO252' & '%'")
            Dim da As New OleDbDataAdapter(sql, conPara)
            Dim ds As New DataSet
            da.Fill(ds)
            cbTO.ValueMember = "param2"
            cbTO.DataSource = ds.Tables(0)
            cbTO.SelectedIndex = -1

        Catch ex As System.Exception
            MsgBox("ERROR : " & ex.Message.ToString)
        End Try

        Try
            Dim sql As String = ("SELECT param2 FROM tblParameters WHERE param1 LIKE '%' & '0402' & '%'")
            Dim da As New OleDbDataAdapter(sql, conPara)
            Dim ds As New DataSet
            da.Fill(ds)
            cbChip.ValueMember = "param2"
            cbChip.DataSource = ds.Tables(0)
            cbChip.SelectedIndex = -1

        Catch ex As System.Exception
            MsgBox("ERROR : " & ex.Message.ToString)
        End Try

        Try
            Dim sql As String = ("SELECT param2 FROM tblParameters WHERE param1 LIKE '%' & 'orderreason' & '%'")
            Dim da As New OleDbDataAdapter(sql, conPara)
            Dim ds As New DataSet
            da.Fill(ds)
            cbOrder.ValueMember = "param2"
            cbOrder.DataSource = ds.Tables(0)
            cbOrder.SelectedIndex = -1

        Catch ex As System.Exception
            MsgBox("ERROR : " & ex.Message.ToString)
        End Try
 
Solution
As for the question, yes, you can do it all with a single query. Just put all three conditions in the WHERE clause and populate a single DataTable. You can then create three DataViews from that one table and bind them to the controls:
VB.NET:
Dim sql As String = "SELECT param1, param2
                     FROM tblParameters
                     WHERE param1 LIKE '%TO252%'
                     OR param1 LIKE '%0402%'
                     OR param1 LIKE '%orderreason%'"
Dim da As New OleDbDataAdapter(sql, conPara)
Dim dt As New DataTable

da.Fill(dt)

Dim dv1 As New DataView(dt) With {.RowFilter = "param1 LIKE '*TO252*'"}
Dim dv2 As New DataView(dt) With {.RowFilter = "param1 LIKE '*0402*'"}
Dim dv3 As New DataView(dt) With...

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,496
Location
Sydney, Australia
Programming Experience
10+
As for the question, yes, you can do it all with a single query. Just put all three conditions in the WHERE clause and populate a single DataTable. You can then create three DataViews from that one table and bind them to the controls:
VB.NET:
Dim sql As String = "SELECT param1, param2
                     FROM tblParameters
                     WHERE param1 LIKE '%TO252%'
                     OR param1 LIKE '%0402%'
                     OR param1 LIKE '%orderreason%'"
Dim da As New OleDbDataAdapter(sql, conPara)
Dim dt As New DataTable

da.Fill(dt)

Dim dv1 As New DataView(dt) With {.RowFilter = "param1 LIKE '*TO252*'"}
Dim dv2 As New DataView(dt) With {.RowFilter = "param1 LIKE '*0402*'"}
Dim dv3 As New DataView(dt) With {.RowFilter = "param1 LIKE '*orderreason*'"}

cbTO.DisplayMember = "param2"
cbTO.ValueMember = "param2"
cbTO.DataSource = dv1
cbTO.SelectedIndex = -1

cbChip.DisplayMember = "param2"
cbChip.ValueMember = "param2"
cbChip.DataSource = dv2
cbChip.SelectedIndex = -1

cbOrder.DisplayMember = "param2"
cbOrder.ValueMember = "param2"
cbOrder.DataSource = dv3
cbOrder.SelectedIndex = -1
You need to include param1 in the query so that you can filter on it locally.
 
Solution

Peter.L

New member
Joined
Jun 10, 2021
Messages
2
Programming Experience
Beginner
Hi,

Thank you so much. This is working as expected. I was trying so hard to make it work in a single query. Didn't realize it would be this easy. Thank you again!
 
Top Bottom