Trying To Make Update From Database GridView Specific User (OleDbCommand)

Question

Hey guys i am working about users GridView Database Upade, And i have a problem with my Update Row, Its telling me when i click on update, The Row is updated Succes but its not really updating it :(

Codes:

1 Asp.net(OleDB)

 protected void AdminBook_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    try
    {
        using (OleDbConnection sqlCon = new OleDbConnection(connectionStr))
        {
            sqlCon.Open();
            string query = "UPDATE Users SET FirstName=@FirstName,LastName=@LastName,UserPass=@UserPass,Gender=@Gender,Birthday=@Birthday,Email=@Email WHERE UserID=@id";
            OleDbCommand sqlCmd = new OleDbCommand(query, sqlCon);
            sqlCmd.Parameters.AddWithValue("@FirstName", (AdminBook.Rows[e.RowIndex].FindControl("txtFirstName") as TextBox).Text.Trim());
            sqlCmd.Parameters.AddWithValue("@LastName", (AdminBook.Rows[e.RowIndex].FindControl("txtLastName") as TextBox).Text.Trim());
            sqlCmd.Parameters.AddWithValue("@UserPass", (AdminBook.Rows[e.RowIndex].FindControl("txtUserPass") as TextBox).Text.Trim());
            sqlCmd.Parameters.AddWithValue("@Gender", (AdminBook.Rows[e.RowIndex].FindControl("txtGender") as TextBox).Text.Trim());
            sqlCmd.Parameters.AddWithValue("@Birthday", (AdminBook.Rows[e.RowIndex].FindControl("txtBirthday") as TextBox).Text.Trim());
            sqlCmd.Parameters.AddWithValue("@Email", (AdminBook.Rows[e.RowIndex].FindControl("txtEmail") as TextBox).Text.Trim());
            sqlCmd.Parameters.AddWithValue("@UserName", (AdminBook.Rows[e.RowIndex].FindControl("txtUserName") as TextBox).Text.Trim());
            sqlCmd.Parameters.AddWithValue("@id", Convert.ToInt32(AdminBook.DataKeys[e.RowIndex].Value.ToString()));
            sqlCmd.ExecuteNonQuery();
            AdminBook.EditIndex = -1;
            PopulateGridView();
            lblSuccessMessage.Text = "עריכת הנתונים התבצעה בהצלחה";
            lblErrorMessage.Text = "";
        }
    }
    catch (Exception ex)
    {
        lblSuccessMessage.Text = "";
        lblErrorMessage.Text = ex.Message;
    }
}

2 HTML (Very Short)

    <asp:GridView ID="AdminBook" runat="server" AutoGenerateColumns="False" 
      ShowFooter="True" DataKeyNames="UserID"
            ShowHeaderWhenEmpty="True"

            OnRowCommand="AdminBook_RowCommand" OnRowEditing="AdminBook_RowEditing" OnRowCancelingEdit="AdminBook_RowCancelingEdit"
            OnRowUpdating="AdminBook_RowUpdating" OnRowDeleting="AdminBook_RowDeleting"

            BackColor="White" BorderColor="#999999" BorderStyle="Solid" 
      BorderWidth="1px" CellPadding="3" ForeColor="Black" GridLines="Vertical">

Picture that you can see it: Here you can see that it show the success message

Please help me... Because its making it succes but not really updating in database :(

Answer 1

The main problem of your query is that the presence of this parameter definition:

sqlCmd.Parameters.AddWithValue("@UserName", (AdminBook.Rows[e.RowIndex].FindControl("txtUserName") as TextBox).Text.Trim());

which doesn't exist in the query string, which has 7 parameters instead of 8 defined by cmd.Parameters.Add():

UPDATE Users SET FirstName=@FirstName,LastName=@LastName,UserPass=@UserPass,Gender=@Gender,Birthday=@Birthday,Email=@Email WHERE UserID=@id

Note that OLE DB doesn't recognize named parameters, it only recognizes positional parameters (query parameters are processed in their definition order instead). With current order in your code, the UserName parameter may be wrongly assigned as id and since provided value does not match with any values stored in UserID column, then no data is updated.

Hence, you should remove mentioned line above so that the query parameters matched exactly with their order present inside query string:

string query = "UPDATE Users SET FirstName=@FirstName,LastName=@LastName,UserPass=@UserPass,Gender=@Gender,Birthday=@Birthday,Email=@Email WHERE UserID=@id";

OleDbCommand sqlCmd = new OleDbCommand(query, sqlCon);

sqlCmd.Parameters.AddWithValue("@FirstName", (AdminBook.Rows[e.RowIndex].FindControl("txtFirstName") as TextBox).Text.Trim());
sqlCmd.Parameters.AddWithValue("@LastName", (AdminBook.Rows[e.RowIndex].FindControl("txtLastName") as TextBox).Text.Trim());
sqlCmd.Parameters.AddWithValue("@UserPass", (AdminBook.Rows[e.RowIndex].FindControl("txtUserPass") as TextBox).Text.Trim());
sqlCmd.Parameters.AddWithValue("@Gender", (AdminBook.Rows[e.RowIndex].FindControl("txtGender") as TextBox).Text.Trim());
sqlCmd.Parameters.AddWithValue("@Birthday", (AdminBook.Rows[e.RowIndex].FindControl("txtBirthday") as TextBox).Text.Trim());
sqlCmd.Parameters.AddWithValue("@Email", (AdminBook.Rows[e.RowIndex].FindControl("txtEmail") as TextBox).Text.Trim());
sqlCmd.Parameters.AddWithValue("@id", Convert.ToInt32(AdminBook.DataKeys[e.RowIndex].Value.ToString()));

Or add UserName column before WHERE clause without changing parameter order:

string query = "UPDATE Users SET FirstName=@FirstName,LastName=@LastName,UserPass=@UserPass,Gender=@Gender,Birthday=@Birthday,Email=@Email,
                UserName=@UserName WHERE UserID=@id";