Click here to Skip to main content
15,900,907 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi can anyone please tell me why this does not work. What im trying to do, is to Read data from a DB then I want to insert that into a different table and and another colum.

public void InsertUniqueGymMemberTrainingProgram(string gmemberID, int daynum)
      {

          using (SqlConnection conn = new SqlConnection(connectionString))
          {

              SqlCommand cmd = new SqlCommand("procGetProg1Day",conn);
              cmd.CommandType = CommandType.StoredProcedure;
              //cmd.CommandText = "procGetProg1Day";
              //cmd.Connection = conn;

              cmd.Parameters.Add(new SqlParameter("@dayNum", SqlDbType.SmallInt));
              cmd.Parameters["@dayNum"].Value = daynum;

              conn.Open();
              SqlDataReader reader = cmd.ExecuteReader();
              while(reader.Read())
              {
                  SqlCommand insCmd = new SqlCommand("procInsertUniqueGymMemberTrainingProgram", conn);
                  insCmd.CommandType = CommandType.StoredProcedure;
                  // insCmd.CommandText = "procInsertGymMemberTrainingProgram";

                  insCmd.Parameters.Add(new SqlParameter("@GymMemberID", SqlDbType.NVarChar,20));
                  insCmd.Parameters["@GymMemberID"].Value = gmemberID;

                  insCmd.Parameters.Add(new SqlParameter("@ExerciseID", SqlDbType.SmallInt));
                  insCmd.Parameters["@ExerciseID"].Value = Convert.ToInt32(reader[2]);

                  insCmd.Parameters.Add(new SqlParameter("@TrainingProgramID", SqlDbType.SmallInt));
                  insCmd.Parameters["@TrainingProgramID"].Value = Convert.ToInt32(reader[1]);

                  insCmd.Parameters.Add(new SqlParameter("@DayNumber", SqlDbType.SmallInt));
                  insCmd.Parameters["@DayNumber"].Value = Convert.ToInt32(reader[0]);

                  conn.Open();
                  insCmd.ExecuteNonQuery();
              }
              reader.Close();
          }
      }


Thanks in advance:)

SQL
ALTER PROCEDURE [dbo].[procGetProg1Day]
@dayNum smallint
AS
SELECT DISTINCT TrainingProgramExercise.DayNumber, TrainingProgramExercise.TrainingProgramID,TrainingProgramExercise.ExerciseID
FROM Exercise
INNER JOIN TrainingProgramExercise
ON Exercise.ExerciseID = TrainingProgramExercise.ExerciseID
WHERE TrainingProgramExercise.DayNumber = @dayNum
AND TrainingProgramExercise.TrainingProgramID = 1



AND 2nd

SQL
ALTER PROCEDURE [dbo].[procInsertUniqueGymMemberTrainingProgram]
@GymMemberID nvarchar(20),
@ExerciseID smallint,
@TrainingProgramID smallint,
@DayNumber smallint
AS
INSERT INTO GymMemberTrainingProgram(GymMemberID, ExerciseID,TrainingProgramID,DayNumber)
VALUES(@GymMemberID,@ExerciseID,@TrainingProgramID,@DayNumber)
Posted
Updated 29-Sep-11 17:09pm
v3

What problem are you encountering? Are you getting an exception? If you debug through the code are you getting rows from the first procedure etc...

Another way you could do this is to execute the whole thing in a single statement using a statement like:
SQL
INSERT INTO GymMemberTrainingProgram(
   GymMemberID, 
   ExerciseID,
   TrainingProgramID,
   DayNumber)
SELECT DISTINCT
       @GymMemberID, 
       TrainingProgramExercise.ExerciseID,
       TrainingProgramExercise.TrainingProgramID,
       TrainingProgramExercise.DayNumber
FROM Exercise
INNER JOIN TrainingProgramExercise
ON Exercise.ExerciseID = TrainingProgramExercise.ExerciseID
WHERE TrainingProgramExercise.DayNumber = @dayNum
AND TrainingProgramExercise.TrainingProgramID = 1

If you set up the parameters correctly and execute the above statement you shouldn't need to loop through all the records at client side, which would be much more efficient.
 
Share this answer
 
Comments
SuperTeagz 30-Sep-11 7:49am    
Thanks that helped..
Wendelius 30-Sep-11 9:58am    
You're welcome :)
Don't open the SQL connection multiple times. I can see that you have open the connection within the while loop as well as before enter to it.
 
Share this answer
 
Comments
SuperTeagz 29-Sep-11 21:37pm    
Thanks for the comment.but with or without it still doesnt work
CodingLover 29-Sep-11 21:51pm    
Yes, it's not an issue. But not safe. Open the connection only one, and use the same.

Anyway, you should check the data insertion through a query first of all. I cannot see a direct error with your code segment above.
SuperTeagz 29-Sep-11 21:37pm    
the 2nd conn.open()
devildx2050 29-Sep-11 22:42pm    
you are trying to open an already open connection.
CodingLover 1-Oct-11 2:54am    
Which is not safe to do.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900