Check record before Delete DataGridView row on Button Click using C# and VB.Net in Windows Application

smile
 
on May 20, 2021 11:38 PM
Sample_184728.zip
593 Views

If user select record against saleId= 10 and Reg_ID= C_000001 by mistake to delete then it must not delete any row and here It must display message that first delete latest record against Reg_ID = C-000001.

how to get it?

Delete maximum ID row from database using C# and VB.Net in Windows Application

private void button1_Click(object sender, EventArgs e)
{
    try
    {
        if (MessageBox.Show("Are you sure you want to delete this data?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
        {
             con = new SqlDbConnect();
             DateTime dtCurrentTime = DateTime.Now;
             con.SqlQuery("DELETE FROM tblItemSale WHERE SaleID= (SELECT MAX(SaleID) FROM tblItemSale WHERE Reg_ID=@RId)");
             con.Cmd.Parameters.AddWithValue("@RId", this.txtACNo.Text.Trim());
             con.NonQueryEx();
             con.conClose();
             this.GetCustomerHis();
             MessageBox.Show("Record Deleted Successfully", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Required output: 

SaleID

Reg_ID

Name

Amount

10

C-000001

A

5000

53

C-000001

A

6000

54

C-000001

A

7000

55

C-000001

A

8000

56

C-000002

B

6000

57

C-000002

B

5000

58

C-000003

C

11000

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 24, 2021 04:25 AM

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