Inserting Into a table using Parameters

Question

My problem is I am trying to insert into a table by using 2 where statements in my Sql Code.

My code:

using (SqlConnection conn = new SqlConnection(@"Connection String"))
{
   conn.Open();

   using (var cmd = new SqlCommand(@"INSERT INTO AssignPlan
         (Reps, Sets, WeightOrTime, Date, MemberId, ExerciseId) 
         Select @Reps, @Sets, @WeightOrTime, @Date, 
         Members.MemberId From Members Where Members.Username = @Username, 
         ExerciseDisplay.ExerciseId From ExerciseDisplay 
         Where ExerciseDisplay.ExerciseName = @Exercise", conn))
   {
      cmd.Parameters.AddWithValue("@Reps", txtReps.Text);
      cmd.Parameters.AddWithValue("@Sets", txtSets.Text);
      cmd.Parameters.AddWithValue("@WeightOrTime", txtWeight.Text);
      cmd.Parameters.AddWithValue("@Date", txtDate.Text);
      cmd.Parameters.AddWithValue("@Username", lblRegistered.Text);
      cmd.Parameters.AddWithValue("@Exercise", txtName.Text);

      cmd.ExecuteNonQuery();

      Response.Redirect("Success.aspx");
   }

   conn.Close();

Any ideas on how to rephrase my SQL statement? Any help would be greatly appreciated!

Easiest solution is to [create a unique constraint](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) on the columns that should be distinct. — user2989408, Jan 30 '14 at 17:55
You should explain what is the relation between users table and ExerciseDisplay table. As is this query is not a valid SQL — Steve, Jan 30 '14 at 17:58
You can't have two from statements, you must join the Members table to the ExerciseDisplay table on a common field and then only use one Where statement. — Eric Scherrer, Jan 30 '14 at 17:59
The query is completely unclear to me. Are you trying to insert into two tables in one operation, or are you trying to insert into one table the parameter values as well as the results of a join between two other tables? — Aaron Bertrand, Jan 30 '14 at 18:00
Although I appreciate the enthusiasm on `using` statements and parametarized queries... I feel it is a sad day when this gets you 3 upvotes ;) That being said, you need to join the tables. This query will not work as is. — Evan L, Jan 30 '14 at 18:01
Sorry guys I probably was a bit vague but the below code works. Thanks for your help! — user3249809, Jan 30 '14 at 18:32

D Stanley · Accepted Answer · 2014-01-30T18:58:33.287

Assuming that there's no relationship between Members and ExerciseDisplay you could do a cross-join and filter the results:

using (var cmd = new SqlCommand(
    " INSERT INTO AssignPlan " + 
    " (Reps, Sets, WeightOrTime, Date, MemberId, ExerciseId)  " + 
    " Select  " + 
    " @Reps, @Sets, @WeightOrTime, @Date, Members.MemberId, ExerciseDisplay.ExerciseId " + 
    " From Members, ExerciseDisplay  " + 
    " Where ExerciseDisplay.ExerciseName = @Exercise " + 
    " AND Members.Username = @Username ", conn))

or, since you're just pulling one value from each table, a subquery should work as well:

using (var cmd = new SqlCommand(
    " INSERT INTO AssignPlan " + 
    " (Reps, Sets, WeightOrTime, Date, MemberId, ExerciseId)  " + 
    " Select  " + 
    " @Reps, @Sets, @WeightOrTime, @Date, " +
    " (SELECT MemberId FROM Members WHERE Username = @Username), " +
    " (SELECT ExerciseId FROM ExerciseDisplay WHERE ExerciseName = @Exercise) "  
    , conn))

But that requires that each subquery only returns one value.

I would be interested to know if the presence of the WHERE could help to reduce the effects of the Cartesian product of the two tables. — Steve, Jan 30 '14 at 18:05

Inserting Into a table using Parameters

1 Answers1