Auto generate No while Import data from Excel to Database using C# and VB.Net in ASP.Net

Try below Code

 In Sql

Create proc [dbo].[SP_Name]
as
select ISNULL (MAX(AdmissionNo),0)+1 from TableName
return

 In Class File

 public DataSet GetOrderIDUniqueMax()
        {
            DataSet ds = new DataSet();
            cmd = new SqlCommand("SP_Name", obj_con.Obj_ConLink);
            cmd.CommandType = CommandType.StoredProcedure;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }

 Code Behind

ClassName ObCls = new ClassName
  public void ORderIDGenearate()
        {
            ds = obCls.GetOrderIDUniqueMax();
            lblMaxIdForOrder.Text = ds.Tables[0].Rows[0][0].ToString();
        }

  _AdmissionNo = "R-" + lblMaxIdForOrder.Text;

Ouput id

R-0001

Hi smile,

Refer the below sample.

C#

protected void Page_Load(object sender, EventArgs e)
{
    // Get DataTable from Excel.
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("AdmissionNo", typeof(string)), new DataColumn("SName", typeof(string)) });
    dt.Rows.Add("0", "ABC");
    dt.Rows.Add("0", "SKY");
    dt.Rows.Add("0", "XYZ");
    DataTable dtFinal = dt.Clone();
    string srNo = "";
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        DataRow row = dtFinal.NewRow();
        row["AdmissionNo"] = "R - " + (i + 1).ToString().PadLeft(6, '0');
        row["SName"] = dt.Rows[i]["SName"];
        dtFinal.Rows.Add(row);
    }
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs)
	' Get DataTable from Excel.
	Dim dt As New DataTable()
	dt.Columns.AddRange(New DataColumn() {New DataColumn("AdmissionNo", GetType(String)), New DataColumn("SName", GetType(String))})
	dt.Rows.Add("0", "ABC")
	dt.Rows.Add("0", "SKY")
	dt.Rows.Add("0", "XYZ")
	Dim dtFinal As DataTable = dt.Clone()
	Dim srNo As String = ""
	For i As Integer = 0 To dt.Rows.Count - 1
		Dim row As DataRow = dtFinal.NewRow()
		row("AdmissionNo") = "R - " + (i + 1).ToString().PadLeft(6, "0"C)
		row("SName") = dt.Rows(i)("SName")
		dtFinal.Rows.Add(row)
	Next
End Sub

Output will come as per your expection.