C# SQL Select and then Insert

Question

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 29-Sep-11 15:19pm

SuperTeagz

Updated 29-Sep-11 17:09pm

walterhevedeich

v3

Add a Solution

2 solutions

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.

Posted 29-Sep-11 15:27pm

CodingLover

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2011-09-29T18:42:00

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.