update sql d/b with session / c#

Question

majestic0110 187 Nearly a Posting Virtuoso

16 Years Ago

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!

asp.net

2 Contributors
27 Replies
239 Views
6 Days Discussion Span
Latest Post 16 Years Ago Latest Post by majestic0110

All 27 Replies

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

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.

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

you don't have a runat server form?

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

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.

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

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.

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

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.");
        }
    }
}

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

majestic0110 187 Nearly a Posting Virtuoso · Answer 1 · 2008-02-28T20:17:12+00:00

majestic0110 187 Nearly a Posting Virtuoso

16 Years Ago

lol ok thanks a million bud!

majestic0110 187 Nearly a Posting Virtuoso · Answer 2 · 2008-02-28T20:43:21+00:00

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

majestic0110 187 Nearly a Posting Virtuoso · Answer 3 · 2008-02-28T21:10:45+00:00

majestic0110 187 Nearly a Posting Virtuoso

16 Years Ago

<form id="form1" runat="server">
yep lol

majestic0110 187 Nearly a Posting Virtuoso · Answer 4 · 2008-02-28T21:19:52+00:00

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 187 Nearly a Posting Virtuoso · Answer 5 · 2008-02-28T21:20:36+00:00

majestic0110 187 Nearly a Posting Virtuoso

16 Years Ago

<<ignore the fact that tb is commented lol

majestic0110 187 Nearly a Posting Virtuoso · Answer 6 · 2008-02-28T21:40:51+00:00

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 187 Nearly a Posting Virtuoso · Answer 7 · 2008-02-29T19:42:21+00:00

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
    }
}

majestic0110 187 Nearly a Posting Virtuoso · Answer 8 · 2008-02-29T21:27:28+00:00

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

majestic0110 187 Nearly a Posting Virtuoso · Answer 9 · 2008-02-29T21:59:34+00:00

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

SheSaidImaPregy 28 Veteran Poster · Answer 10 · 2008-02-29T22:02:33+00:00

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 187 Nearly a Posting Virtuoso · Answer 11 · 2008-03-03T15:23:03+00:00

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 187 Nearly a Posting Virtuoso · Answer 12 · 2008-03-03T18:29:24+00:00

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 28 Veteran Poster · Answer 13 · 2008-03-03T22:07:43+00:00

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

is tb declared, and a control?

majestic0110 187 Nearly a Posting Virtuoso · Answer 14 · 2008-03-03T22:11:32+00:00

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.");
        }
    }
}

majestic0110 187 Nearly a Posting Virtuoso · Answer 15 · 2008-03-03T22:12:05+00:00

you will notice that tb is not a standard control it is generated dynamicayll

SheSaidImaPregy 28 Veteran Poster · Answer 16 · 2008-03-03T22:55:58+00:00

then the control hasn't been created yet, but is in process.

majestic0110 187 Nearly a Posting Virtuoso · Answer 17 · 2008-03-04T15:04:59+00:00

ok buddy, here is what i have. I think the sql command is whats killing it:

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
{
    private String CurrentUser = "Unitialised";
    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.DataBind();
                    cell.Controls.Add(ddl);  
                }
                else
                {

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


                    if (key.Equals("username"))
                    {
                        CurrentUser = rowData[key];
                        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=" + CurrentUser, conn);
        cmd.Connection = conn;
      
        try {
            conn.Open();
            foreach (TableRow row in Table1.Rows)
            {
                cmd.Parameters.AddWithValue(row.Cells[0].Text, ((TextBox)row.Cells[1].Controls[0]).Text);
            }
          
            int recs = cmd.ExecuteNonQuery();            
            Response.Write(recs + " records updated.");
            conn.Close();
        } 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", CurrentUser);
        try
        {
            conn.Open();
            int recs = cmd.ExecuteNonQuery();
            conn.Close();

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

Thanks for your help man - your the best!

majestic0110 187 Nearly a Posting Virtuoso · Answer 18 · 2008-03-04T15:10:06+00:00

Just got delete working!

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
{
    private String CurrentUser = "Unitialised";
    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.DataBind();
                    cell.Controls.Add(ddl);  
                }
                else
                {

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


                    if (key.Equals("username"))
                    {
                        CurrentUser = rowData[key];
                        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=" + CurrentUser, conn);
        cmd.Connection = conn;
      
        try {
            conn.Open();
            foreach (TableRow row in Table1.Rows)
            {
                cmd.Parameters.AddWithValue(row.Cells[0].Text, ((TextBox)row.Cells[1].Controls[0]).Text);
            }
          
            int recs = cmd.ExecuteNonQuery();            
            Response.Write(recs + " records updated.");
            conn.Close();
        } catch {
            Response.Write( "failed update.");
        }
    }

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

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

majestic0110 187 Nearly a Posting Virtuoso · Answer 19 · 2008-03-04T16:56:50+00:00

ok, been trying to get insert to work, I thnk its the sql command statements that are wrong(in INSERT and UPDATE).

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
{
    private String CurrentUser = "Unitialised";
   
    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"))
                    {
                        CurrentUser = rowData[key];

                        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("@username", CurrentUser); 
        
        try 
        {
            conn.Open();

            foreach (TableRow row in Table1.Rows)
            {
                cmd.Parameters.AddWithValue(row.Cells[0].Text, ((TextBox)row.Cells[1].Controls[0]).Text);
             }                        
            
            int recs = cmd.ExecuteNonQuery();      
            conn.Close();     
            Response.Write(recs + " Record 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", CurrentUser);

        try
        {
            conn.Open();
            int recs = cmd.ExecuteNonQuery();
            conn.Close();
            Response.Write(recs + " Record Deleted!");
        }
        
        catch
        {
            Response.Write("failed deletion.");
        }
    }
    protected void AddBtn_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=blah;Integrated Security=True");
        SqlCommand cmd = new SqlCommand("INSERT INTO blahUserDetails(username, password, role, email, telephone, organization, fax, address, created, modified, expiry)VALUES(@username)", conn);
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("@username", CurrentUser);

        try
        {
            conn.Open();

            foreach (TableRow row in Table1.Rows)
            {
                cmd.Parameters.AddWithValue(row.Cells[0].Text, ((TextBox)row.Cells[1].Controls[0]).Text);
            }

            int recs = cmd.ExecuteNonQuery();
            conn.Close();
            Response.Write("New Record Inserted!");
        }

        catch
        {
            Response.Write("failed insert.");
        }
    }
}

SheSaidImaPregy 28 Veteran Poster · Answer 20 · 2008-03-04T23:14:42+00:00

Yup, here's the problem with UPDATE:

SqlCommand cmd = new SqlCommand("UPDATE blahUserDetails SET UserDetailsRow WHERE username=@username", conn);

You're not setting a value, it should be something like: UserDetailsRow=@Details
Otherwise, you are just calling the column, but never giving it a value. Hence the error.

Now for your insert:

SqlCommand cmd = new SqlCommand("INSERT INTO blahUserDetails(username, password, role, email, telephone, organization, fax, address, created, modified, expiry)VALUES(@username)", conn);

You're calling all these columns, but never giving them values. If you only wish to set the username, then call like this:
INSERT INTO BlahUserDetails (Username) VALUES (@Username)

majestic0110 187 Nearly a Posting Virtuoso · Answer 21 · 2008-03-05T01:21:31+00:00

majestic0110 187 Nearly a Posting Virtuoso

16 Years Ago

ok mate thanks a lot your the don!

majestic0110 187 Nearly a Posting Virtuoso · Answer 22 · 2008-03-05T17:45:59+00:00

I've got it! wicked. will post code later (on request) if anyone is interested! many thanks SheSaidImaPregy !

update sql d/b with session / c#

Recommended Answers Collapse Answers

All 27 Replies

Recommended Answers