Limit GridView edit to only one column

Question

I have a gridview set up like this:

<asp:GridView ID="GridViewUsers" runat="server" 
    DataSourceID="AccessDataSourceUsers"
    AllowSorting="True" AutoGenerateColumns="False" AutoGenerateEditButton="True">
    <Columns>
        <asp:BoundField DataField="username" HeaderText="username" 
            SortExpression="username" />
        <asp:TemplateField HeaderText="role" SortExpression="role">
            <EditItemTemplate>
                <asp:DropDownList ID="DropDownListRoles" runat="server" 
                    DataSourceID="AccessDataSourceRoles" DataTextField="role" DataValueField="role" 
                    SelectedValue='<%# Bind("role") %>'>
                </asp:DropDownList>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Bind("role") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

So the gridview contains a list of users and their roles, upon selecting edit the roles column cells turn into dropdown lists containing roles (currently only Admin and Member).

The issue I'm currently tackling is getting the appropriate values from the gridview (username and selected value from the dropdown list) into the parameters of the update query.

Current codebehind:

protected void AccessDataSourceUsers_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    GridViewRow row = GridViewUsers.Rows[GridViewUsers.EditIndex];
    DropDownList ddl = (DropDownList)row.FindControl("DropDownListRoles");

    AccessDataSourceUsers.UpdateParameters.Add("@role", ddl.SelectedValue);
    AccessDataSourceUsers.UpdateParameters.Add("@username", row.Cells[0].Text);
}

I managed to get the selected value of the dropdownlist just fine, however I can't get the username, instead I only get an empty string. I'm assuming that's because upon clicking edit the username field turns into a textbox.

How could I prevent editing the username column so that the cells don't turn into a textbox (I only wish for the roles column to be editable). And also will that automatically fix the problem of getting the value through row.Cells[0].Text or is there something else I'm missing here?

EDIT: Issue 2. : Parameters and query

My update command looks like this:

UpdateCommand="UPDATE users
 SET users.roleID = DLookup( "[id]" , "roles" , "[role] = '?'")
WHERE ((username = '?'));

Codebehind parameters:

protected void GridViewUsers_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow row = GridViewUsers.Rows[GridViewUsers.EditIndex];
    DropDownList ddl = (DropDownList)row.FindControl("DropDownListRoles");
    Label lbl = (Label)row.FindControl("LabelItemEditUsername");

    if (ddl != null && ddl.SelectedValue != null && lbl != null)
    {
        AccessDataSourceUsers.UpdateParameters.Add("?", System.Data.DbType.String, ddl.SelectedValue); // I've also tried this without specifying the DbType
        AccessDataSourceUsers.UpdateParameters.Add("?", System.Data.DbType.String, lbl.Text);
        int result = AccessDataSourceUsers.Update();
        System.Diagnostics.Debug.WriteLine(AccessDataSourceUsers.UpdateCommand);
        System.Diagnostics.Debug.WriteLine(AccessDataSourceUsers.UpdateParameters[0]);
        System.Diagnostics.Debug.WriteLine(AccessDataSourceUsers.UpdateParameters[1]);
        System.Diagnostics.Debug.WriteLine(result);
    }
    else
    {
        e.Cancel = true;
    }
}

After clicking update on gridview the debug values in output are:

UPDATE users
 SET users.roleID = DLookup( "[id]" , "roles" , "[role] = '?'")
WHERE ((username = '?'));


?
?
0

I'm using ? due to this line in Retrieving Data Using the AccessDataSource Web Server Control : Because the AccessDataSource control extends the SqlDataSource class and uses the System.Data.OleDb provider, you specify parameter placeholders using the "?" placeholder character. The System.Data.OleDb provider does not support named parameters;

What is weird is that there are two empty lines and then the two placeholder names. Isn't AccessDataSourceUsers.UpdateParameters[0] supposed to return the value of the parameter, and AccessDataSourceUsers.UpdateParameters[0].Name return the name? Are the two empty lines supposed to be values? If so why are they empty?

Answer 1

How's about replacing the bound field with another template field? Your markup should look like this: EDIT 2 : Regarding your second question---

1. I would check my UpdateCommand for the extra lines. I can't see the closed quotation in your command. I have this UpdateCommand UpdateCommand="UPDATE users SET users.roleID = DLookup('[id]','roles', '[role]'=?) WHERE username = ?" and don't have any empty line in debug.

2. To see the value, you have to get DefaultValue of the parameter like :AccessDataSourceUsers.UpdateParameters[0].DefaultValue.

EDIT : I don't think you can set the parameters in AccessDatasource_Updating. GridView's RowUpdating is better place to do this.

   <asp:GridView ID="GridViewUsers" runat="server" 
    DataSourceID="AccessDataSourceUsers"
    AllowSorting="True" AutoGenerateColumns="False" AutoGenerateEditButton="True" OnRowUpdating="GridViewUsers_RowUpdating">
    <Columns>        
        <asp:TemplateField HeaderText="username" SortExpression="username">
            <EditItemTemplate>
                 <asp:Label ID="lblUserName" runat="server" Text='<%# Bind("username") %>'></asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="lblUserName" runat="server" Text='<%# Bind("username") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="role" SortExpression="role">
            <EditItemTemplate>
                <asp:DropDownList ID="DropDownListRoles" runat="server" 
                    DataSourceID="AccessDataSourceRoles" DataTextField="role" DataValueField="role" 
                    SelectedValue='<%# Bind("role") %>'>
                </asp:DropDownList>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Bind("role") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
   </asp:GridView>

You can find the label in template field in code:

protected void AccessDataSource_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
  // Moved code to the method below
}

protected void GridViewUsers_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    GridViewRow row = GridViewUsers.Rows[GridViewUsers.EditIndex];
    var ddl = row.FindControl("DropDownListRoles") as DropDownList;
    var lblUserName = row.FindControl("lblUserName") as Label;
    if (ddl != null && ddl.SelectedValue != null && lblUserName != null)
    {
        AccessDataSourceUsers.UpdateParameters.Add("@role", ddl.SelectedValue);
        AccessDataSourceUsers.UpdateParameters.Add("@username", lblUserName.Text);
        AccessDataSourceUsers.Update();
    }
}

I have tested the code above. Hope it helps!

Answer 2

The solution is very simple open the aspx file at design mode and edit the GridView as the following image

Then mark the column that you want to be not editable as read only as following