Editable GridView check if the same ID already has a checked checkbox

Question

I have an editable GridView for Desktop machines, with people, their PC names and if those are their primary PCs. It looks something like this.

MachineId	PersonId	PC	IsPrmary
1	1	Machine-123	true
2	2	Machine-567	true
3	1	Machine-101	false

As of now, on the Editable Grid, I can edit MachineId="3" and set isPrimary="true" which would mean that PersonId="1" now has two primary machines, which should not happen.

The editable gridview can only edit one row at a time, so I was wondering how would I check if the MachineId I am editing has a PersonId who already has IsPrimary="checked".

This is the code behind I am using for add, which is connected to a simple Save store procedure that basically just gets the editable values and inserts them using e.NewValues:

protected void gvMachines_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    int index = gvMachines.EditIndex;
    GridViewRow row = gvMachines.Rows[index];

    Label lblMachineId = (Label)row.FindControl("lblMachineId");
    DropDownList ddlPersonId = (DropDownList)row.FindControl("ddlPersonId");
    Label lblPCName = (Label)row.FindControl("lblPCName");
    CheckBox cbIsPrimary = (CheckBox)row.FindControl("cbIsPrimary");

    e.NewValues["Id"] = lblMachineId.Text;
    e.NewValues["PersonId"] = ddlPersonId.SelectedValue;
    e.NewValues["PCName"] = lblPCName.Text;
    e.NewValues["cbIsPrimary"] = cbIsPrimary.Checked;
}

I am using the following Save procedure:

 IF EXISTS (SELECT * FROM tblMachines WHERE [Id] = @Id AND @Id IS NOT NULL)
    BEGIN 
        UPDATE tblMachines
        SET 
        [PersonId] = @PersonId,
        [PCName] = @PCName,
        [IsPrimary] = @IsPrimary,
         WHERE ([Id] = @Id AND @Id IS NOT NULL) 
    END 
    ELSE 
    BEGIN 
        INSERT INTO tblMachines
        ( [PersonId],
         [PCName],
         [IsPrimary]
        )       VALUES 
        ( @PersonId,
         @PCName,
         @IsPrimary
         )
     END 
 END

I created an if (cbIsPrimary.Checked) {} that displays a warning telling the user to check if the person already has a primary machine allocated, but this is not optimal cause we might have 10 000 people with machines.

Any help and insights are appreciated!

Answer 1

Well, you can change/fix the store procedure to check for this, and not allow the update.

Or maybe you create a trigger that when you set one to true, it sets all others = false.

but, if you want to check this at the UI level BEFORE all that behind stuff occurs?

I would consider just coding out the condition, and checking - that way you can give the user some nice friendly feedback.

so, code in that update could do say this (air code)

            string strSQL = "SELECT MachineID FROM tblMachines  where MachineID <> @ID" +
                                     "AND PersonID = @PersonID AND IsPrimary = 1";

            using (SqlCommand cmdSQL = new SqlCommand(strSQL, 
                new SqlConnection(Properties.Settings.Default.TEST4)))

            {
                cmdSQL.Parameters.Add("@MachineID", SqlDbType.Int).Value = "the machine ID";
                cmdSQL.Parameters.Add("@PersonID", SqlDbType.Int).Value = "the person id";

                cmdSQL.Connection.Open();
                string  PK = Convert.ToString(cmdSQL.ExecuteScalar());

                if (PK == "")
                {
                    Response.Write("<h2>ok, we can do this - no rows returned</h2>");
                }
                else
                {
                   Response.Write("<h2>Already a machine set as  primary for this user-</h2>");
                }
            }

So even if you setup a stored procedure to not allow this, you near in all cases will want to check/test/inform the user in actual use of such rules. So you can setup the rule to not allow this, but my bets suggest that you still need to test/check/try and take actions - actions that will give some type of feedback on this not being allowed. (like say maybe a way cool looking jQuery.UI dialog box).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada