1

I have 2 tables Station and Contract. Contract have a foreign key relationship with Station table IDStation auto increment . How i get IDStation number at same time i do insert statement.

cmd.CommandText = "Insert into Stations (CityID," +
                    "StartDate,Space,Owner)" +
                    "values (@CityID,@StartDate," +
                    "@Space,@Owner)" +
                    "Insert Contract (IDStation,ContractStart,ContractEnd) values (@IDStation,@ContractStart,@ContractEnd) ";

                cmd.Parameters.AddWithValue("@CityID", drpCity.Value);           
                cmd.Parameters.AddWithValue("@StartDate", txtStartDate.Value);               
                cmd.Parameters.AddWithValue("@Space", txtSpace.Text);     
                cmd.Parameters.AddWithValue("@Owner", txtOwner.Text);               
               // cmd.Parameters.AddWithValue("@IDStation",drp );
                cmd.Parameters.AddWithValue("@ContractStart", txtConStart.Value);
                cmd.Parameters.AddWithValue("@ContractEnd", txtConEnd.Value);
               
                cmd.ExecuteNonQuery();
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Yasseralim
  • 25
  • 1
  • I think, in that case, you will have to insert the station, get the ID of the inserted station, and then do your second insertion. – Clement Jun 15 '22 at 07:38
  • 1
    Checkout the `@@IDENTITY` in T-SQL https://learn.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql?view=sql-server-ver16 – cleftheris Jun 15 '22 at 08:02
  • 3
    `SCOPE_IDENTITY()` is a better choice than `@@IDENTITY` – Nick.Mc Jun 15 '22 at 08:10
  • Does this answer your question? [Trying to get SELECT SCOPE\_IDENTITY() as c# variable](https://stackoverflow.com/questions/39111849/trying-to-get-select-scope-identity-as-c-sharp-variable) – Nick.Mc Jun 15 '22 at 08:11
  • Does this answer your question? [Insert record in two tables with identity field value from first table](https://stackoverflow.com/questions/35941607/insert-record-in-two-tables-with-identity-field-value-from-first-table) – Charlieface Jun 15 '22 at 11:59
  • Using [OUTPUT](https://www.brentozar.com/archive/2020/11/how-to-insert-rows-and-get-their-identity-values-with-the-output-clause/) is even better than @@IDENTITY and SCOPE_IDENTITY() – Crowcoder Jun 15 '22 at 12:06

0 Answers0