Search Filter DataGridView using InputBox dialog in Windows Application

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Hi Everyone, 

I have this code that I use for search in my DataGridView, it work with ID column but not with Date and Description columns, I think it doesn’t show multiple results but only one but I am not sure. 

when I type into the search button the ID it shows me but when i type description or Date

Here is the code :

    Private Sub GroceryDataGridView_ColumnHeaderMouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles GroceryDataGridView.ColumnHeaderMouseDoubleClick
        Dim sqlstr As String
        Dim ds As New DataSet
        Dim SearchData As String
        SearchData = InputBox("Search Date", "Enter information ")
        If SearchData = "" Then
            Exit Sub
        End If
        Select Case e.ColumnIndex
            Case 1
                sqlstr = "ID=" & SearchData

            Case 2
                sqlstr = "Description=" & SearchData

            Case Else
                sqlstr = "Date=" & SearchData
        End Select
        ds = GetData("Expenses", sqlstr)
        GroceryDataGridView.DataSource = ds.Tables(0)
    End Sub

    Public Function GetData(ByVal Tablename As String, ByVal FilterString As String) As DataSet
        On Error Resume Next
        Dim cmd As New OleDb.OleDbCommand
        Dim ids As New DataSet

        GetData = Nothing
        m_dbcon.ConnectionString = "Provider = sqloledb;Data Source=;Initial Catalog=Expenses;Trusted_Connection=yes;"
        m_dbcon.Open()

        cmd.Connection = m_dbcon
        If FilterString.Contains("Where") Then
        Else
            If FilterString <> "" Then FilterString = "WHERE " & FilterString
        End If

        If (FilterString = "") Then
            cmd.CommandText = String.Format("Select ID, Date , Description, Quantity, Price, Store, Payment, MwSt from {0}", Tablename)

        Else
            cmd.CommandText = String.Format("Select ID, Date , Description, Quantity, Price, Store, Payment, MwSt from {0} {1} ", Tablename, FilterString)
        End If

        m_DataAdapter.SelectCommand = cmd
        m_DataAdapter.Fill(ids, Tablename)

        GetData = ids
        m_dbcon.Close()
        Return GetData
    End Function

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

Thank you for your help, Best Regards

Posted 5 months ago Modified on 5 months ago

Hi elvisidrizi1,

Check this example. Now please take its reference and correct your code.

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using Microsoft.VisualBasic;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic

Code

C#

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        BindGrid();
    }

    private void GroceryDataGridView_ColumnHeaderMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
    {
        string sqlstr = "";
        DataSet ds = new DataSet();
        string SearchData;
        SearchData = Interaction.InputBox("Search Date", "Enter information ");
        if (SearchData == "")
            return;
        switch (e.ColumnIndex)
        {
            case 0:
                    sqlstr = "ID=" + SearchData;
                    break;
            case 2:
                    sqlstr = "Description='" + SearchData + "'";
                    break;
            case 1:
                    sqlstr = "Date='" + Convert.ToDateTime(SearchData) + "'";
                    break;
            default:
                    break;
        }
        ds = GetData("Expenses", sqlstr);
        GroceryDataGridView.DataSource = ds.Tables[0];
    }

    public DataSet GetData(string Tablename, string FilterString)
    {
        DataSet ids = new DataSet();
        DataSet GetData = new DataSet();
        SqlConnection m_dbcon = new SqlConnection();
        m_dbcon.ConnectionString = @"Data Source=.;Initial Catalog=master;User id = sa;password=pass@123";
        m_dbcon.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = m_dbcon;
        if (FilterString.Contains("Where"))
        {
        }
        else if (FilterString != "")
        {
            FilterString = "WHERE " + FilterString;
        }
        if (FilterString == "")
        {
            cmd.CommandText = string.Format("Select ID, Date , Description, Quantity, Price, Store, Payment, MwSt from {0}", Tablename);
        }
        else
        {
            cmd.CommandText = string.Format("Select ID, Date , Description, Quantity, Price, Store, Payment, MwSt from {0} {1} ", Tablename, FilterString);
        }
        SqlDataAdapter m_DataAdapter = new SqlDataAdapter();
        m_DataAdapter.SelectCommand = cmd;
        m_DataAdapter.Fill(ids, Tablename);
        GetData = ids;
        m_dbcon.Close();
        return GetData;
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
        }
        finally
        {
            GC.Collect();
        }
    }

    private void BindGrid()
    {
        string constring = @"Data Source=.;Initial Catalog=master;User id = sa;password=pass@123";
        using (SqlConnection con = new SqlConnection(constring))
        {
            using (SqlCommand cmd = new SqlCommand("Select ID,Date,Description from Expenses", con))
            {
                cmd.CommandType = CommandType.Text;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds);
                        GroceryDataGridView.DataSource = ds.Tables[0];
                    }
                }
            }
        }
    }
}

VB.Net

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        BindGrid()
    End Sub

    Private Sub GroceryDataGridView_ColumnHeaderMouseDoubleClick(ByVal sender As Object, ByVal e As DataGridViewCellMouseEventArgs) Handles GroceryDataGridView.ColumnHeaderMouseDoubleClick
        Dim sqlstr As String = ""
        Dim ds As New DataSet
        Dim SearchData As String
        SearchData = InputBox("Search Date", "Enter information ")
        If SearchData = "" Then
            Exit Sub
        End If
        Select Case e.ColumnIndex
            Case 0
                sqlstr = "ID=" & SearchData
            Case 2
                sqlstr = "Description='" & SearchData & "'"
            Case 1
                sqlstr = "Date='" & Convert.ToDateTime(SearchData) & "'"
            Case Else
        End Select
        ds = GetData("Expenses", sqlstr)
        GroceryDataGridView.DataSource = ds.Tables(0)
    End Sub

    Public Function GetData(ByVal Tablename As String, ByVal FilterString As String) As DataSet
        On Error Resume Next
        Dim ids As New DataSet
        GetData = Nothing
        Dim m_dbcon As SqlConnection = New SqlConnection()
        m_dbcon.ConnectionString = "Data Source=.;Initial Catalog=master;User id = sa;password=pass@123"
        m_dbcon.Open()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = m_dbcon
        If FilterString.Contains("Where") Then
        Else
            If FilterString <> "" Then FilterString = "WHERE " & FilterString
        End If
        If (FilterString = "") Then
            cmd.CommandText = String.Format("Select ID, Date , Description, Quantity, Price, Store, Payment, MwSt from {0}", Tablename)
        Else
            cmd.CommandText = String.Format("Select ID, Date , Description, Quantity, Price, Store, Payment, MwSt from {0} {1} ", Tablename, FilterString)
        End If
        Dim m_DataAdapter As SqlDataAdapter = New SqlDataAdapter()
        m_DataAdapter.SelectCommand = cmd
        m_DataAdapter.Fill(ids, Tablename)
        GetData = ids
        m_dbcon.Close()
        Return GetData

    End Function

    Private Sub releaseObject(ByVal obj As Object)
        Try
            Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub BindGrid()
        Dim constring As String = "Data Source=.;Initial Catalog=master;User id = sa;password=pass@123"
        Using con As New SqlConnection(constring)
            Using cmd As New SqlCommand("Select ID,Date,Description from Expenses", con)
                cmd.CommandType = CommandType.Text
                Using sda As New SqlDataAdapter(cmd)
                    Using ds As New DataSet()
                        sda.Fill(ds)
                        GroceryDataGridView.DataSource = ds.Tables(0)
                    End Using
                End Using
            End Using
        End Using
    End Sub
End Class

Screenshot