DataGridView Search with Date Range and Rows = Label

[CodingSource]

Dear fellow programmers,

I have tweaked this code for some time, and again failed. I want the user to select a client in the combobox and its text, with date range (two datetimepickers) the results will show in a click of a button.

CODE for DataGridView search results:

Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\david\Documents\Visual Studio 2008\Projects\Golden Dragon Billing System\Golden Dragon Billing System\EncodingComplete.accdb"
        Dim con As OleDbConnection = New OleDbConnection(ConnectionString)
        Dim com As OleDbCommand
        Dim oledbda As OleDbDataAdapter
        Dim ds As DataSet
        Dim str As String

        If ComboBox1.Text = "" Then
            ErrorProvider1.SetError(ComboBox1, "Please select a client")
        Else

            Try
                con.Open()
                str = "SELECT * FROM EncodingComplete WHERE Client=" & ComboBox1.Text & "'"
                com = New OleDbCommand(str, con)
                oledbda = New OleDbDataAdapter(com)
                ds = New DataSet
                oledbda.Fill(ds, "EncodingComplete_table")
                con.Close()
                DataGridView1.DataSource = ds
                DataGridView1.DataMember = "EncodingComplete_table"
            Catch ex As Exception
                MsgBox(ErrorToString)
            End Try


        End If

CODE for Date Range:

 Dim sqlsearch As String
        Dim con As New OleDbConnection(Get_Constring)

        Dim date1 As Date = DateTimePicker1.Value.Date
        Dim date2 As Date = DateTimePicker2.Value.Date

        sqlsearch = "SELECT Client, Address, Date_Bill, BusStyle_Name, Vessel, TIN, Port, [Pier No], InterPleader1, InterPleader2, Call_Date, Call_Time, Call_VoyageNo, Call_ReceiveBy, VesselType, VesselCalls, FixedFee_ExcessCum, FixedFee_Amount, FixedFee_Rate, FixedFee_Total, OilyExcessCum, OilyRate, OilyAmnt, OilyTotal, GarbageExcessCum, GarbageRate, GarbageAmnt, GarbageTotal, NoxiousExcessCum, NoxiousRate, NoxiousAmnt, NoxiousTotal, Other, OtherExcessCum, OtherRate, OtherAmnt, OtherTotal, DollarConvers, AmountDue, PaymentDetails, TypeofPayment, DateofPayment, OrNo, Amount, Collector, BI, CSR FROM EncodingComplete WHERE Date_Bill BETWEEN @startDate AND @endDate order by Date_Bill desc"

        Dim adapter As New OleDbDataAdapter(sqlsearch, con)

        With adapter.SelectCommand.Parameters
            .AddWithValue("@startDate", date1)
            .AddWithValue("@endDate", date2)
        End With

        ' Shows the records and updates the DataGridView

        Dim dt As New DataTable("EncodingComplete")

        adapter.Fill(dt)
        DataGridView1.DataSource = dt

However, when I click the 'Search' button, the datagridview goes blank and I don't know why. As you can see there are no codes for resetting. I construct a new code for date range for the datagridview to filter search (this is where I get very confused).

Me.BindingSource1.Filter = "Date_Bill >= '" & DateTimePicker1.Text & "' and Date_Bill <= '" & DateTimePicker2.Text & "'"

The datagridview still goes blank. I am getting hopeless. Please help..

[Santanu.Das]

Do not understand which codeblocks you used to show your result in datagridview Block1 or Block2 Or both simultaneously. If you use both codeblocks simultaneously in a Button_Click event, Blank is the currect result.
To get result use any one of the codeblocks with proper Sql Statement.

CodeBlocks1 :
Firstly, Here is a simple mistake in SQL Statement. A Single Quatation charector i.e. ** ' ** is required at Where **Clause.
You did **WHERE Client=" & ComboBox1.Text & "'", but it would be WHERE Client='" & ComboBox1.Text & "'".

Secondly: Call the Close() method of Connection Object after asigning the DataSource Property of the DataGridView Object.
The codes are

Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\david\Documents\Visual Studio 2008\Projects\Golden Dragon Billing System\Golden Dragon Billing System\EncodingComplete.accdb"
        Dim con As OleDbConnection = New OleDbConnection(ConnectionString)
        Dim com As OleDbCommand
        Dim oledbda As OleDbDataAdapter
        Dim ds As DataSet
        Dim str As String
        If ComboBox1.Text = "" Then
            ErrorProvider1.SetError(ComboBox1, "Please select a client")
        Else
            Try
                con.Open()
                str = "SELECT * FROM EncodingComplete WHERE Client='" & ComboBox1.Items(ComboBox1.SelectedIndex) & "'"
                com = New OleDbCommand(str, con)
                oledbda = New OleDbDataAdapter(com)
                ds = New DataSet
                oledbda.Fill(ds, "EncodingComplete_table")
                DataGridView1.DataSource = ds
                DataGridView1.DataMember = "EncodingComplete_table"
                con.Close()
            Catch ex As Exception
                MsgBox(ErrorToString)
            End Try
        End If

If you want to display result filtering by dates the SQL Statement should be

 str = "SELECT * FROM EncodingComplete WHERE Client =@ClientNm And  Date_Bill BETWEEN @startDate AND @endDate Order By Date_Bill desc"

The codeblock should be

Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\david\Documents\Visual Studio 2008\Projects\Golden Dragon Billing System\Golden Dragon Billing System\EncodingComplete.accdb"

Dim con As OleDbConnection = New OleDbConnection(ConnectionString)
Dim str As String
If ComboBox1.Text = "" Then
    ErrorProvider1.SetError(ComboBox1, "Please select a client")
Else
    Try
        con.Open()
        str = "SELECT * FROM EncodingComplete WHERE Client =@ClientNm And Date_Bill BETWEEN @startDate AND @endDate Order by Date_Bill desc"                    
        Dim adapter As New OleDbDataAdapter(str, con)

        With adapter.SelectCommand.Parameters
            .AddWithValue("@ClientNm", ComboBox1.Items(ComboBox1.SelectedIndex))
            .AddWithValue("@startDate", DateTimePicker1.Value.Date)
            .AddWithValue("@endDate", DateTimePicker2.Value.Date)
        End With

        ' Shows the records and updates the DataGridView
        Dim dt As New DataTable("EncodingComplete")
        adapter.Fill(dt)
        DataGridView1.DataSource = dt
        con.Close()
    Catch ex As Exception
        MsgBox(ErrorToString)
    End Try
End If

Hope it should help you.