Asp.net Getting error while saving username to string based column in datatable

Question

Scenerio

I've a role rights page in my web application. In this page I've a dropdown and a gridview when I select any role from a dropdown its selected index event was run and get the below datatable from SQL Server.

Store Procedure

Below is the store procedure if Role Rights not assign before then ELSE condition runs and from there I'll get null values in column Prepared_By_ID

-- SP_SETUP_ROLES_RIGHTS_GET_DATA 2
ALTER PROCEDURE [dbo].[SP_SETUP_ROLES_RIGHTS_GET_DATA]
@Role_ID bigint
AS
BEGIN
IF EXISTS(Select ROLE_ID from Role_Rights WHERE ROLE_ID = @Role_ID)
BEGIN
    select
    RR.Role_ID,
    RR.Form_ID,
    FR.Form_Name,
    RR.Can_View,
    RR.Can_Edit,
    RR.Can_Prepare_By,
    RR.Active,
    RR.Prepared_By_ID,
    RR.Prepared_Date
    from 
    dbo.Forms FR
    INNER JOIN dbo.Role_Rights RR
    ON RR.Form_ID = FR.ID

    WHERE RR.Role_ID = @Role_ID
    AND FR.Active = 1

    UNION 

    SELECT 
    @Role_ID AS Role_ID,
    FR.ID as Form_ID,
    FR.Form_Name,
    0 as Can_View,
    0 as Can_Edit,
    0 as Can_Prepare_By,
    0 as Active,
    NULL as Prepared_By_ID,
    NULL as Prepared_Date
    FROM 
    dbo.Forms FR
    where FR.ID not in (select Form_ID from dbo.role_rights)

END
ELSE
BEGIN
    SELECT
    @Role_ID AS Role_ID,
    FR.ID as Form_ID,
    FR.Form_Name,
    0 as Can_View,
    0 as Can_Edit,
    0 as Can_Prepare_By,
    0 as Active,
    NULL as Prepared_By_ID,
    NULL as Prepared_Date
    FROM 
    Forms FR
END

END

Fig: 1

I bind the above table to Gridview and store in session. So user easily assign rights (View/Edit/etc) of specific form or multiple forms according to selected role id. Which is working fine.

Problem

When user edit rights and press save button below code run and in below code I put loop on Gridview Rows so each row which has been edited will save in datatable.

Code:

var dt = (DataTable)Session["RoleRights"];
        foreach (GridViewRow row in this.gvRoleRights.Rows)
        {
            roleID = Convert.ToInt32(row.Cells[0].Text);
            FormID = Convert.ToInt32(gvRoleRights.DataKeys[row.RowIndex]["Form_ID"]);
            dt.Rows[row.DataItemIndex]["Role_ID"] = roleID;
            dt.Rows[row.DataItemIndex]["Form_ID"] = FormID;
            dt.Rows[row.DataItemIndex]["Can_View"] = ((CheckBox)row.FindControl("chkView")).Checked;
            dt.Rows[row.DataItemIndex]["Can_Edit"] = ((CheckBox)row.FindControl("chkEdit")).Checked;
            dt.Rows[row.DataItemIndex]["Can_Prepare_By"] = ((CheckBox)row.FindControl("chkPrepare")).Checked;
            dt.Rows[row.DataItemIndex]["Active"] = ((CheckBox)row.FindControl("chkActive")).Checked;
            dt.Rows[row.DataItemIndex]["Prepared_By_ID"] = Context.User.Identity.Name;
        }
        this.RRBO = new RoleRightsBO
        {
            SaveandUpdate = dt,
            Modified_By_ID = Context.User.Identity.Name
        };
        dt.Dispose();
        Result rst = this.RRBL.SaveandUpdate(this.RRBO);

The problem which I facing was in line this dt.Rows[row.DataItemIndex]["Prepared_By_ID"] = Context.User.Identity.Name; when loop is on column Prepared_By_ID and trying to save username of current login user it shows me below error

According to above error I'm trying to save string value to integer type column but as you can see in Fig 1 Datatable Prepared By ID column was null and in my information integer column not set to be null. So Why I'm getting this error. Also I have below code in my aspx page so Its easy for you developer to identify my mistake.

ASPX Gridview Code

<asp:GridView
    ID="gvRoleRights"
    runat="server"
    ShowHeaderWhenEmpty="true"
    AutoGenerateColumns="false"
    EmptyDataText="No Data Found!"
    ShowFooter="False"
    BorderStyle="None"
    CellPadding="3"
    GridLines="Horizontal"
    DataKeyNames="Form_ID"
    SkinID="AHGridView">
    <Columns>
        <asp:BoundField DataField="Role_ID" HeaderText="Role ID" />
        <asp:BoundField DataField="Form_Name" HeaderText="Form Name" />
        <asp:TemplateField HeaderText="View">
             <ItemTemplate>
                  <asp:CheckBox ID="chkView" runat="server" Checked='<%# Convert.ToBoolean(Eval("Can_View")) %>' />
             </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Edit">
             <ItemTemplate>
                  <asp:CheckBox ID="chkEdit" runat="server" Checked='<%# Convert.ToBoolean(Eval("Can_Edit")) %>' />
             </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Prepare">
             <ItemTemplate>
                  <asp:CheckBox ID="chkPrepare" runat="server" Checked='<%# Convert.ToBoolean(Eval("Can_Prepare_By")) %>' />
             </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Active">
             <ItemTemplate>
                  <asp:CheckBox ID="chkActive" runat="server" Checked='<%# Convert.ToBoolean(Eval("Active")) %>' />
             </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Prepared_By_ID" HeaderText="Prepared By ID" />
        <asp:BoundField DataField="Prepared_Date" HeaderText="Prepared By Date" DataFormatString="{0:dd/MM/yyyy}" />
    </Columns>
</asp:GridView>

are you sure the datatype in the database for that bound field is a String.. also what you see in the datagrid for blanks could have NULL in the actual column value in the database.. so you will need to check for if the Prepared_By_ID is null or empty using this string.IsNullOrEmpty function on that value you are checking — MethodMan
Ok. I modified my question kindly check it now. I'm trying to save username in datatable column not in gridview column. — Ahmer Ali Ahsan
@MethodMan kindly check my store procedure in which I'm getting Prepared_By_ID column null when If data against specific role ID is not in Role Rights table. — Ahmer Ali Ahsan
can you run that query outside of your appliation? if so is the column empty or does it have [Null] in the column.. that's what I am pointing you towards.. also there is a difference between NULL and Empty when doing comparison of values.. if the value is NULL.. you cannot compare NULL to a string.. you can however check if the Length of that column value is > 0 to get around the Exception. are you familiar with Ternary and how to setup inline if else checks..? — MethodMan

Ahmer Ali Ahsan Ahmer Ali Ahsan · Accepted Answer · 2017-11-20T20:58:25

As @methodMan identify the issue is in my store procedure. I modify my store procedure and my error was gone.

ALTER PROCEDURE [dbo].[SP_SETUP_ROLES_RIGHTS_GET_DATA]
@Role_ID bigint
AS
BEGIN
IF EXISTS(Select ROLE_ID from Role_Rights WHERE ROLE_ID = @Role_ID)
BEGIN
    select
    RR.Role_ID,
    RR.Form_ID,
    FR.Form_Name,
    RR.Can_View,
    RR.Can_Edit,
    RR.Can_Prepare_By,
    RR.Active,
    RR.Prepared_By_ID,
    RR.Prepared_Date
    from 
    dbo.Forms FR
    INNER JOIN dbo.Role_Rights RR
    ON RR.Form_ID = FR.ID

    WHERE RR.Role_ID = @Role_ID
    AND FR.Active = 1

    UNION 

    SELECT 
    @Role_ID AS Role_ID,
    FR.ID as Form_ID,
    FR.Form_Name,
    0 as Can_View,
    0 as Can_Edit,
    0 as Can_Prepare_By,
    0 as Active,
    '' as Prepared_By_ID,
    GetDate() as Prepared_Date
    FROM 
    dbo.Forms FR
    where FR.ID not in (select Form_ID from dbo.role_rights)

END
ELSE
BEGIN
    SELECT
    @Role_ID AS Role_ID,
    FR.ID as Form_ID,
    FR.Form_Name,
    0 as Can_View,
    0 as Can_Edit,
    0 as Can_Prepare_By,
    0 as Active,
    '' as Prepared_By_ID,
    GetDate() as Prepared_Date
    FROM 
    Forms FR
END

END

Asp.net Getting error while saving username to string based column in datatable

1 Answers