0

I have 2 tables:

  1. RESERVATION

    ID, DATE, TIME, TABLE

  2. CLIENT

    ID_CLIENT, FNAME, LNAME, EMAIL, PHONE, FK_RESERVATION

I have working INSERT statement for Reservation Table-

 string insertSql = "INSERT INTO Rezervacija (date,time,table) VALUES (@date,@time,@table);
 SqlCommand cmd = new SqlCommand(insertSql, con);

 cmd.Parameters.AddWithValue("@date", txtDate.Text);
 cmd.Parameters.AddWithValue("@time", ddlTime.SelectedItem.Text);
 cmd.Parameters.AddWithValue("@table", ddlTable.SelectedItem.Text);

But the problem comes with INSERT INTO Client Table Foreign Key.

Can anyone help me how to insert data into two related tables.

Community
  • 1
  • 1
McKeymayker
  • 358
  • 3
  • 12

1 Answers1

2

You'll need to modify your query to get the ID of the row that you just inserted.

string insertSql = "INSERT INTO Rezervacija (date,time,table) OUTPUT INSERTED.Id VALUES (@date,@time,@table);"
 SqlCommand cmd = new SqlCommand(insertSql, con);

 cmd.Parameters.AddWithValue("@date", txtDate.Text);
 cmd.Parameters.AddWithValue("@time", ddlTime.SelectedItem.Text);
 cmd.Parameters.AddWithValue("@table", ddlTable.SelectedItem.Text);

var **reservationId** = (int)cmd.ExecuteScalar()

string insertSql2 = "INSERT INTO CLIENT (ID_CLIENT,FNAME,LNAME,EMAIL,PHONE,FK_RESERVATION) VALUES (@clientId, @fname, @lname, @email, @phone, @reservation"

SqlCommand cmd2 = new SqlCommand(insertSql2, con);
cmd.Parameters.AddWithValue("@clientId", clientId);
cmd.Parameters.AddWithValue("@fname", fname);
cmd.Parameters.AddWithValue("@lname", lname);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@reservation", **reservationId**);

This will allow you use the Inserted.Id in your second query as you've returned the output to a variable.

Bill Hatter
  • 165
  • 5