Get a new record primary key identifier from an SQL insert query to insert the same value into another table?

Question

I have the following C #

protected void add_button_Click(object sender, EventArgs e) { string teamname = team_name_tb.Text; string teamstatus = "Not Started"; string teamnotes = team_notes_tb.Text; string sprintid = sprint_select.SelectedValue; string ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(ConnectionString); myConnection.Open(); String query = "INSERT INTO teams (team_name, status, notes) VALUES (@team_name, @status, @notes); INSERT INTO sprints_vs_teams (sprint_id, team_id) VALUES (@sprint_id, @team_id)"; SqlCommand myCommand = new SqlCommand(query, myConnection); myCommand.Parameters.AddWithValue("@team_name", teamname); myCommand.Parameters.AddWithValue("@status", teamstatus); myCommand.Parameters.AddWithValue("@notes", teamnotes); myCommand.Parameters.AddWithValue("@sprint_id", sprintid); myCommand.Parameters.AddWithValue("@team_id", ????); myCommand.ExecuteNonQuery(); myConnection.Close(); } 

In the teams table, the primary key is team_id I want to pull the value of this and paste it into the team_id field in the lookup table, I have sprints_vs_teams . Is there a way to do this, if you could provide me some recommendations, remember that I am very new to C #. Any help would be greatly appreciated.

Modified code

 protected void add_button_Click(object sender, EventArgs e) { String user = Session["user_id"].ToString(); string teamname = team_name_tb.Text; string teamstatus = "Not Started"; string teamnotes = team_notes_tb.Text; string sprintid = sprint_select.SelectedValue; string ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(ConnectionString); myConnection.Open(); String query = "Declare @team_identity int INSERT INTO teams (team_name, status, notes) VALUES (@team_name, @status, @notes); set @team_identity = scope_identity() INSERT INTO sprints_vs_teams (sprint_id, team_id) VALUES (@sprint_id, @team_identity)"; SqlCommand myCommand = new SqlCommand(query, myConnection); myCommand.Parameters.AddWithValue("@team_name", teamname); myCommand.Parameters.AddWithValue("@status", teamstatus); myCommand.Parameters.AddWithValue("@notes", teamnotes); myCommand.Parameters.AddWithValue("@sprint_id", sprintid); myCommand.ExecuteNonQuery(); myConnection.Close(); } 

I modified my code with @Sick's answer, however it didn't seem to work. If someone could advise where I might have done wrong, it would be very helpful.

Answer 1

Use SCOPE_IDENTITY

 Declare @team_identity int INSERT INTO teams (team_name, status, notes) VALUES (@team_name, @status, @notes); set @team_identity = scope_identity() INSERT INTO sprints_vs_teams (sprint_id, team_id) VALUES (@sprint_id, @team_identity)" 

Answer 2

If I am not mistaken, you should declare team_identity as cmd.parameter. Of course, there is no parameter in it. C # is still expecting one

 cmd.paramater.addwithvalue("@team_identity", paramaterDirection.out); 

Or something like this

Answer 3

after inserting a row into the teams table, write the following code to get the current value of the identifier and use it during insertion into the sprints_vs_teams table.

 SqlCommand cmd=new SqlCommand("select Ident_current('teams')",Cn); Cn.Open(); int teamid=int.Parse(Cmd.ExecuteScalar().ToString()); 

Hope this helps