SQL replace two records while cloning a row

Question

I have coded this snippet to clone a selected row in a table:

protected void Button4_Click(object sender, EventArgs e)
{
    DateTime ucode = DateTime.Now;
    string slctedProj = DropDownList1.SelectedItem.ToString();
    string new_code = "PR" + ucode.ToString("ssmmHHddMM");

    string query = @"INSERT INTO Projects (proj_id, proj_prod_id, proj_cust_id, proj_man_id,
                    proj_name, proj_date, proj_num_of_vehicles, proj_coach_vehicle,
                    proj_contract_value, proj_length, proj_width, proj_height, proj_passenger_seats,
                    proj_passenger_total, proj_type, proj_notes, uname, proj_brand, proj_systemvoltage,
                    proj_gauge, proj_service_speed)
                    SELECT proj_id, proj_prod_id, proj_cust_id, proj_man_id,
                    proj_name, proj_date, proj_num_of_vehicles, proj_coach_vehicle,
                    proj_contract_value, proj_length, proj_width, proj_height, proj_passenger_seats,
                    proj_passenger_total, proj_type, proj_notes, uname, proj_brand, proj_systemvoltage,
                    proj_gauge, proj_service_speed
                    FROM Projects WHERE proj_name =" + "'" + slctedProj + "'";
    System.Windows.Forms.MessageBox.Show(query);
    string getconnstring = ConfigurationManager.ConnectionStrings["stad_conn"].ConnectionString;
    SqlConnection conn = new SqlConnection(getconnstring);
    conn.Open();
    SqlCommand cmd = new SqlCommand(query, conn);
    cmd.ExecuteNonQuery();
    conn.Close();
}

the code is working fine but I need to replace in the new row, "proj_id" with the string "new_code" and attach at the beginning of the "proj_name" something like "Cloned". How can I do that?

@SLaks, thanks but that is exactly my problem.I do not know how to change it. I do not use sql often. — FeliceM, Nov 29 '13 at 16:38

score 0 · Accepted Answer · edited May 23 '17 at 12:12

First of all: Use parameters in your SQL command.

Heres the revised code, with parameters. Note that the Add method on the parameters is specifying both the data type as well as the length. It's better to specify these explicitly rather than let ADO.Net assume it knows what you want.

DateTime ucode = DateTime.Now;
string slctedProj = DropDownList1.SelectedItem.ToString();
string new_code = "PR" + ucode.ToString("ssmmHHddMM");
string new_projectName = "Some new name";

string query = @"INSERT INTO Projects (proj_id, proj_prod_id, proj_cust_id, proj_man_id,
                proj_name, proj_date, proj_num_of_vehicles, proj_coach_vehicle,
                proj_contract_value, proj_length, proj_width, proj_height, proj_passenger_seats,
                proj_passenger_total, proj_type, proj_notes, uname, proj_brand, proj_systemvoltage,
                proj_gauge, proj_service_speed)
                SELECT @NewProjectID, proj_prod_id, proj_cust_id, proj_man_id,
                @NewProjectName, proj_date, proj_num_of_vehicles, proj_coach_vehicle,
                proj_contract_value, proj_length, proj_width, proj_height, proj_passenger_seats,
                proj_passenger_total, proj_type, proj_notes, uname, proj_brand, proj_systemvoltage,
                proj_gauge, proj_service_speed
                FROM Projects WHERE proj_name = @SelectedProj";

System.Windows.Forms.MessageBox.Show(query);

string getconnstring = ConfigurationManager.ConnectionStrings["stad_conn"].ConnectionString;
SqlConnection conn = new SqlConnection(getconnstring);
conn.Open();
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@NewProjectID", SqlDbType.VarChar, 20).Value = new_code;
cmd.Parameters.Add("@NewProjectName", SqlDbType.VarChar, 20).Value = new_projectName;
cmd.Parameters.Add("@SelectedProj", SqlDbType.VarChar, 20).Value = slctedProj;

cmd.ExecuteNonQuery();
conn.Close();

Bogdan Sahlean · Answer 2 · 2013-11-29T20:23:27.220

This is not an answer. You could improve this design by keeping a reference to source / initial project using a FK (only for cloned projects) thus:

ALTER TABLE dbo.Projects
ADD source_proj_id INT NULL
REFERENCES dbo.Projects(proj_id);
GO

and you need to change also the insert statement thus:

string query = @"INSERT INTO Projects 
                (proj_id, source_proj_id, proj_prod_id, ...)
                SELECT @NewProjectID, proj_id, proj_prod_id, ...
                FROM Projects WHERE proj_name = @SelectedProj";

SQL replace two records while cloning a row

2 Answers2