How to copy data from a table into the same table

Question

0.00/5 (No votes)

See more:

, +

Hey guys so I am creating an Event and what I want to do is copy over the attendees from last years event.

This is the code I currently have:

protected void Button3_Click(object sender, EventArgs e)
    {
        string constring = ConfigurationManager.AppSettings["Award_Management2ConnectionString1"].ToString();
        SqlConnection con = new SqlConnection(constring);
        con.Open();

        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            CheckBox check = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("checkAction");

            if (check.Checked)
            {
string peopleID = GridView1.Rows[i].Cells[4].Text;
string eventID = GridView1.Rows[i].Cells[5].Text;
string DropboxEventID = DropDownList2.SelectedItem.Value.ToString();
string Usercommand = "INSERT INTO People_Event (People_Event.Confirmation,People_Event.People_ID, People_Event.Event_ID) (SELECT People_Event.Confirmation,People_Event.People_ID, '" + DropboxEventID + "' FROM People_Event WHERE (People_Event.People_ID = '" + peopleID + "' AND People_Event.Event_ID = '" + eventID + "')";
SqlCommand command = new SqlCommand(Usercommand, con);
command.ExecuteNonQuery();
            }
        }
        Response.Write("Successfully Copied and Added!!");
        con.Close();
        GridView1.DataBind();
        GridView2.DataBind();
        
    }

However this moves (not copies) the people from one event to the other, so when I go to view a previous event the people who attended are no longer there.

Can someone please help me here.. I want to be able to copy over attendees from previous years and the only thing I want to update is the EventID to equal to new EventID.

Any help here will be greatly appreciated.

Thank You

Posted 2-Sep-13 1:40am

Aamir Mitha

Updated 2-Sep-13 22:33pm

v5

Add a Solution

Comments

Sadique KT 2-Sep-13 8:03am

You are given just insert command... where is the remaining code?

Aamir Mitha 2-Sep-13 20:37pm

Hey please view the code above .. I have updated it a little but i still get the same problem.. could you please update my sql query to 'copy' instead of 'move'?

Thanks7872 2-Sep-13 8:35am

As suggested in below solution,insert always creates new record,it won't copy the data. What you can do in this scenario is,get the data what you want to copy and combine it with the new data (EventID),insert it into the table.

1 solution

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)

hypermellow · Answer 1 · 2013-09-02T01:46:00

Solution 1

Hi,

C#

string Usercommand = "INSERT INTO People_Event (People_Event.Confirmation, People_Event.People_ID, People_Event.Event_ID) VALUES ('no', '" + peopleID + "', '" + DropboxEventID + "')";

This statement does not move people from 1 event to another - an INSERT statement will add records, not update existing records.

Have you checked the People_Event table directly? ... rather than viewing the data via the front end application?

Posted 2-Sep-13 1:46am

hypermellow

Comments

Aamir Mitha 2-Sep-13 20:38pm

Hey please view the code above .. I have updated it a little but i still get the same problem.. could you please update my sql query to 'copy' instead of 'move'?

and yes I have checked the People_Event table directly and it also shows the same problem.. could you please help me here ?

idenizeni 2-Sep-13 23:54pm

This should work for you, there is no way this INSERT statement is doing the same thing as your UPDATE statement. You are over complicating this. Don't think about it as a record copy, instead think of it as a record insert, only instead of inserting a completely new record you are inserting a new record based off of an existing record.

Aamir Mitha 3-Sep-13 4:09am

Thank you for your comment , so I made a new one (please see updated code)... however it still only moves entire rows over and gives them a new people_eventID .. it does not copy the rows over with the people_eventID of the event hosted in the year shown in the dropbox.

Aamir Mitha 3-Sep-13 4:35am

Hey Sorry I updated my code again but I get this error now : Incorrect syntax near ')'. ?? My code seems to be right why am i getting this error ?

idenizeni 3-Sep-13 13:50pm

You have an extra open bracket before your SELECT clause. Try this...

string Usercommand = "INSERT INTO People_Event (People_Event.Confirmation,People_Event.People_ID, People_Event.Event_ID) SELECT People_Event.Confirmation,People_Event.People_ID, '" + DropboxEventID + "' FROM People_Event WHERE (People_Event.People_ID = '" + peopleID + "' AND People_Event.Event_ID = '" + eventID + "')";

Also, the code above is going to set the Confirmation field to whatever the original record was, did you want that or did you want new records to be 'no'? If you want new records to be 'no' then you may want this...

string Usercommand = "INSERT INTO People_Event (People_Event.Confirmation,People_Event.People_ID, People_Event.Event_ID) SELECT 'no',People_Event.People_ID, '" + DropboxEventID + "' FROM People_Event WHERE (People_Event.People_ID = '" + peopleID + "' AND People_Event.Event_ID = '" + eventID + "')";