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 :(

`@UserName` parameter seems not assigned anywhere, can you confirm what kind of values stored inside `cmd.Parameters` array? Since you're using `OleDbCommand`, the parameter order matters rather than parameter name. — Tetsuya Yamamoto, Dec 03 '18 at 02:52

score 0 · Accepted Answer · answered Dec 03 '18 at 03:07

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";

Thank you very much, Its helped me! Your answers are perfect! Well done! It was a big mistake and very stupid mistake from me :( Thank you very very much! — Alex, Dec 03 '18 at 14:06
Alex i will be really happy if you help me with my new problem: https://stackoverflow.com/questions/53599085/trying-to-make-search-database-by-username-in-textbox-by-paramaters-and-oledb — Alex, Dec 03 '18 at 18:31

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

1 Asp.net(OleDB)

2 HTML (Very Short)

1 Answers1