Update database with values from textbox

Question

i m trying to edit the values in database through textboxes in ASP.

first i retrived the values from database and set those values to the value property of textboxes on the form so that user can see the old values.

now, i want him to enter new values in the same textboxes and when he click on update the new values should be updated in the database.

can any one tell what i have to do to get those new values???? when to submit the form????

the code:

protected void Button2_Click(object sender, EventArgs e)
        {
            string MachineGroupName = TextBox2.Text;
            string MachineGroupDesc = TextBox3.Text;
            int TimeAdded = DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second;

            if (MachineGroupName == "" || MachineGroupDesc == "")
            {
                Label2.Text = ("Please ensure all fields are entered");
                Label2.Visible = true;
            }
            else
            {
                System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection();
                dataConnection.ConnectionString =
                    @"Data Source=JAGMIT-PC\SQLEXPRESS;Initial Catalog=SumooHAgentDB;Integrated Security=True";

                System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
                dataCommand.Connection = dataConnection;

                //tell the compiler and database that we're using parameters (thus the @first, @last, @nick)  
                dataCommand.CommandText = ("UPDATE [MachineGroups] SET ([MachineGroupName]=@MachineGroupName,[MachineGroupDesc]=@MachineGroupDesc,[TimeAdded]=@TimeAdded) WHERE ([MachineGroupID]= @node)");

                //add our parameters to our command object  
                dataCommand.Parameters.AddWithValue("@MachineGroupName", MachineGroupName);
                dataCommand.Parameters.AddWithValue("@MachineGroupDesc", MachineGroupDesc);
                dataCommand.Parameters.AddWithValue("@TimeAdded", TimeAdded);

                dataConnection.Open();
                dataCommand.ExecuteNonQuery();
                dataConnection.Close();

            }

This is unrelated to the question, but don't forget to dispose of your SqlCommand object. — Chuck Conway, Sep 19 '09 at 23:27
also i am gettind the node value from a querystring passed from another page.. that is working fine The page load event has string node = Request.QueryString["node"]; so this is getting the node value — user175084, Sep 19 '09 at 23:33
do you encounter an exception? cause your code seems kinda ok. — Kostas Konstantinidis, Sep 19 '09 at 23:34
If you have a textbox whose id is MachineGroupName, you'll need to use its Text property to get at its most recent value (e.g., MachineGroupName.Text). — David Andres, Sep 19 '09 at 23:38
no i get a error message Server Error in '/' Application. Incorrect syntax near '('. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '('. — user175084, Sep 19 '09 at 23:42
@unknown: You don't need to parenthesize the SET or WHERE clauses in your UPDATE statement. This is probably what's throwing off your statements. — David Andres, Sep 19 '09 at 23:55

Canavar · Answer 1 · 2009-09-20T08:21:16.927

You're not providing the @node parameter. so you should get an exception. Also change your sql statement like that without parenthesis :

long MachineGroupID = Convert.ToInt64(Request.QueryString["node"]); 
dataCommand.CommandText = "UPDATE [MachineGroups] SET [MachineGroupName]=@MachineGroupName,[MachineGroupDesc]=@MachineGroupDesc,[TimeAdded]=@TimeAdded WHERE [MachineGroupID]= @MachineGroupID";

//add our parameters to our command object  
dataCommand.Parameters.AddWithValue("@MachineGroupName", MachineGroupName);
dataCommand.Parameters.AddWithValue("@MachineGroupDesc", MachineGroupDesc);
dataCommand.Parameters.AddWithValue("@TimeAdded", TimeAdded);
dataCommand.Parameters.AddWithValue("@MachineGroupID", MachineGroupID);

EDIT : As you posted your insert page, your table should have an ID column to identify your record uniquely. As I see in your update SQL youe ID column's name is MachineGroupID. So to update your record, you should provide MachineGroupID as @node parameter. try to get this MachineGroupID value in your event and pass it into your Command.

yes, but you should set it in the way you're getting the MachineGroupName and MachineGroupDesc. — Canavar, Sep 20 '09 at 00:13
the value of the ID is coming from another page as node. I am using a query string to get it. I kno it is coming because the textboxes are getting the values. So i cant give a textbox to enter the ID as node value is the ID — user175084, Sep 20 '09 at 00:56
ok, I updated the answer as the value is coming from the querystring. then this should work. — Canavar, Sep 20 '09 at 01:08

score 1 · Answer 2 · edited Oct 29 '12 at 15:04

long MachineGroupID = Convert.ToInt64(Request.QueryString["node"]);  
dataCommand.CommandText = "UPDATE [MachineGroups] SET 
[MachineGroupName]=@MachineGroupName,[MachineGroupDesc]=@MachineGroupDesc,    
[TimeAdded]=@TimeAdded WHERE [MachineGroupID]= @MachineGroupID",cn;  //add our parameters to our command object   
dataCommand.Parameters.AddWithValue("@MachineGroupName", MachineGroupName); 
dataCommand.Parameters.AddWithValue("@MachineGroupDesc", MachineGroupDesc); 
dataCommand.Parameters.AddWithValue("@TimeAdded", TimeAdded); 
dataCommand.Parameters.AddWithValue("@MachineGroupID", MachineGroupID);

example :

SqlCommand cmdup = new SqlCommand("UPDATE [port1] SET [prt1]=@prt1 WHERE [no]= 1", cn);             
cmdup.Parameters.Add("@prt1", TextBox1.Text);   
cmdup.ExecuteNonQuery();

I think this may help your case, mention Connection at the last of your update command

score 0 · Accepted Answer · answered Sep 20 '09 at 00:22

ok i have the insert page which is working fine with this code.......

    protected void Button2_Click(object sender, EventArgs e)
    {
        string MachineGroupName = TextBox2.Text;
        string MachineGroupDesc = TextBox3.Text;
        int TimeAdded = DateTime.Now.Hour+DateTime.Now.Minute+DateTime.Now.Second;

        if (MachineGroupName == "" || MachineGroupDesc == "")
        {
            Label1.Text = ("Please ensure all fields are entered");
            Label1.Visible = true;
        }
        else
        {
            System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection();
            dataConnection.ConnectionString =
                @"Data Source=JAGMIT-PC\SQLEXPRESS;Initial Catalog=SumooHAgentDB;Integrated Security=True";

            System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
            dataCommand.Connection = dataConnection;

            //tell the compiler and database that we're using parameters (thus the @first, @last, @nick)  
            dataCommand.CommandText = ("INSERT [MachineGroups] ([MachineGroupName],[MachineGroupDesc],[TimeAdded]) VALUES (@MachineGroupName,@MachineGroupDesc,@TimeAdded)");

            //add our parameters to our command object  
            dataCommand.Parameters.AddWithValue("@MachineGroupName", MachineGroupName);
            dataCommand.Parameters.AddWithValue("@MachineGroupDesc", MachineGroupDesc);
            dataCommand.Parameters.AddWithValue("@TimeAdded", TimeAdded);

            dataConnection.Open();
            dataCommand.ExecuteNonQuery();
            dataConnection.Close();

        }

Update database with values from textbox

3 Answers3

Linked