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

smile
 
on May 05, 2021 02:28 AM
587 Views

How to delete maximum ID row from database using C# in Windows Application

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

The above is datagridview data. Now I want to delete the latest record in sequence against specific Id. for this I wrote the following query to delete the latest record

select MAX(SaleID) FROM tblItemSale where Reg_ID='C-000001';

it will delete the record against SaleID = '55'. Then '54' when I would like to delete.

Now my question is that how it will be implement in C# windows form to delete the latest row data against specific Reg_ID in datagridview using above query?

private void btnGet_Click(object sender, EventArgs e)
{
    try
    {
        con = new SqlDbConnect();
        con.SqlQuery(@"select SaleID,Reg_ID,C_Name,Phone,ItemName,B_Qty,T_Price,Adv_Payment,Balance,Install_Amount,Paid_Amount,Due_Date,RecoveredMan,SalesMan,Remarks,Install_Type from tblItemSale where Due_Date between @From and @To order by SaleID desc;");//inner join tblItems as i on s.ItemID=i.ItemID
        con.Cmd.Parameters.Add(new SqlParameter("@From", dtpFDate.Text));
        con.Cmd.Parameters.Add(new SqlParameter("@To", dtpTDate.Text));
        paging.SelectCommand = con.Cmd;
        sBuilder = new SqlCommandBuilder(paging);
        ds = new DataSet();
        paging.Fill(ds, "tblItemSale");
        sTable = ds.Tables["tblItemSale"];
        dGV.DataSource = ds.Tables["tblItemSale"].DefaultView;
        con.conClose();
        dGV.ReadOnly = true;
 
        //lblTotal.Text = dGVStock.Rows.Count.ToString();
        if (sTable.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());
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 06, 2021 04:13 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

FormDesign

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();
    }

    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()
    {
        try
        {
            DataSet ds = new DataSet();
            string query = "SELECT * FROM tblItemSale";
            using (SqlConnection con = new SqlConnection(conString))
            {
                SqlCommand cmd = new SqlCommand(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)
    {
        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", txtReg_ID.Text.Trim());
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        this.BindGrid();
    }
}

VB.Net

Partial Public Class Form1
    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) Handles MyBase.Load
        Me.BindGrid()
    End Sub

    Private Sub BindGrid()
        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(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)
        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", txtReg_ID.Text.Trim())
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using

        Me.BindGrid()
    End Sub
End Class

Screenshot

Database records after delete