Click here to Skip to main content
14,574,744 members
Rate this:
Please Sign up or sign in to vote.
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:)

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

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
Rate this:
Please Sign up or sign in to vote.

Solution 2

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:
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.
   
Comments
SuperTeagz 30-Sep-11 7:49am
   
Thanks that helped..
Wendelius 30-Sep-11 9:58am
   
You're welcome :)
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100