neel
neel

Reputation: 5293

Insert data in to two tables one after another

I am working in ASP.NET MVC 4 using C# and sql.

I have two tables:

CREATE TABLE [dbo].[Mem_Basic] (
   [Id]      INT           IDENTITY (1, 1) NOT NULL,
   [Mem_NA]  VARCHAR (100) NOT NULL,
   [Mem_Occ] VARCHAR (200) NOT NULL,
   PRIMARY KEY CLUSTERED ([Id] ASC)
);

and

CREATE TABLE [dbo].[Mem_Details] (
   [Id]          INT           IDENTITY (1, 1) NOT NULL,
   [Mem_Id]      INT           NOT NULL,
   [Mem_Role]    VARCHAR (250) NOT NULL,
   [Mem_Email]   VARCHAR (50)  NULL,
   [Mem_MPh]     VARCHAR (15)  NULL,
   [Mem_DOB]     DATE          NULL,
   [Mem_BGr]     NCHAR (10)    NULL,
   [Mem_WAnn]    DATE          NULL,
   [Mem_Spouse]  VARCHAR (75)  NULL,
   [Mem_Web]     VARCHAR (300) NULL,
   PRIMARY KEY CLUSTERED ([Id] ASC)
);

I want to insert data into the first table and then insert the newly inserted id from the Mem_Basic table as Mem_Id into the Mem_Details table using same create button.

After that I want to return the first table's Id. I am using the following c# code

 public int CreateNewMember(string Mem_NA, string Mem_Occ )
 {
       using (SqlConnection con=new SqlConnection(Config.ConnectionString))
       {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Mem_Basic(Mem_Na, Mem_Occ) output INSERTED.Id VALUES(@na, @occ)", con))
            {
                cmd.Parameters.AddWithValue("@na", Mem_NA);
                cmd.Parameters.AddWithValue("@occ", Mem_Occ);
                con.Open();

                int modified = (int)cmd.ExecuteScalar();

                using (SqlCommand cmd1 = new SqlCommand("INSERT INTO Mem_Details (Mem_Id, Mem_Role) VALUES (@id, @role)", con))
                {
                    cmd.Parameters.AddWithValue("@id", modified);
                    cmd.Parameters.AddWithValue("@role","Member");
                }

                if (con.State == System.Data.ConnectionState.Open) con.Close();
                return modified;
            }
        }
    }

This will only insert the data into first table and return the Id.

Upvotes: 0

Views: 1359

Answers (3)

Ehsan
Ehsan

Reputation: 32671

you need to change cmd to cmd1 in your second using block and then call the exeute scalar

public int CreateNewMember(string Mem_NA, string Mem_Occ )
    {
        using (SqlConnection con=new SqlConnection(Config.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.Id VALUES(@na,@occ) SELECT SCOPE_IDENTITY()", con))
            {
                cmd.Parameters.AddWithValue("@na", Mem_NA);
                cmd.Parameters.AddWithValue("@occ", Mem_Occ);
                con.Open();
                int modified = (int)cmd.ExecuteScalar();
                using (SqlCommand cmd1 = new SqlCommand("INSERT INTO Mem_Details (Mem_Id,Mem_Role) VALUES (@id,@role) ", con))
                {

                    cmd1.Parameters.AddWithValue("@id", modified);// change here
                    cmd1.Parameters.AddWithValue("@role","Member");// change here
                    cmd1.ExecuteNonQuery(); //added this
                }
                if (con.State == System.Data.ConnectionState.Open) con.Close();
                return modified;
            }
        }
    }

Upvotes: 0

Damith
Damith

Reputation: 63065

I would change the code as below

using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("CreateNewMember", conn) { 
                           CommandType = CommandType.StoredProcedure }) {
   command.Parameters.AddWithValue("@na", Mem_NA);
   command.Parameters.AddWithValue("@occ", Mem_Occ);
   command.Parameters.AddWithValue("@role","Member");
   conn.Open();
   modified = (int)command.ExecuteScalar();
   conn.Close();
}

by using stored procedure

create proc CreateNewMember
(
     @na nvarchar(100), 
     @occ nvarchar(100),
     @role nvarchar(250)
)
as
begin

declare @ID int

      INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.Id VALUES(@na,@occ)

      select @ID = Scope_Identity()

      INSERT INTO Mem_Details (Mem_Id,Mem_Role) VALUES (@ID,@role)

      SELECT @ID
end

Upvotes: 2

Sasidharan
Sasidharan

Reputation: 3740

Try this.

 public int CreateNewMember(string Mem_NA, string Mem_Occ )
        {
            using (SqlConnection con=new SqlConnection(Config.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Mem_Basic(Mem_Na,Mem_Occ) output INSERTED.Id VALUES(@na,@occ)", con))
                {
                    cmd.Parameters.AddWithValue("@na", Mem_NA);
                    cmd.Parameters.AddWithValue("@occ", Mem_Occ);
                    con.Open();
                    int modified = (int)cmd.ExecuteScalar();
                    using (SqlCommand cmd1 = new SqlCommand("INSERT INTO Mem_Details (Mem_Id,Mem_Role) VALUES (@id,@role)", con))
                    {
                        cmd1.Parameters.AddWithValue("@id", modified);
                        cmd1.Parameters.AddWithValue("@role","Member");
                        cmd1.ExecuteNonQuery();
                    }
                    if (con.State == System.Data.ConnectionState.Open) con.Close();
                    return modified;
                }
            }
        }

Upvotes: 0

Related Questions