C# .NET - Updating:

Asked By Chandru
03-Aug-11 07:17 AM
Hi all,

     Getting a particular row from a table in textboxes and dropdownlists after logging in using username and password, how can I edit and update those datas?

      The submit button coding is like this,
     
    SqlConnection connection = new SqlConnection("connstring");
      connection.Open();
      string q = "select count(*) from table1 where username='" + username.Text + "' and password='" + pwd.Text + "'";
      SqlCommand command = new SqlCommand(q, connection);
      int result = (int)command.ExecuteScalar();
      if (result > 0)
      {
        Session["user"] = username.Text.ToString();
        Response.Redirect("default2.aspx");
      }    
      connection.Close();

      In default2 page, the binding is like this,

  public void BindGrid()
    {
      if (Session["user"] != null)
      {
        string username = Session["user"].ToString();
        string q = "select * from table1 where username='" + username + "'";
        SqlDataAdapter da = new SqlDataAdapter(q, connection);
        DataSet ds = new DataSet();
        da.Fill(ds);
        if (ds != null)
        {
          for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
          {
            if (ds.Tables[0].Rows.Count > 0)
            {
              TextBox1.Text = Convert.ToString(ds.Tables[0].Rows[i]["title"]);             
              TextBox2.Text = Convert.ToString(ds.Tables[0].Rows[i]["departmenthead"]);             
              TextBox3.Text = Convert.ToString(ds.Tables[0].Rows[i]["fax"]);
              TextBox4.Text = Convert.ToString(ds.Tables[0].Rows[i]["address"]);             
            }
          }
        }
      }


Thanks and REgards........


  Reena Jain replied to Chandru
03-Aug-11 07:33 AM
ello,

you can use @parameter for this. here is the updated code for you

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
  SqlCommand cmd;
protected void Button1_Click(object sender, EventArgs e)
  {
  con.Open();
  string query1 = "UPDATE table1 SET title=@title, departmenthead=@departmenthead, fax=@fax, address=@address where username = @username";
  cmd = new SqlCommand(query1, con);
  cmd.CommandType = CommandType.Text;
  cmd.AddWithValue("@title", txttitle.Text);
  cmd.AddWithValue("@user", txtuser.Text);
    cmd.AddWithValue("@departmenthead", txtdeartment.Text);
    cmd.AddWithValue("@fax", txtfax.Text);
    cmd.AddWithValue("@address", txtaddress.Text);
  //Execute Query
  cmd.ExecuteNonQuery();
  cmd.Parameters.Clear();
  con.Close();
   
Hope this will help you

hope this will help you
  dipa ahuja replied to Chandru
03-Aug-11 07:41 AM
Hi..

if you are working with one record at a time then i think instead of displaying the data in the textbox use the label. coz in the textbox use will easily update the data and change the values..


Now your actual answer.. if you want to update data try this code:

Take a button and now you are displaying data in the textbox, user will update it now to save , write the code in the button click:

protected void Button1_Click(object sender, EventArgs e)
{
  string connString = "Connectionstring";
 
  SqlConnection conn = new SqlConnection(connString);
  string query = "Update table1 set title=@title, departmenthead=@dept,fax=@fax,address=@addr where username=@uname";
 
  conn.Open();
 
  SqlCommand comm = new SqlCommand(query,conn);
  comm.Parameters.AddWithValue("title", TextBox1.Text);
  comm.Parameters.AddWithValue("dept", TextBox2.Text);
  comm.Parameters.AddWithValue("fax", TextBox3.Text);
  comm.Parameters.AddWithValue("addr", TextBox4.Text);
  comm.Parameters.AddWithValue("uname", Session["user"].ToString());
 
  //Execute query
 
  comm.ExecuteNonQuery();
  conn.Close();
}

  Chandru replied to Reena Jain
03-Aug-11 07:43 AM
Hi Reena,

    Thanks for your help.  But I have tried this, when i click the update button, it's not updating anything.  The same old datas are being coming again.  I dont know what's wrong with the codings.....



Thanks and regards,...... 





  Riley K replied to Chandru
03-Aug-11 08:01 AM
After Updating again call your Bind Grid Method

public void BindGrid()
  {
    if (Session["user"] != null)
    {
    string username = Session["user"].ToString();
    string q = "select * from table1 where username='" + username + "'";
    SqlDataAdapter da = new SqlDataAdapter(q, connection);
    DataSet ds = new DataSet();
    da.Fill(ds);
    if (ds != null)
    {
      for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
      {
      if (ds.Tables[0].Rows.Count > 0)
      {
        TextBox1.Text = Convert.ToString(ds.Tables[0].Rows[i]["title"]);      
        TextBox2.Text = Convert.ToString(ds.Tables[0].Rows[i]["departmenthead"]);      
        TextBox3.Text = Convert.ToString(ds.Tables[0].Rows[i]["fax"]);
        TextBox4.Text = Convert.ToString(ds.Tables[0].Rows[i]["address"]);      
      }
      }
    }
    }
Try this and let me know
  Chandru replied to dipa ahuja
03-Aug-11 08:13 AM
Hi,

   Dipa, when I click the update button its not updating.  The same old datas are coming again.  It was you gave the coding for binding, it works fine.  But when I go for updating its not working.  I dont know what's wrong with the coding.  Please try to sort it out.   


Thanks and Regards....
  dipa ahuja replied to Chandru
03-Aug-11 08:43 AM
Are your sure? coz this code is perfect . you may be doing some mistake... close the tables records and try to open them again by "Show table Data".

Because it won't take effect untill you reopen them.
  Chandru replied to dipa ahuja
03-Aug-11 08:51 AM
Hi,

    Dipa, I've tried all sorts of things.  The table is not updating with the new datas.  Anyway, thanks a lot for your help. 



Thanks and regards,......
  Chandru replied to dipa ahuja
03-Aug-11 09:09 AM
Dipa,

         It's a very simple thing i have missed it.  For the bindgrid() method I have not given if(!IsPostBack) in the page load.  Now I got it right.  Its working fine.  Updating fine.  Once again thanks for your help. 


Thanks and regards........
  dipa ahuja replied to Chandru
03-Aug-11 09:18 AM
thats great! cheers..!
Create New Account
help
near ' = '. Hi!Help fixing this error<< Incorrect syntax near ' = '. > > It is pointin to the sqlcomm.ExecuteNonQuery(); This is the code I am using: string sconStr = System.Configuration.ConfigurationManager.ConnectionStrings["EticketingconnectionString"].ToString(); SqlConnection sqlconnection = new SqlConnection(sconStr); sqlconnection.Close(); sqlconnection.Open(); string u = "select username, password from dbo.Registration where username = '" + UsernameTextBox.Text + "'&password = '"+PasswordTextBox Text+"'"; SqlCommand sqlcomm = new SqlCommand(u, sqlconnection); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = sqlcomm; sqlcomm.CommandType = CommandType.Text; sqlcomm.ExecuteNonQuery(); DataTable dt = new
dtTable public static DataTable dtTable; / / Get the connectionstring from the webconfig and declare a global SqlConnection "SqlConnection" public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; public SqlConnection SqlConnection = new SqlConnection(connectionString); / / Declare a global SqlDataAdapter SqlDataAdapter public SqlDataAdapter SqlDataAdapter = new SqlDataAdapter(); / / Declare a global SqlCommand SqlCommand public SqlCommand SqlCommand = new SqlCommand(); protected void
sender, EventArgs e) { if (!IsPostBack) { BindData(); } } private void BindData() { string connStr = ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString(); SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter ad = new SqlDataAdapter("select * from [mnames]", conn); DataSet dSet = new DataSet(); ad.Fill(dSet, "PagesData"); datagrid1.DataSource = dSet datagrid1.DataBind(); } protected void Button1_Click(object sender, EventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["MyConn77"].ToString(); SqlConnection conn = new SqlConnection(connStr); string query = "INSERT INTO [tasssk] (name, gender, qualification) VALUES('" + name1.Text + "', '" + gender.ToString() + "''" + qualification1.ToString() + "')"; SqlCommand dCmd = new SqlCommand(query, conn); conn.Open(); SqlDataAdapter da = new SqlDataAdapter(dCmd); DataSet dt = new DataSet (); da.Fill(dt); dCmd.CommandText = "insert_task"; dCmd.CommandType = CommandType.StoredProcedure
in gridview protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["MyConn77"].ToString(); SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter ad = new SqlDataAdapter("select name from [form] where autoid = @autoid" , conn); DataTable dt = new DataTable (); ad.Fill(dt the code protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["MyConn77"].ToString(); SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter ad = new SqlDataAdapter("select * from [form] where autoid = @autoid" , conn); DataTable dt = new DataTable (); ad.Fill(dt); gridview1
can yu plz modify diz code if (!IsPostBack) { BindData(); string connStr = ConfigurationManager.ConnectionStrings["MyConn77"].ToString(); SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter ad = new SqlDataAdapter("select * from [form]", conn); SqlDataAdapter da = new SqlDataAdapter("select autoid from [form]", conn); DataSet dSet = new DataSet(); ad.Fill(dSet, "PagesData"); GridView1.DataSource load : protected void Page_Load( object sender, EventArgs e) { if (!IsPostBack) { autoid.Text = getId(); } } string getId() { SqlConnection sqlconn = new SqlConnection( "Your Connection string" ); string value = "" ; try { if (sqlconn.State = = ConnectionState.Closed) { sqlconn.Open(); } string Query