protected void btnSave_Click(object sender, EventArgs e) { List <FocusGroup> focusGroupList = new List <FocusGroup>(); for (int i = 0; i < gvPublication.Rows.Count; i++) { FocusGroup fc = new FocusGroup(); RadioButtonList rbl = (RadioButtonList)gvPublication.Rows[i].FindControl("rblFocusGroup"); if (rbl == null) { return; } if (rbl.SelectedIndex != -1) { fc.focusGroupId = Convert.ToInt32(rbl.SelectedValue.ToString()); ErrorMessage.Text = ""; int id; Label lblIdTemp = null; lblIdTemp = (Label)gvPublication.Rows[i].FindControl("lblId"); if (lblIdTemp != null) { id = Convert.ToInt32(lblIdTemp.Text); } else { return; } fc.pubProgId = id; focusGroupList.Add(fc); } } foreach (FocusGroup fc in focusGroupList) { SaveFocusGroupSelection(fc); } FillPublicationGrid(); string msg = "Saved."; ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + msg + "');", true); }
protected void SaveFocusGroupSelection(FocusGroup fc) { int publicationProgramId = fc.pubProgId; int focusGroupId = fc.focusGroupId; string connectionStr = ConfigurationManager.ConnectionStrings["UcccPubMedDB"].ConnectionString; SqlConnection myConnection = new SqlConnection(connectionStr); string sqlStatement; if (focusGroupId == -1) //review/editorial { /* * sqlStatement = * "IF NOT EXISTS(SELECT pmid FROM reject_publication WHERE pmid = (select pmid from publication p inner join publication_program pp on p.publication_id = pp.publication_id" + * " and pp.publication_program_id = @publication_program_id))" + * " insert into reject_publication (pmid) values (" + * "(select pmid from publication p inner join publication_program pp on p.publication_id = pp.publication_id" + * " and pp.publication_program_id = @publication_program_id))"; * */ sqlStatement = "update publication_processing set review_editorial = 1" + " where publication_id =" + " (select publication_id from publication_program where publication_program_id = @publication_program_id)"; SqlCommand commandInsert = new SqlCommand(sqlStatement, myConnection); SqlParameter publication_program_idParameterInsert = new SqlParameter(); publication_program_idParameterInsert.ParameterName = "@publication_program_id"; publication_program_idParameterInsert.SqlDbType = SqlDbType.Int; publication_program_idParameterInsert.Value = publicationProgramId; commandInsert.Parameters.Add(publication_program_idParameterInsert); myConnection.Open(); commandInsert.ExecuteNonQuery(); myConnection.Close(); sqlStatement = "update publication_program set l_focus_group_id = null" + " where publication_program_id = @publication_program_id"; SqlCommand command = new SqlCommand(sqlStatement, myConnection); SqlParameter publication_program_idParameter = new SqlParameter(); publication_program_idParameter.ParameterName = "@publication_program_id"; publication_program_idParameter.SqlDbType = SqlDbType.Int; publication_program_idParameter.Value = publicationProgramId; command.Parameters.Add(publication_program_idParameter); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); } /* * else if (focusGroupId == -2) //not in the program, to be removed * { * sqlStatement = * "delete from publication_program" + * " WHERE (publication_program_id = @publication_program_id)"; * * SqlCommand command = new SqlCommand(sqlStatement, myConnection); * SqlParameter publication_program_idParameter = new SqlParameter(); * publication_program_idParameter.ParameterName = "@publication_program_id"; * publication_program_idParameter.SqlDbType = SqlDbType.Int; * publication_program_idParameter.Value = publicationProgramId; * command.Parameters.Add(publication_program_idParameter); * * myConnection.Open(); * command.ExecuteNonQuery(); * myConnection.Close(); * } * */ else if (focusGroupId == -2) //unknown, clear { sqlStatement = "update publication_processing set review_editorial = null" + " where publication_id =" + " (select publication_id from publication_program where publication_program_id = @publication_program_id)"; SqlCommand commandInsert = new SqlCommand(sqlStatement, myConnection); SqlParameter publication_program_idParameterInsert = new SqlParameter(); publication_program_idParameterInsert.ParameterName = "@publication_program_id"; publication_program_idParameterInsert.SqlDbType = SqlDbType.Int; publication_program_idParameterInsert.Value = publicationProgramId; commandInsert.Parameters.Add(publication_program_idParameterInsert); myConnection.Open(); commandInsert.ExecuteNonQuery(); myConnection.Close(); sqlStatement = "update publication_program set l_focus_group_id = null" + " where publication_program_id = @publication_program_id"; SqlCommand command = new SqlCommand(sqlStatement, myConnection); SqlParameter publication_program_idParameter = new SqlParameter(); publication_program_idParameter.ParameterName = "@publication_program_id"; publication_program_idParameter.SqlDbType = SqlDbType.Int; publication_program_idParameter.Value = publicationProgramId; command.Parameters.Add(publication_program_idParameter); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); } else //some focus group is selected { sqlStatement = "Update publication_program" + " SET l_focus_group_id=@l_focus_group_id" + " WHERE (publication_program_id = @publication_program_id)"; SqlCommand command = new SqlCommand(sqlStatement, myConnection); SqlParameter l_focus_group_idParameter = new SqlParameter(); l_focus_group_idParameter.ParameterName = "@l_focus_group_id"; l_focus_group_idParameter.SqlDbType = SqlDbType.Int; if (focusGroupId != 0) { l_focus_group_idParameter.Value = focusGroupId; } else { l_focus_group_idParameter.Value = DBNull.Value; } command.Parameters.Add(l_focus_group_idParameter); SqlParameter publication_program_idParameter = new SqlParameter(); publication_program_idParameter.ParameterName = "@publication_program_id"; publication_program_idParameter.SqlDbType = SqlDbType.Int; publication_program_idParameter.Value = publicationProgramId; command.Parameters.Add(publication_program_idParameter); myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); sqlStatement = "update publication_processing set review_editorial = null" + " where publication_id =" + " (select publication_id from publication_program where publication_program_id = @publication_program_id)"; SqlCommand command2 = new SqlCommand(sqlStatement, myConnection); SqlParameter publication_program_idParameter2 = new SqlParameter(); publication_program_idParameter2.ParameterName = "@publication_program_id"; publication_program_idParameter2.SqlDbType = SqlDbType.Int; publication_program_idParameter2.Value = publicationProgramId; command2.Parameters.Add(publication_program_idParameter2); myConnection.Open(); command2.ExecuteNonQuery(); myConnection.Close(); } gvPublication.EditIndex = -1; //FillPublicationGrid(); }