Insert into two tables with one table having an auto increment primary

yasser alim

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();

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhishek Chadha4y ago
you can try to use the select max inside your insert query

use this select max(idstation) from station where you are putting the column name

----

if this not works then first insert the first table then insert into 2nd table using transactions

normally the second method is preferable with using the transactions
Sachin Singh4y ago
Although, i will suggest creating stored procedure, inline queries are neither well readable nor maintable,
but at EOD, your query will be something like below
"Insert into Stations (CityID," +
"StartDate,Space,Owner)" +
"values (@CityID,@StartDate," +
"@Space,@Owner)" + "
Declare @Sid INT;
SET @Sid=@ScopeIdentity();
"
"Insert Contract (IDStation,ContractStart,ContractEnd) values (@Sid,@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("@ContractStart", txtConStart.Value);
cmd.Parameters.AddWithValue("@ContractEnd", txtConEnd.Value);
cmd.ExecuteNonQuery();