update sql d/b with session / c#

[majestic0110]

hi all I have a connection to a sql d/b that loads data into a gridview. now in this grid view I have edit buttons that process user onto the 'edit' page (a separate page). the data from the row that was selected for editing was sent to the next page via the session technique. My question is, how can I make ediits from the second page update the sql d/b ? Thanks for your time!

UserName.Text = ((DataControlFieldCell)Session["TheRow1"]).Text;
            PassWord.Text = ((DataControlFieldCell)Session["TheRow2"]).Text;
            DropDownList1.Text = ((DataControlFieldCell)Session["TheRow3"]).Text;
            EmailBox.Text = ((DataControlFieldCell)Session["TheRow4"]).Text;
            TelBox.Text = ((DataControlFieldCell)Session["TheRow5"]).Text;
            OrgBox.Text = ((DataControlFieldCell)Session["TheRow6"]).Text;
            FaxBox.Text = ((DataControlFieldCell)Session["TheRow7"]).Text;
            AddressBox.Text = ((DataControlFieldCell)Session["TheRow8"]).Text;
            DCreatedBox.Text = ((DataControlFieldCell)Session["TheRow9"]).Text;
            DExpiresBox.Text = ((DataControlFieldCell)Session["TheRow10"]).Text;
            DModBox.Text = ((DataControlFieldCell)Session["TheRow11"]).Text;
            UserName.ReadOnly = true;

BTW the code above demonstrates the filling up of components on edit page!

[SheSaidImaPregy]

Make the edits, then create a sub to handle a submit onclick. Within that onclick, update the database with an update command. But make sure you have the record ID, you wouldn't want to update every record in the table on accident :O

Oh I have done that once.. to a 200,000 row table. I was wondering what was taking awhile for a single row update, just to realize I didn't specify the WHERE clause.

Fun times rolling back.

[majestic0110]

lol ok thanks a million bud!

[majestic0110]

have decided to add the content dynamically instead:

foreach (String key in rowData.Keys)
            {
                

                TableRow newRow = new TableRow();
                TableCell cell = new TableCell();
                cell.Text = key;
                cell.CssClass = "FixedText";
                newRow.Cells.Add(cell);
                cell = new TableCell();
                
                cell.Text = rowData[key];
                cell.CssClass = "DataText";
                newRow.Cells.Add(cell);
                TextBox tb = new TextBox();
                tb.Text = rowData[key];
                Controls.Add(tb);
                Table1.Rows.Add(newRow);
                
               

            }

I wanted the textboxes to be added dynamically (and editable) but i keep getting "textbox must be placed inside tag with run at server!"

[SheSaidImaPregy]

you don't have a runat server form?

[majestic0110]

yep lol

[SheSaidImaPregy]

make sure you are spitting out the textboxes within the form. If not, you will get this error. You are most likely spitting out the info above the form tag.

[majestic0110]

I think there must be something wrong in the code

foreach (String key in rowData.Keys)
            {
                updateStart.Append(comma + key);
                updateEnd.Append(comma + rowData[key]);
                comma = ", ";

                TableRow newRow = new TableRow();
                TableCell cell = new TableCell();
                cell.Text = key;
                cell.CssClass = "FixedText";
                newRow.Cells.Add(cell);
                cell = new TableCell();
                
                cell.Text = rowData[key];
                cell.CssClass = "DataText";
                newRow.Cells.Add(cell);

                //TextBox tb = new TextBox();
                //tb.Text = key;
                //newRow.Controls.Add(tb);
                //tb = new TextBox();
                //cell.Text = rowData[key];
                //newRow.Controls.Add(tb);


                Table1.Rows.Add(newRow);

[majestic0110]

<

[SheSaidImaPregy]

It doesn't look like there is something wrong. Where are you actually putting the string on the page? It usually doesn't throw this error unless you are putting server content outside the tag.

[majestic0110]

Aha! lol check it out! works now! (although need an update statement for 'save' button event handler lol

TextBox tb = new TextBox();
                tb.Text = rowData[key];
                cell.Controls.Add(tb);
                newRow.Cells.Add(cell);

[majestic0110]

ok check this out, still am unable to figure out how to update (bear in mind the drop down list)

using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Restricted_UserEdit : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            Dictionary<String, String> rowData = (Dictionary<String, String>)Session["UserDetailsRow"];
            StringBuilder updateStart = new StringBuilder("UPDATE natsUserDetails SET UserDetailsRow ");
            StringBuilder updateEnd = new StringBuilder("");
            String comma = "";

            foreach (String key in rowData.Keys)
            {
                updateStart.Append(comma + key);
                updateEnd.Append(comma + rowData[key]);
                comma = ", ";

                TableRow newRow = new TableRow();
                TableCell cell = new TableCell();
                
                // FIRST CELL
                cell.Text = key;
                cell.CssClass = "FixedText";
                newRow.Cells.Add(cell);

                // SECOND CELL
                cell = new TableCell();
                cell.CssClass = "DataText";

                // SECOND CELLS TEXTBOX
                if (key.Equals("role"))
                {
            
                    DropDownList ddl = new DropDownList();
                    ddl.DataSource = SqlDataSource1;
                    ddl.DataTextField = "role";
                    //ddl.DataValueField = "role";
                    ddl.DataBind();
                    cell.Controls.Add(ddl);  
                }
                else
                {

                    TextBox tb = new TextBox();
                    tb.Text = rowData[key];
                    cell.Controls.Add(tb);
                   

                    if (key.Equals("username"))
                    {

                        tb.ReadOnly = true;
                    }
                }
                 newRow.Cells.Add(cell);
               Table1.Rows.Add(newRow);              
                
            }

         
        }

        catch
        {
            DeleteUserBtn.Visible = false;
           
        }
    }

    protected void CancelBtn_Click(object sender, EventArgs e)
    {
        Response.Redirect("UserAdmin.aspx");
    }

    protected void SaveBtn_Click(object sender, EventArgs e)
    {
              //need to commit updates here
    }

    protected void DeleteUserBtn_Click(object sender, EventArgs e)
    {
//need to delete selected user data here
    }
}

[SheSaidImaPregy]

Well I don't do c#, but I can "try" for you. I also don't do code-behind ^^

protected void CancelBtn_Click(object sender, EventArgs e)
    {
        Response.Redirect("UserAdmin.aspx");
    }

    protected void SaveBtn_Click(object sender, EventArgs e)
    {
        SqlConnection conn = New SqlConnection( connstringfromweb.config );
        SqlCommand cmd = New SqlCommand( "UPDATE Inventory SET tb1=@tb1, tb2=@tb2, tb3=@tb3 WHERE InventoryID=@ID", conn);
        cmd.Parameters.AddWithValue("@tb1", tb1.Text.Trim());
        cmd.Parameters.AddWithValue("@tb2", tb2.Text.Trim());
        cmd.Parameters.AddWithValue("@tb3", tb3.Text.Trim());
        cmd.Parameters.AddWithValue("@ID", hiddenhtmlfield.Value);
        try {
            conn.Open();
            Int recs = cmd.ExecuteNonQuery();
            conn.Close();

            //response.write(recs + " records updated.");
        } catch {
            response.write("failed update.");
        }
    }

    protected void DeleteUserBtn_Click(object sender, EventArgs e)
    {
        SqlConnection conn = New SqlConnection( connstringfromweb.config );
        SqlCommand cmd = New SqlCommand( "DELETE FROM Inventory WHERE InventoryID=@ID", conn);
        cmd.Parameters.AddWithValue("@ID", hiddenhtmlfield.Value);
        try {
            conn.Open();
            Int recs = cmd.ExecuteNonQuery();
            conn.Close();

            //response.write(recs + " records deleted.");
        } catch {
            response.write("failed deletion.");
        }
    }
}

[majestic0110]

thanks a lot mate, you are a GOD! will let you know how it goes in a bit

[majestic0110]

what is hiddenhtmlfield.ValueVS doesnt recognise it as c#? thanks

[SheSaidImaPregy]

you need to keep track of the ID of the record you are updating. Put it there, or create a "hidden html field" and set the value of it on page_load. Then, grab the value when you update/delete.

[majestic0110]

ok. I keep catching failures why is that?

protected void SaveBtn_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection( @"Data Source=.\SQLEXPRESS;Initial Catalog=blah;Integrated Security=True" );
        SqlCommand cmd = new SqlCommand("UPDATE UserDetails SET UserDetailsRow WHERE username=@username", conn);
        
        try {
            conn.Open();
            int recs = cmd.ExecuteNonQuery();
            conn.Close();

            Response.Write(recs + " records updated.");
        } catch {
            Response.Write("failed update.");
        }
    }

    protected void DeleteUserBtn_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=blah;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("DELETE FROM UserDetails WHERE username=@username", conn);
        cmd.Parameters.AddWithValue("@username", null);
        try
        {
            conn.Open();
            int recs = cmd.ExecuteNonQuery();
            conn.Close();

            Response.Write(recs + " records deleted.");
        }
        catch
        {
            Response.Write("failed deletion.");
        }

[majestic0110]

I am guessing its becuase I have not included:
cmd.Parameters.AddWithValue("@tb", tb.Text.Trim());
but when i include that I get an error 'tb does not exist in current context.' hmm.

[SheSaidImaPregy]

is tb declared, and a control?

[majestic0110]

yep, and yep

using System;
using System.Data;
using System.Text;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Restricted_UserEdit : System.Web.UI.Page
  
{
    
    protected void Page_Load(object sender, EventArgs e)
    {
        
        try
        {
            Dictionary<String, String> rowData = (Dictionary<String, String>)Session["UserDetailsRow"];
            StringBuilder updateStart = new StringBuilder("UPDATE blahUserDetails SET UserDetailsRow ");
            StringBuilder updateEnd = new StringBuilder("");
            String comma = "";

            foreach (String key in rowData.Keys)
            {
                updateStart.Append(comma + key);
                updateEnd.Append(comma + rowData[key]);
                comma = ", ";

                TableRow newRow = new TableRow();
                TableCell cell = new TableCell();
                
                // FIRST CELL
                cell.Text = key;
                cell.CssClass = "FixedText";
                newRow.Cells.Add(cell);

                // SECOND CELL
                cell = new TableCell();
                cell.CssClass = "DataText";

                // SECOND CELLS TEXTBOX
                if (key.Equals("role"))
                {
            
                    DropDownList ddl = new DropDownList();
                    ddl.DataSource = SqlDataSource1;
                    ddl.DataTextField = "role";
                    //ddl.DataValueField = "role";
                    ddl.DataBind();
                    cell.Controls.Add(ddl);  
                }
                else
                {

                    TextBox tb = new TextBox();
                    tb.Text = rowData[key];
                    cell.Controls.Add(tb);

                    if (key.Equals("username"))
                    {
                        if (rowData.ContainsValue(""))
                        {
                            tb.ReadOnly = false;
                            DeleteUserBtn.Visible = false;
                            SaveBtn.Visible = false;
                            AddBtn.Visible = true;
                        }
                        else
                        {
                        AddBtn.Visible = false;
                        tb.ReadOnly = true;
                        }
                    }                                
                }
                 newRow.Cells.Add(cell);
               Table1.Rows.Add(newRow);
            }
                      
        }

        catch
        {
            DeleteUserBtn.Visible = false;
           
        }
    }

    protected void CancelBtn_Click(object sender, EventArgs e)
    {
        Response.Redirect("UserAdmin.aspx");
    }

    protected void SaveBtn_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection( @"Data Source=.\SQLEXPRESS;Initial Catalog=blah;Integrated Security=True" );
        SqlCommand cmd = new SqlCommand("UPDATE blahUserDetails SET UserDetailsRow WHERE username=@username", conn);
        cmd.Connection = conn;
        //cmd.Parameters.AddWithValue("@tb", tb.Text.Trim());

        try {
            conn.Open();
            int recs = cmd.ExecuteNonQuery();
            conn.Close();

            Response.Write(recs + " records updated.");
        } catch {
            Response.Write("failed update.");
        }
    }

    protected void DeleteUserBtn_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=blah;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("DELETE FROM blahUserDetails WHERE username=@username", conn);
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("@username", null);
        try
        {
            conn.Open();
            int recs = cmd.ExecuteNonQuery();
            conn.Close();

            Response.Write(recs + " records deleted.");
        }
        catch
        {
            Response.Write("failed deletion.");
        }
    }
}