Click here to Skip to main content
15,907,905 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have retrieve data from datatable to textbox and i have created a system navigation with a next button and its ok until here.
now i want to update data in this textbox and save them to the database.
i have code this but results me with an error.. everytime a change a value in a textbox.. all the records of the datatable store the same values
what have i done wrong???
here is my code:
namespace Csharp
{
    public partial class prova1 : System.Web.UI.Page
    {


        private DataTable GetData()
        {

            DataTable dt = new DataTable();


            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ProvaConnectionString"].ConnectionString);

            try
            {

                connection.Open();

                SqlCommand sqlCmd = new SqlCommand("Select * From prova", connection);
                SqlCommand sqlCmd2 = new SqlCommand("UPDATE Customer SET Code='" + txt_kodi.Value + "', Tel='" + txt_tel.Value + "', Mobile = '" + txt_mobile.Value + "'");

                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);



                sqlDa.Fill(dt);

            }

            catch (System.Data.SqlClient.SqlException ex)
            {

                string msg = "Fetch Error:";

                msg += ex.Message;

                throw new Exception(msg);



            }

            finally
            {

                connection.Close();

            }

            return dt;

        }

        protected void Page_Load(object sender, EventArgs e)
        {

            if (!Page.IsPostBack)
            {
                txt_kodi.Disabled = true;
                txt_persh.Disabled = true;
                txt_pershholl.Disabled = true;
                txt_tel.Disabled = true;
                txt_mobile.Disabled = true;

                DataTable dt = GetData();
                Hidden_id.Value = "0";
                if (dt.Rows.Count > 0)
                {

                    // Populate the TextBox with the first entry on page load

                    txt_kodi.Value = dt.Rows[0]["Code"].ToString();

                   

                    txt_tel.Value = dt.Rows[0]["Tel"].ToString();

                    txt_mobile.Value = dt.Rows[0]["Mobile"].ToString();





                    //Then we store the DataTable in Session so that we will NOT

                    //query the DB on every postbacks

                    Session["dt"] = dt;

                }



            }

        }



        protected void btn_next_Click(object sender, EventArgs e)
        {

            int rowIndex = 0;

            if (Hidden_id.Value.Trim() != null)
            {
                rowIndex = Convert.ToInt32(Hidden_id.Value) + 1;



                if (Session["dt"] != null)
                {

                    DataTable dt = (DataTable)Session["dt"];

                    if (rowIndex < dt.Rows.Count)
                    {

                        //get the next row entry on Button Click by setting the Row Index
                        txt_kodi.Value = dt.Rows[rowIndex]["Code"].ToString();

                     

                        txt_tel.Value = dt.Rows[rowIndex]["Tel"].ToString();

                        txt_mobile.Value = dt.Rows[rowIndex]["Mobile"].ToString();

                        Session["dt"] = dt;
                        Hidden_id.Value = rowIndex.ToString();



                    }
                    else

                    { Response.Write("Last record!"); }

                }

            }
        }
 protected void btn_modify_Click(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ProvaConnectionString"].ConnectionString);

            SqlCommand sqlCmd2 = new SqlCommand("UPDATE prova SET Code='" + txt_kodi.Value + "', Tel='" + txt_tel.Value + "', Mobile = '" + txt_mobile.Value + "'");

            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd2);

            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder();

            cmdBuilder = new SqlCommandBuilder(sqlDa);


            int rowIndex = 0;

            if (Hidden_id.Value.Trim() != null)
            {
                rowIndex = Convert.ToInt32(Hidden_id.Value);



                if (Session["dt"] != null)
                {

                    DataTable dt = (DataTable)Session["dt"];
                    rowIndex = 0;

                    System.Data.DataRow dRow = dt.Rows[rowIndex];

                    dRow["Code"] = txt_kodi.Value;
                   
                    dRow["Tel"] = txt_tel.Value;
                    dRow["Mobile"] = txt_mobile.Value;
                    connection.Open();
                    sqlCmd2.Connection = connection;
                    sqlCmd2.ExecuteNonQuery();
                    connection.Close();
                
                    Session["dt"] = dt;
                    Hidden_id.Value = rowIndex.ToString();
                    Response.Write("data save!");
                }
            }
        }
    } 
Posted

1 solution

your update statement needs to be amended so that it only updates a particular row or rows

"UPDATE Customer SET Code='" + txt_kodi.Value + "', Tel='" + txt_tel.Value + "', Mobile = '" + txt_mobile.Value + "'"


needs to have a where clause on it to stop it updating the entire table

i.e.

UPDATE Customer SET Code = '" + txt_kodi.Value + "', Tel='" + txt_tel.Value + "', Mobile = '" + txt_mobile.Value + "' WHERE CustomerID = 1


I would also recommend that you get into using parameterised queries too they will make your code more secure against SQL Injection

Parameterised Queries Example[^]

EDIT [Updated to add how to Update Records in ADO.NET]
C#
using (SQLConnection db = new SQLConnection(mydatabaseString))
{
  SqlCommand cmd = new SqlCommand(sqlString, db);
  int RowsEffected = cmd.ExecuteNonQuery();
}
 
Share this answer
 
v2
Comments
lovitaxxxx 4-Dec-12 9:14am    
But i havent specified the id value
can i do this: to store the id value in a hiddenfield and then call it to the update query?
Simon_Whale 4-Dec-12 9:18am    
I used the CustomerID as a obvious example of a way to limit the effect of the update statement.

you should be able to do that approach of hiding the customerID and then using it inside your update statement
lovitaxxxx 4-Dec-12 9:25am    
still it doesnt give any result :/
Simon_Whale 4-Dec-12 9:28am    
what does the SQL Statement look like now?
lovitaxxxx 4-Dec-12 9:30am    
like this:
SqlCommand sqlCmd2 = new SqlCommand("UPDATE prova SET Code='" + txt_kodi.Value + "', Tel='" + txt_tel.Value + "', Mobile = '" + txt_mobile.Value + "' WHERE Id='" +txt_id + "' ");
where txt_id.Value = dt.Rows[rowIndex]["Id"].ToString();
and
<asp:HiddenField ID="txt_id" runat="server" />

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900