Insert Identity Column value to another table after record insert in Database using C# and VB.Net in ASP.Net

Hi George616,

Check this example. Now please take its reference and correct your code.

Database

CREATE TABLE SignUp
(
	UId INT IDENTITY PRIMARY KEY,
	Email VARCHAR(50),
	Pass VARCHAR(50),
	Con_pass VARCHAR(50),
	UserRole CHAR(1),
	CreatedBy INT,
	CreateDate DATE
)
 
CREATE TABLE Wallet
(
	Id INT IDENTITY PRIMARY KEY,
	UId INT,
	Amount INT
) 

HTML

<table>
    <tr>
        <td>Email</td>
        <td><asp:TextBox ID="txtEmail" runat="server" /></td>
    </tr>
    <tr>
        <td>Password</td>
        <td><asp:TextBox ID="txtPassword" runat="server" /></td>
    </tr>
    <tr>
        <td>Confirm Password</td>
        <td><asp:TextBox ID="txtConfirmPassword" runat="server" /></td>
    </tr>
    <tr>
        <td>User Role</td>
        <td>
            <asp:DropDownList ID="ddlRoles" runat="server">
                <asp:ListItem Text="Select" Value="0" />
                <asp:ListItem Text="A" Value="A" />
                <asp:ListItem Text="U" Value="U" />
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:Button ID="btnSave" Text="Save" runat="server" OnClick="OnSave" />
        </td>
    </tr>
</table>

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void OnSave(object sender, EventArgs e)
{
    string email = txtEmail.Text.Trim();
    string password = txtPassword.Text.Trim();
    string confirmPass = txtConfirmPassword.Text.Trim();
    string userRole = "A";
    string createdBy = "";
    int id = 0;
    if (ddlRoles.SelectedValue == "U")
    {
        userRole = "U";
        createdBy = Session["UserId"].ToString(); // Set Session after Login.
    }
    string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    if (password == confirmPass)
    {
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                string query = "INSERT INTO SignUp (Email,Pass,Con_pass,UserRole,CreatedBy,CreateDate) VALUES (@Email,@Passsword,@ConPass,@Role,@CreatedBy,@CreateDate)";
                query += " SELECT SCOPE_IDENTITY()";
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                cmd.Parameters.AddWithValue("@Email", email);
                cmd.Parameters.AddWithValue("@Passsword", password);
                cmd.Parameters.AddWithValue("@ConPass", confirmPass);
                cmd.Parameters.AddWithValue("@Role", userRole);
                cmd.Parameters.AddWithValue("@CreatedBy", !string.IsNullOrEmpty(createdBy) ? createdBy : (object)DBNull.Value);
                cmd.Parameters.AddWithValue("@CreateDate", DateTime.Now);
                con.Open();
                id = Convert.ToInt32(cmd.ExecuteScalar());
                con.Close();
            }
        }
    }

    if (id > 0)
    {
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Wallet (Uid,Amount) VALUES (@Id,@Amount)";
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@Amount", 0);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

VB.Net

Protected Sub OnSave(ByVal sender As Object, ByVal e As EventArgs)
    Dim email As String = txtEmail.Text.Trim()
    Dim password As String = txtPassword.Text.Trim()
    Dim confirmPass As String = txtConfirmPassword.Text.Trim()
    Dim userRole As String = "A"
    Dim createdBy As String = ""
    Dim id As Integer = 0
    If ddlRoles.SelectedValue = "U" Then
        userRole = "U"
        createdBy = Session("UserId").ToString()
    End If

    Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    If password = confirmPass Then
        Using con As SqlConnection = New SqlConnection(strConnString)
            Using cmd As SqlCommand = New SqlCommand()
                Dim query As String = "INSERT INTO SignUp (Email,Pass,Con_pass,UserRole,CreatedBy,CreateDate) VALUES (@Email,@Passsword,@ConPass,@Role,@CreatedBy,@CreateDate)"
                query += " SELECT SCOPE_IDENTITY()"
                cmd.Connection = con
                cmd.CommandType = CommandType.Text
                cmd.CommandText = query
                cmd.Parameters.AddWithValue("@Email", email)
                cmd.Parameters.AddWithValue("@Passsword", password)
                cmd.Parameters.AddWithValue("@ConPass", confirmPass)
                cmd.Parameters.AddWithValue("@Role", userRole)
                cmd.Parameters.AddWithValue("@CreatedBy", If(Not String.IsNullOrEmpty(createdBy), createdBy, CObj(DBNull.Value)))
                cmd.Parameters.AddWithValue("@CreateDate", DateTime.Now)
                con.Open()
                id = Convert.ToInt32(cmd.ExecuteScalar())
                con.Close()
            End Using
        End Using
    End If

    If id > 0 Then
        Using con As SqlConnection = New SqlConnection(strConnString)
            Using cmd As SqlCommand = New SqlCommand()
                cmd.Connection = con
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "INSERT INTO Wallet (Uid,Amount) VALUES (@Id,@Amount)"
                cmd.Parameters.AddWithValue("@Id", id)
                cmd.Parameters.AddWithValue("@Amount", 0)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End If
End Sub