SQL server update query not working

Marius Vasile

I want to edit a Datalist values and update but I can't see why the update procedure does not modify the text. What I have is

Page view

code behind

protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
    using (SqlConnection conn = new SqlConnection(connString))
    {
        int id = Convert.ToInt32(DataList1.DataKeys[e.Item.ItemIndex].ToString());
        TextBox txtIntrebare = (TextBox)e.Item.FindControl("txtIntrebare");

        string sqlQuery = "UPDATE tblIntrebare SET Intrebare=@Intrebare WHERE IdIntrebare=@id";
        using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@Intrebare", SqlDbType.NVarChar).Value = txtIntrebare.Text;

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        DataList1.EditItemIndex = -1;
        LoadData();
    }
}

So, on update the int id is correct but txtIntrebare is not modified at all

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Saravanan Ganesan3y ago
Accepted answer
Hi Marius ,

It looks like you are using a DataList control to display a list of items and allow editing. The DataList control is part of ASP.NET Web Forms, and updating the values in edit mode should work as expected. However, since you mentioned that the values are not getting updated, there could be several reasons for this behavior.
1. ViewState: Make sure that ViewState is enabled for the page and control. Ensure that the EnableViewState property is set to true at both the page level and the DataList control level.
2. PostBack Handling: Ensure that you are handling postbacks correctly in your code-behind. If you are rebinding the DataList on each postback, it could cause the values not to persist during the update.
3. Editing Mode: Verify that you are entering edit mode correctly when clicking the "Edit" button. The EditItemTemplate should be displayed when you click the "Edit" button and switch the DataList to edit mode.
4. Check Event Handlers: Double-check your event handlers in the code-behind to ensure that the DataList1_UpdateCommand method is indeed called when the "Update" button is clicked.
5. Binding Data: When calling LoadData() to bind data to the DataList, make sure you are calling it within a !IsPostBack block to avoid resetting the control state on postbacks.
6. Debugging: Place breakpoints in the DataList1_UpdateCommand method and examine the values of id and txtIntrebare.Text to ensure they have the correct values during the update. If you have tried all the above steps and the issue persists, you may want to share more details about the LoadData() method, the event handling, and how the DataList control is being populated with data with more context.
Marius Vasile3y ago
I found the problem, it seems that the session is causing the update failure. So I probably should do the post back within session page load
Marius Vasile3y ago
Ok, I've checked all items in your post Saravanan and for:

6. Debugging: Place breakpoints in the DataList1_UpdateCommand method and examine the values of id and txtIntrebare.Text to ensure they have the correct values during the update.

the id is correct but the textbox is not modified, it is showing the old value not the updated one

Marius Vasile3y ago

It is really frustating because I followed an old project I did few years ago which is working even now without issues. This one though...is not. My code behind is below

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Instruire.AdminPages
{
    public partial class Intrebari : Page
    {
        private readonly string connString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["Sectiune"] != null)
            {
                txtParam.Text = Session["Sectiune"].ToString();
                LoadData();
            }
            if (!IsPostBack)
            {
                LoadSectiune();
            }
        }
        protected void LoadSectiune()
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sqlQuery = "SELECT DISTINCT Sectiune FROM tblProcedura";
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    conn.Open();
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    ddlSectiune.DataSource = ds;
                    ddlSectiune.DataBind();
                }
            }
        }
        protected void ddlSectiune_SelectedIndexChanged(object sender, EventArgs e)
        {
            ddlCodProcedura.Items.Clear();
            ddlCodProcedura.Items.Add("Cod procedura");

            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sqlQuery = "SELECT CodProcedura FROM tblProcedura WHERE Sectiune=@parameter";
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@parameter", ddlSectiune.SelectedItem.Text);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    conn.Open();
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    ddlCodProcedura.DataSource = ds;
                    ddlCodProcedura.DataBind();
                }
            }
        }
        protected void ddlCodProcedura_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtProcedura.Text = string.Empty;

            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sqlQuery = "SELECT * FROM tblProcedura WHERE CodProcedura=@parameter";
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@parameter", ddlCodProcedura.SelectedItem.Text);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    conn.Open();

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            txtIdProcedura.Text = rdr["IdProcedura"].ToString();
                            txtProcedura.Text = rdr["Procedura"].ToString();
                        }
                    }
                    conn.Close();
                }
            }
        }
        protected void LoadData()
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sqlQuery = "SELECT * FROM tblIntrebare t1 JOIN tblProcedura t2 ON t1.IdProcedura=t2.IdProcedura WHERE t2.Sectiune=@parameter";
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@parameter", txtParam.Text);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    conn.Open();
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    DataList1.DataSource = ds.Tables[0];
                    DataList1.DataBind();
                }
            }
        }
        protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
        {
            if (e.CommandName.Equals("Edit"))
            {
                DataList1.EditItemIndex = e.Item.ItemIndex;
                LoadData();
            }
            else if (e.CommandName.Equals("Update"))
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    int id = Convert.ToInt32(DataList1.DataKeys[e.Item.ItemIndex].ToString());
                    
                    TextBox txtIntrebare = (TextBox)e.Item.FindControl("txtIntrebare");

                    string sqlQuery = "UPDATE tblIntrebare SET Intrebare=@Intrebare WHERE IdIntrebare=@id";
                    using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                        cmd.Parameters.Add("@Intrebare", SqlDbType.NVarChar).Value = txtIntrebare.Text;

                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();

                        DataList1.EditItemIndex = -1;
                    }
                    LoadData();
                }
            }
        }

        protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
        {
            DataList1.EditItemIndex = -1;
            LoadData();
        }
        protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
        {
            TextBox tmp = (TextBox)(e.Item.FindControl("txtID"));

            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sqlQuery = "DELETE FROM tblIntrebare WHERE IdIntrebare=@parameter";
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@parameter", tmp.Text);
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
            LoadData();
        }
        protected void Adauga_Intrebare(object sender, EventArgs e)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand("AddIntrebare", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@IdProcedura", txtIdProcedura.Text);
                    cmd.Parameters.AddWithValue("@Intrebare", txtIntrebare.Text);

                    conn.Open();

                    int result = cmd.ExecuteNonQuery();

                    conn.Close();

                    ddlSectiune.Items.Clear();
                    ddlCodProcedura.Items.Clear();
                    txtProcedura.Text = string.Empty;
                    txtIntrebare.Text = string.Empty;

                    if (result > 0)
                    {
                        ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Record Inserted Successfully')", true);
                    }
                }
            }
            Response.Redirect("/AdminPages/Intrebari.aspx");
        }
        protected void View_General(object sender, EventArgs e)
        {
            string txtSession = "General";
            Session["Sectiune"] = txtSession;
            Response.Redirect("/AdminPages/Intrebari.aspx");
        }
        protected void View_Specific(object sender, EventArgs e)
        {
            string txtSession = "Specific";
            Session["Sectiune"] = txtSession;
            Response.Redirect("/AdminPages/Intrebari.aspx");
        }
    }
}

I am using a Session to load data, no other Load_Data(). Edit mode and Update mode are as required, at least from my point of view

Marius Vasile3y ago

Hi Saravanan, thank you for replying. I removed all visible="false" from all divs in the page. my entire View is below. still not working

<%@ Page Title="" Language="C#" MasterPageFile="~/Instruire.Master" EnableViewState="true"  AutoEventWireup="true" CodeBehind="Intrebari.aspx.cs" Inherits="Instruire.AdminPages.Intrebari" %>

    
        
            
                Intrebari
            

            
                
                    
                        
                            
                        
                        
                            
                        
                        
                            
                        
                    
                    
                        
                              
                                --Sectiune--
                            
                        
                        
                              
                                -- Cod Procedura --

Saravanan Ganesan3y ago
Hi Marius ,

It appears that the issue might be related to the ViewState in ASP.NET Web Forms. When you set Visible="false" on the DivIntrebare div element containing the DataList, the content of the DataList is not preserved in the ViewState, which is causing the problem.

To fix this issue, you can try the following steps:
1. Remove the Visible="false" attribute from the DivIntrebare div element. This ensures that the content of the DataList is present in the ViewState.
2. Instead of using the Visible attribute to control the visibility of the DivIntrebare div, you can use CSS to hide or show the element. For example, you can add the following CSS style to hide the div:
3. Update your Page view to use the CSS class to control the visibility of the DivIntrebare div:
4. In your code-behind, you can use the Visible property of the DivIntrebare div instead of directly setting the Display property in the CSS class:
  protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e) { // ... Your existing code ... // Show the DivIntrebare div again DivIntrebare.Visible = true; // ... Rest of your code ... }
With these changes, your DataList content should now be preserved in the ViewState, and you should be able to update the txtIntrebare TextBox correctly when the UpdateCommand is triggered.