CODEWITHSUNDEEP
c#asp.netsqlsql-server
user3249809
user3249809

Reputation: 83

Inserting Into a table using Parameters

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!

Upvotes: 0

Views: 94

Answers (1)

D Stanley
D Stanley

Reputation: 152521

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.

Upvotes: 3

Related Questions