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

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