I have the following C#

我有以下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 be able to pull the value of this and insert it into the team_id field in the lookup table I have sprints_vs_teams. Is there a method of doing this, if so could you possibly provide my with some guidance, please be aware I am very new to C#. Any help would be greatly appreciated.

在teams表中，主键是team_id我希望能够获取this的值并将其插入到查找表中的team_id字段中，我有sprints_vs_teams。有没有这样做的方法，如果可以的话，你可以提供一些指导，请注意我对C＃很新。任何帮助将不胜感激。

Amended 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 amended my code with @Sick 's answer, however it didn't seem to work. If anyone could advise on where I may have went wrong it would be greatly appreciated.

我用@Sick的答案修改了我的代码，但它似乎没有用。如果有人可以就我可能出错的地方提出建议，我们将不胜感激。

3 个解决方案

#1

Use SCOPE_IDENTITY

使用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)"

#2

If I am not mistaken, you have to declare team_identity as a cmd.parameter. of course there is no param passed into it c# is still expecting one

如果我没弄错，你必须将team_identity声明为cmd.parameter。当然没有param进入它c＃仍然期待一个

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

Or something like that

或类似的东西

#3

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

在将一行插入到team表中之后，编写以下代码以获取identity的当前值并在插入sprints_vs_teams表期间使用它。

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

Hope it helps

希望能帮助到你

#1