How to make queries in Access Database using windows form in vb.net

Question

1.00/5 (1 vote)

See more:

while the criteria and the values are variables .. for example i have 6 check-boxes and 6 combo-boxes the check-boxes represents the field name that i need to make as criteria and the combo boxes are the values that will be set for each criteria. what i cannot accomplish is that if i checked more than one check-box to be added to the SELECT Query and accordingly when i choose a values from combo-boxes to be reflected to the values in the SELECT statement as well.. below are my codes but it is not working as i needed it to ... please help me. i need to be able to select one or many of the check-boxes and then select the values from the combo-boxes.

VB

Imports System
Imports System.IO
Imports System.Data
Imports System.Management
Imports System.Data.OleDb
Imports System.ComponentModel
Imports Microsoft.Office.Interop
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Reports
    Dim cnnOLEDB As New OleDbConnection
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim SystemUserName As String = Environment.UserName
    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\SupplierDB.mdb"
    'Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\mob-fs-01\Prepaid Complaint Handling\ppp\SupplierDB.mdb"
    Dim IntialFolder As String = My.Computer.FileSystem.SpecialDirectories.Desktop + "\Alerting Tool Extracted Files"
    Dim ChkVar1, ChkVar2, ChkVar3, ChkVar4, ChkVar5, ChkVar6, ChkVar7, ChkVar8 As String

    Private Sub SetFontAndColors()
        With Me.DataGridViewReports.DefaultCellStyle
            .Font = New Font("Segoe UI", 9)
            .ForeColor = Color.DarkOrange
            .BackColor = Color.GhostWhite
            .SelectionForeColor = Color.Black
            .SelectionBackColor = Color.Orange
            ' .Font = New Font(Control.DefaultFont, FontStyle.Bold)
        End With
    End Sub

    Private Sub Export_Report()
        If cnnOLEDB.State = ConnectionState.Open Then
            cnnOLEDB.Close()
        End If
        cnnOLEDB.ConnectionString = strConnectionString
        cnnOLEDB.Open()
        'verfying the datagridview having data or not
        If ((Me.DataGridViewReports.Columns.Count = 0) Or (Me.DataGridViewReports.Rows.Count = 0)) Then
            Exit Sub
        End If
        'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To Me.DataGridViewReports.ColumnCount - 1
            dset.Tables(0).Columns.Add(Me.DataGridViewReports.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To Me.DataGridViewReports.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To Me.DataGridViewReports.Columns.Count - 1
                dr1(j) = Me.DataGridViewReports.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next
        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()
        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next
        Next
        Dim time As DateTime = DateTime.Now
        Dim format As String = "MMM d yyyy on- HH mm ss "
        Dim myPath As String
        Dim myFile As String
        myFile = "Alerting Report Generated " & " - " & (time.ToString(format)) & ".xlsx"
        myPath = Environ("userprofile") & "\desktop\Alerting Tool Extracted Files\" & myFile
        wSheet.Columns.AutoFit()
        Dim strFileName As String = myPath
        Dim blnFileOpen As Boolean = False
        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
        End Try
        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If
        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = False
        MsgBox("Data saved to your Desktop.")
        'Kill Excell opened process
        Dim proc As System.Diagnostics.Process
        For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
            If proc.MainWindowTitle.ToString = "" Then
                proc.Kill()
            End If
        Next
        If cnnOLEDB.State = ConnectionState.Open Then
            cnnOLEDB.Close()
        End If
    End Sub

    Private Sub ExecuteQuery()
        If cnnOLEDB.State = ConnectionState.Open Then
            cnnOLEDB.Close()
        End If
        cnnOLEDB.ConnectionString = strConnectionString
        cnnOLEDB.Open()
        Try         
            If chk_Supplier.Checked = True Then ChkVar1 = "SupplierName = '" & cbo_Supplier.Text
            If chk_Action.Checked = True Then ChkVar2 = "ActionType = '" & cbo_Action.Text
            If chk_Action.Checked = True Then ChkVar3 = "Reason = '" & cbo_Reason.Text
            Dim sqlQRY, sqlQRY1, sqlQRY2, AN As String
            AN = " AND "
            sqlQRY1 = "Select * From Customers Where "
            sqlQRY2 = ChkVar1
            sqlQRY = sqlQRY1 & sqlQRY2 & AN & ChkVar2 & AN & ChkVar3
            Dim da As OleDbDataAdapter
            Dim ds As DataSet = New DataSet
            da = New OleDbDataAdapter(sqlQRY, cnnOLEDB)
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
            da.Fill(ds, "Customers")
            DataGridViewReports.DataSource = ds
            DataGridViewReports.DataMember = "Customers"
            lbl_RowCount.Text = DataGridViewReports.RowCount
        Catch ex As OleDbException
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnReport_reset_Click(sender As Object, e As EventArgs) Handles btnReport_reset.Click
        chk_ServiceName.Checked = False
        chk_Supplier.Checked = False
        chk_Shortcode.Checked = False
        chk_DateSent.Checked = False
        cbo_ServiceName.Text = ""
        cbo_Supplier.SelectedIndex = 0
        cbo_Shortcode.Text = ""
        cbo_DateSent.Text = ""
        chk_FeedbackDate.Checked = False
        chk_Action.Checked = False
        chk_Reason.Checked = False
        chk_Feedback.Checked = False
        cbo_FeedbackDate.Text = ""
        cbo_Action.SelectedIndex = 0
        cbo_Reason.SelectedIndex = 0
        cbo_Feedback.SelectedIndex = 0
        DataGridViewReports.DataSource = Nothing
    End Sub

    Private Sub btnReport_Run_Click(sender As Object, e As EventArgs) Handles btnReport_Run.Click
        ExecuteQuery()
    End Sub

    Private Sub Reports_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        Dim proc As System.Diagnostics.Process
        For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
            If proc.MainWindowTitle.ToString = "" Then
                proc.Kill()
            End If
        Next
        If cnnOLEDB.State = ConnectionState.Open Then
            cnnOLEDB.Close()
        End If
    End Sub

    Private Sub Reports_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cnnOLEDB.ConnectionString = strConnectionString
        cnnOLEDB.Open()
        SetFontAndColors()
        cbo_DateSent.Value = Today
        cbo_FeedbackDate.Value = Today
    End Sub

    Private Sub btnReport_Export_Click(sender As Object, e As EventArgs) Handles btnReport_Export.Click
        Export_Report()
    End Sub

    Private Sub chk_DateSent_CheckedChanged(sender As Object, e As EventArgs) Handles chk_DateSent.CheckedChanged
        If chk_DateSent.Checked Then
            cbo_DateSent.Enabled = True
        Else
            cbo_DateSent.Enabled = False
        End If
    End Sub

    Private Sub chk_FeedbackDate_CheckedChanged(sender As Object, e As EventArgs) Handles chk_FeedbackDate.CheckedChanged
        If chk_FeedbackDate.Checked Then
            cbo_FeedbackDate.Enabled = True
        Else
            cbo_FeedbackDate.Enabled = False
        End If
    End Sub

End Class

Posted 17-Dec-15 11:09am

BassamKassem

Updated 17-Dec-15 11:13am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-12-21T04:57:00

You have several issues to deal with here (apart from posting so much code that isn't relevant to your problem)

Firstly, spot the error in the preparation of the sql ... you haven't closed the single quotes on any the cbo_xxxx.Text lines

2nd - You are checking the value of chk_Action.Checked but using cbo_Reason.Text

3rd (and yet the most important) - you should NEVER use string concatenation to build a sql query, especially if user input is involved. Use Parameterized Queries[^] to avoid SQL Injection[^]. One of the other nice things about using command parameters is that you don't have to worry about those single quotes.

4th - you are adding the word "AND" regardless of whether you have added in any filters. If you debug this code with no checkboxes checked you end up with SQL of

SQL

Select * From Customers Where  AND  AND

which does not make any sense at all. If just Supplier is checked then you get

SQL

Select * From Customers Where SupplierName = 'cbo_Supplier.Text AND  AND

Ironically if both Suppler and Action are checked you end up with something that is almost there (if it wasn't for error 1 above)

Select * From Customers Where SupplierName = 'cbo_Supplier.Text AND ActionType = 'cbo_Action.Text AND Reason = 'cbo_Reason.Text

Try something like this instead

VB.NET

Dim cmd As OleDbCommand = New OleDbCommand()
Dim sqlQry As String = "Select * From Customers "
'Only add "WHERE" if at least one of the boxes is checked
sqlQry &= IIf(chk_Supplier.Checked Or chk_Action.Checked Or chk_Reason.Checked, " WHERE ", "")
'Only add the parameter for Supplier if Supplier box is checked
If chk_Supplier.Checked Then
    sqlQry &= "SupplierName = @Supplier"
    cmd.Parameters.AddWithValue("@Supplier", cbo_Supplier.Text)
End If
If chk_Action.Checked Then
    'Only add "AND" if Supplier box was checked (i.e. we already have a filter)
    sqlQry &= IIf(chk_Supplier.Checked, " AND ", "")
    sqlQry &= "ActionType = @Action"
    cmd.Parameters.AddWithValue("@Action", cbo_Action.Text)
End If
If chk_Reason.Checked Then
    'Only add " AND " if we have a previous filter in place already
    'I.e. one or both of Supplier and/or Action is checked
    sqlQry &= IIf(chk_Supplier.Checked Or chk_Action.Checked, " AND ", "")
    sqlQry &= "Reason = @Reason"
    cmd.Parameters.AddWithValue("@Reason", cbo_Reason.Text)
End If
cmd.CommandText = sqlQry

BassamKassem · Accepted Answer · 2015-12-29T00:05:00

i mixed up your method with mine as below .. thanks for your support.

VB

Try
            If cnnOLEDB.State = ConnectionState.Open Then
                cnnOLEDB.Close()
            End If
            cnnOLEDB.ConnectionString = strConnectionString
            cnnOLEDB.Open()
            Dim sqlQry As String = "Select * From Customers "
            'Only add "WHERE" if at least one of the boxes is checked
            sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked Or DateAdded.Checked Or SupplierFeedbackDate.Checked, " WHERE ", "")
            'Only add the parameter for Supplier if Supplier box is checked
            If SupplierName.Checked Then
                If cbo_Supplier.Text = vbNullString Then
                    sqlQry &= "SupplierName IS NOT NULL"
                Else
                    sqlQry &= "SupplierName = '" & cbo_Supplier.Text & "'"
                End If
            End If
            If Supplier_Feedback.Checked Then
                If cbo_Feedback.Text = vbNullString Then
                    'Only add "AND" if Supplier box was checked (i.e. we already have a filter)
                    sqlQry &= IIf(SupplierName.Checked, " AND ", "")
                    sqlQry &= "Supplier_Feedback IS NOT NULL"
                Else
                    'Only add "AND" if Supplier box was checked (i.e. we already have a filter)
                    sqlQry &= IIf(SupplierName.Checked, " AND ", "")
                    sqlQry &= "Supplier_Feedback = '" & cbo_Feedback.Text & "'"
                End If
            End If
            If Reason.Checked Then
                If cbo_Reason.Text = vbNullString Then
                    sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked, " AND ", "")
                    sqlQry &= "Reason IS NOT NULL"
                Else
                    'Only add " AND " if we have a previous filter in place already
                    'I.e. one or both of Supplier and/or Action is checked
                    sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked, " AND ", "")
                    sqlQry &= "Reason = '" & cbo_Reason.Text & "'"
                End If
            End If
            If ServiceName.Checked Then
                If TXTServiceName.Text = vbNullString Then
                    'I.e. one or both of Supplier and/or Action is checked
                    sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked, " AND ", "")
                    sqlQry &= "ServiceName IS NOT NULL"
                Else
                    'Only add " AND " if we have a previous filter in place already
                    'I.e. one or both of Supplier and/or Action is checked
                    sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked, " AND ", "")
                    sqlQry &= "ServiceName LIKE '%" & TXTServiceName.Text & "%'"
                End If
            End If
            If DateAdded.Checked Then
                'Only add " AND " if we have a previous filter in place already
                'I.e. one or both of Supplier and/or Action is checked
                sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked, " AND ", "")
                sqlQry &= "DateAdded >= #" + cbo_DateSent.Value + "# And DateAdded <= #" + cbo_DateSentTo.Value + "#"
            End If
            If SupplierFeedbackDate.Checked Then
                'Only add " AND " if we have a previous filter in place already
                'I.e. one or both of Supplier and/or Action is checked
                sqlQry &= IIf(SupplierName.Checked Or Supplier_Feedback.Checked Or Reason.Checked Or ServiceName.Checked Or DateAdded.Checked, " AND ", "")
                sqlQry &= "SupplierFeedbackDate >= #" + cbo_FeedbackDate.Value + "# And SupplierFeedbackDate <= #" + cbo_FeedbackDateTo.Value + "#"
            End If
            SQLText.Text = sqlQry
            Dim da As OleDbDataAdapter
            Dim ds As DataSet = New DataSet
            da = New OleDbDataAdapter(sqlQry, cnnOLEDB)
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
            da.Fill(ds, "Customers")
            DataGridViewReports.DataSource = ds
            DataGridViewReports.DataMember = "Customers"
            lbl_RowCount.Text = DataGridViewReports.RowCount
        Catch ex As OleDbException
            MessageBox.Show(ex.Message)
        End Try
        cnnOLEDB.Close()

How to make queries in Access Database using windows form in vb.net

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0