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