Reputation: 5293
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
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
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
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