Hi smile,
Check this example. Now please take its reference and correct your code.
Database
CREATE TABLE tblItemSale(SaleID INT PRIMARY KEY,Reg_ID VARCHAR(20),Name CHAR(5),Amount INT)
INSERT INTO tblItemSale VALUES(10,'C-000001','A',5000)
INSERT INTO tblItemSale VALUES(53,'C-000001','A',6000)
INSERT INTO tblItemSale VALUES(54,'C-000001','A',7000)
INSERT INTO tblItemSale VALUES(55,'C-000001','A',8000)
INSERT INTO tblItemSale VALUES(56,'C-000002','B',6000)
INSERT INTO tblItemSale VALUES(57,'C-000002','B',5000)
INSERT INTO tblItemSale VALUES(58,'C-000003','C',11000)
SELECT * FROM tblItemSale
Namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Code
C#
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
this.CenterToScreen();
}
private const string conString = @"Data Source=.\SQL2014;Initial Catalog=master;Integrated Security = true";
private void Form1_Load(object sender, EventArgs e)
{
this.BindGrid();
}
private void BindGrid(string actNo = null)
{
try
{
DataSet ds = new DataSet();
string query = "SELECT * FROM tblItemSale";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand();
if (!string.IsNullOrEmpty(actNo))
{
query += " WHERE Reg_ID LIKE @SearchText + '%'";
cmd.Parameters.AddWithValue("@SearchText", actNo);
}
cmd.CommandText = query;
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(ds, "tblItemSale");
}
}
dGV.DataSource = ds.Tables["tblItemSale"].DefaultView;
dGV.ReadOnly = true;
if (ds.Tables["tblItemSale"].Rows.Count < 1)
{
MessageBox.Show("No Record Found", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
dGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dGV.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dGV.RowsDefaultCellStyle.BackColor = Color.GhostWhite;
dGV.AlternatingRowsDefaultCellStyle.BackColor = Color.GhostWhite;
dGV.CellBorderStyle = DataGridViewCellBorderStyle.Single;
dGV.DefaultCellStyle.SelectionBackColor = Color.ForestGreen;
dGV.DefaultCellStyle.SelectionForeColor = Color.White;
dGV.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
dGV.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
dGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dGV.AllowUserToResizeColumns = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void OnDelete(object sender, EventArgs e)
{
int saleId = Convert.ToInt32(dGV.SelectedCells[0].Value.ToString().Trim());
string regId = this.txtACNo.Text.Trim();
int maxSaleID = 0;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand("SELECT MAX(SaleID) FROM tblItemSale WHERE Reg_ID = @RegId", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@RegId", regId);
con.Open();
maxSaleID = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
}
if (saleId == maxSaleID)
{
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM tblItemSale WHERE SaleId = (SELECT MAX(SaleID) FROM tblItemSale WHERE Reg_ID = @RegId)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@RegId", regId);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
this.BindGrid(txtACNo.Text.Trim());
}
else
{
MessageBox.Show("first delete latest record against Reg_ID = " + regId, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void OnTextChange(object sender, EventArgs e)
{
this.BindGrid(txtACNo.Text.Trim());
}
}
VB.Net
Public Partial Class Form1
Public Sub New()
InitializeComponent()
Me.CenterToScreen()
End Sub
Private Const conString As String = "Data Source=.\SQL2014;Initial Catalog=master;Integrated Security = true"
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
Me.BindGrid()
End Sub
Private Sub BindGrid(ByVal Optional actNo As String = Nothing)
Try
Dim ds As DataSet = New DataSet()
Dim query As String = "SELECT * FROM tblItemSale"
Using con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand()
If Not String.IsNullOrEmpty(actNo) Then
query += " WHERE Reg_ID LIKE @SearchText + '%'"
cmd.Parameters.AddWithValue("@SearchText", actNo)
End If
cmd.CommandText = query
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(ds, "tblItemSale")
End Using
End Using
dGV.DataSource = ds.Tables("tblItemSale").DefaultView
dGV.ReadOnly = True
If ds.Tables("tblItemSale").Rows.Count < 1 Then
MessageBox.Show("No Record Found", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
Return
End If
dGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
dGV.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
dGV.RowsDefaultCellStyle.BackColor = Color.GhostWhite
dGV.AlternatingRowsDefaultCellStyle.BackColor = Color.GhostWhite
dGV.CellBorderStyle = DataGridViewCellBorderStyle.Single
dGV.DefaultCellStyle.SelectionBackColor = Color.ForestGreen
dGV.DefaultCellStyle.SelectionForeColor = Color.White
dGV.DefaultCellStyle.WrapMode = DataGridViewTriState.[True]
dGV.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
dGV.SelectionMode = DataGridViewSelectionMode.FullRowSelect
dGV.AllowUserToResizeColumns = True
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Private Sub OnDelete(ByVal sender As Object, ByVal e As EventArgs)
Dim saleId As Integer = Convert.ToInt32(dGV.SelectedCells(0).Value.ToString().Trim())
Dim regId As String = Me.txtACNo.Text.Trim()
Dim maxSaleID As Integer = 0
Using con As SqlConnection = New SqlConnection(conString)
Using cmd As SqlCommand = New SqlCommand("SELECT MAX(SaleID) FROM tblItemSale WHERE Reg_ID = @RegId", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@RegId", regId)
con.Open()
maxSaleID = Convert.ToInt32(cmd.ExecuteScalar())
con.Close()
End Using
End Using
If saleId = maxSaleID Then
Using con As SqlConnection = New SqlConnection(conString)
Using cmd As SqlCommand = New SqlCommand("DELETE FROM tblItemSale WHERE SaleId = (SELECT MAX(SaleID) FROM tblItemSale WHERE Reg_ID = @RegId)", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@RegId", regId)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Me.BindGrid(txtACNo.Text.Trim())
Else
MessageBox.Show("first delete latest record against Reg_ID = " & regId, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub
Private Sub OnTextChange(ByVal sender As Object, ByVal e As EventArgs)
Me.BindGrid(txtACNo.Text.Trim())
End Sub
End Class
Screenshot