Insert Auto Increment Id in Database based on User in ASP.Net using C# and VB.Net

Hi nauna,

You need to another column in the table for maintaining the which user inserting the record.

Please refer below sample.

SQL

CREATE TABLE TestDemoUser(ID INT, CityId INT, Users VARCHAR(20))

HTML

UserName :
<asp:TextBox runat="server" ID="txtUserName" />
<asp:DropDownList runat="server" ID="ddlCityId">
    <asp:ListItem Text="Select" />
    <asp:ListItem Text="1" />
    <asp:ListItem Text="2" />
</asp:DropDownList>
<asp:Button Text="Insert" runat="server" OnClick="Insert" />
<asp:GridView runat="server" AutoGenerateColumns="false" ID="gvUserDetails">
    <Columns>
        <asp:BoundField DataField="ID" HeaderText="ID" />
        <asp:BoundField DataField="CityId" HeaderText="CityId" />
        <asp:BoundField DataField="Users" HeaderText="Users" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Insert(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("SELECT MAX(ID) ID FROM TestDemoUser WHERE Users =@Users", con);
    cmd.Parameters.AddWithValue("@Users", txtUserName.Text);
    con.Open();
    int id = Convert.ToInt32(cmd.ExecuteScalar().ToString() == "" ? 0 : cmd.ExecuteScalar());
    if (id == 0)
    {
        cmd = new SqlCommand("INSERT INTO TestDemoUser(ID, CityId, Users) VALUES(@ID, @CityId, @Users)", con);
        cmd.Parameters.AddWithValue("@ID", 1);
        cmd.Parameters.AddWithValue("@Users", txtUserName.Text);
        cmd.Parameters.AddWithValue("@CityId", ddlCityId.SelectedItem.Text);
        cmd.ExecuteNonQuery();
        con.Close();
    }
    else
    {
        id++;
        cmd = new SqlCommand("INSERT INTO TestDemoUser(ID, CityId, Users) VALUES(@ID, @CityId, @Users)", con);
        cmd.Parameters.AddWithValue("@ID", id);
        cmd.Parameters.AddWithValue("@Users", txtUserName.Text);
        cmd.Parameters.AddWithValue("@CityId", ddlCityId.SelectedItem.Text);
        cmd.ExecuteNonQuery();
    }
    cmd = new SqlCommand("SELECT * FROM TestDemoUser", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    this.gvUserDetails.DataSource = dt;
    this.gvUserDetails.DataBind();
    con.Close();
}

VB.Net

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand("SELECT MAX(ID) ID FROM TestDemoUser WHERE Users =@Users", con)
    cmd.Parameters.AddWithValue("@Users", txtUserName.Text)
    con.Open()
    Dim id As Integer = Convert.ToInt32(If(cmd.ExecuteScalar().ToString() = "", 0, cmd.ExecuteScalar()))

    If id = 0 Then
        cmd = New SqlCommand("INSERT INTO TestDemoUser(ID, CityId, Users) VALUES(@ID, @CityId, @Users)", con)
        cmd.Parameters.AddWithValue("@ID", 1)
        cmd.Parameters.AddWithValue("@Users", txtUserName.Text)
        cmd.Parameters.AddWithValue("@CityId", ddlCityId.SelectedItem.Text)
        cmd.ExecuteNonQuery()
        con.Close()
    Else
        id += 1
        cmd = New SqlCommand("INSERT INTO TestDemoUser(ID, CityId, Users) VALUES(@ID, @CityId, @Users)", con)
        cmd.Parameters.AddWithValue("@ID", id)
        cmd.Parameters.AddWithValue("@Users", txtUserName.Text)
        cmd.Parameters.AddWithValue("@CityId", ddlCityId.SelectedItem.Text)
        cmd.ExecuteNonQuery()
    End If

    cmd = New SqlCommand("SELECT * FROM TestDemoUser", con)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)
    Me.gvUserDetails.DataSource = dt
    Me.gvUserDetails.DataBind()
    con.Close()
End Sub

Screenshot