C# ASP.net Update Data into a SQL server database

Question

mike888 -2 Newbie Poster

14 Years Ago

Hi, I have a problem with my homework assignment.
I am able to access the database and insert new customer but it doesn't work when i try to update any record.
if anyone can tell me what is wrong with my code.
thank you so much

this is all my code

using System;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;


public partial class CustomerEdit : System.Web.UI.Page
{
    String CustomerID = "";

    protected void Page_Load(object sender, EventArgs e)
    {
        this.Master.HighlightMenu = "Customers";

        if (Request.QueryString["id"] != null)
        {




            CustomerID = Request.QueryString["id"];



            Label1.Text = CustomerID;

            txtFirstName.Text = "";
            txtLastName.Text = "";
            txtEmailA.Text = "";
            txtEmailB.Text = "";
            txtCity.Text = "";
            txtPasswordA.Text = "";
            txtPasswordB.Text = "";
            txtFax.Text = "";
            txtAddress1.Text = "";
            txtAddress2.Text = "";
            txtState.Text = "";
            txtZip.Text = "";
            txtPhone.Text = "";




            //Declare the connection object
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

            //Make the connection 
            Conn.Open();

            //Define you query
            string sql = "SELECT * FROM [db_owner].[Customer] Where CustomerID=@CustomerID";

            //Declare the Command
            SqlCommand cmd = new SqlCommand(sql, Conn);

            //Add the parameters needed for the SQL query
            cmd.Parameters.AddWithValue("@CustomerID", CustomerID);

            //Declare the DataReader
            SqlDataReader dr = null;

            //Fill the DataReader
            dr = cmd.ExecuteReader();

            //Get the data
            if (dr.Read() == false)
            {
                //No Records
                dr.Close();
                Conn.Close();
                return;
            }
            txtFirstName.Text = dr["FirstName"].ToString();
            txtLastName.Text = dr["LastName"].ToString();
            txtEmailA.Text = dr["Email"].ToString();
            txtEmailB.Text = dr["Email"].ToString();

            txtPasswordA.Text = dr["Password"].ToString();
            txtPasswordB.Text = dr["Password"].ToString();
            txtAddress1.Text = dr["Address1"].ToString();
            txtAddress2.Text = dr["Address2"].ToString();
            txtCity.Text = dr["City"].ToString();
            txtState.Text = dr["State"].ToString();
            txtZip.Text = dr["Zip"].ToString();
            txtFax.Text = dr["Fax"].ToString();
            txtPhone.Text = dr["Phone"].ToString();


            dr.Close();
            Conn.Close();
        }
    }
    protected void cuvPassword_ServerValidate(object source, ServerValidateEventArgs e)
    {
        if (e.Value.Length == 6)
            e.IsValid = true;
        else
            e.IsValid = false;
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        Response.Redirect("Customers.aspx");
    }



    protected void btnUpdate_Click(object sender, EventArgs e)
    {

        if (Request.QueryString["id"] == null)
        {


         

             
                 //Declare the connection object
                 SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

                 //Make the connection 
                 Conn.Open();

                 //Define you query
                 string sqlQuery = " UPDATE [db_owner].[Customer] SET [FirstName] = @FirstName, [LastName] = @LastName, [Email] = @Email, [Password] = @Password, [Address1] = @Address1, [Address2] = @Address2, [City] = @City, [State] = @State, [zip] = @zip, [Phone] = @Phone, [Fax] = @Fax  WHERE CustomerID=@CustomerID";
                // string sql = "UPDATE [dbo].[Customer] SET [FirstName] = @FirstName   WHERE CustomerID=@CustomerID";
                 //string sql = "UPDATE [Customer] SET [FirstName] = @FirstName,  [LastName] = @LastName, [Email] = @Email, [Password] = @Password, [Address1] = @Address1, [Address2] = @Address2, [City] = @City, [State] = @State, [zip] = @zip, [Phone] = @Phone, [Fax] = @Fax  WHERE CustomerID=@CustomerID";
                 // string sql = "UPDATE [db_owner].[Customer] SET FirstName=@FirstName, LastName=@LastName, Email=@Email, Password=@Password, Address1=@Address1, Address2=@Address2, City=@City, State=@State, Zip=@Zip, Phone=@Phone, Fax=@Fax Where CustomerID=@CustomerID";
                 //string sql = "INSERT INTO Customer(FirstName, LastName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax) VALUES(@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax) Where CustomerID=@CustomerID";
                 // string sql = "INSERT INTO [Customer] ([FirstName], [LastName], [Email], [Password], [Address1], [Address2], [City], [State], [Zip], [Phone], [Fax]) VALUES (@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax)";
                 //Declare the Command
                 SqlCommand cmd = new SqlCommand(sqlQuery, Conn);

                 //Add the parameters needed for the SQL query
                 cmd.Parameters.AddWithValue("@cusID", CustomerID);
                 cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
                  cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
                  cmd.Parameters.AddWithValue("@Email", txtEmailA.Text);
                  cmd.Parameters.AddWithValue("@Password", txtPasswordA.Text);
                  cmd.Parameters.AddWithValue("@Address1", txtAddress1.Text);
                  cmd.Parameters.AddWithValue("@Address2", txtAddress2.Text);
                   cmd.Parameters.AddWithValue("@City", txtCity.Text);
                  cmd.Parameters.AddWithValue("@State", txtState.Text);
                   cmd.Parameters.AddWithValue("@Zip", txtZip.Text);
                  cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);
                   cmd.Parameters.AddWithValue("@Fax", txtFax.Text);



                 //Execute the query

                 cmd.ExecuteNonQuery();

                 Conn.Close();
                 Response.Redirect("Customers.aspx");
             
         

        }

        if (Request.QueryString["id"] == null)
        {

            //Declare the connection object
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

            //Make the connection 
            Conn.Open();

            //Define you query
            //string sql = "UPDATE [db_owner].[Customer] SET FirstName=@FirstName, LastName=@LastName, Email=@Email, Password=@Password, Address1=@Address1, Address2=@Address2, City=@City, State=@State, Zip=@Zip, Phone=@Phone, Fax=@Fax  Where CustomerID=@CustomerID";
            string sql = "INSERT INTO Customer(FirstName, LastName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax) VALUES(@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax)";
            //string sqlN = "INSERT INTO [Customer] ([FirstName], [LastName], [Email], [Password], [Address1], [Address2], [City], [State], [Zip], [Phone], [Fax]) VALUES (@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax)";
            //Declare the Command
            SqlCommand cmd = new SqlCommand(sql, Conn);

            //Add the parameters needed for the SQL query
            cmd.Parameters.AddWithValue("@CustomerID", ID);
            cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
            cmd.Parameters.AddWithValue("@Email", txtEmailA.Text);
            cmd.Parameters.AddWithValue("@Password", txtPasswordA.Text);
            cmd.Parameters.AddWithValue("@Address1", txtAddress1.Text);
            cmd.Parameters.AddWithValue("@Address2", txtAddress2.Text);
            cmd.Parameters.AddWithValue("@City", txtCity.Text);
            cmd.Parameters.AddWithValue("@State", txtState.Text);
            cmd.Parameters.AddWithValue("@Zip", txtZip.Text);
            cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);
            cmd.Parameters.AddWithValue("@Fax", txtFax.Text);



            //Execute the query

            cmd.ExecuteNonQuery();

            Conn.Close();
            Response.Redirect("Customers.aspx");

        }
    }  

    }

this is the section that doesn't work

protected void btnUpdate_Click(object sender, EventArgs e)
    {

        if (Request.QueryString["id"] == null)
        {


         

             
                 //Declare the connection object
                 SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

                 //Make the connection 
                 Conn.Open();

                 //Define you query
                 string sqlQuery = " UPDATE [db_owner].[Customer] SET [FirstName] = @FirstName, [LastName] = @LastName, [Email] = @Email, [Password] = @Password, [Address1] = @Address1, [Address2] = @Address2, [City] = @City, [State] = @State, [zip] = @zip, [Phone] = @Phone, [Fax] = @Fax  WHERE CustomerID=@CustomerID";
                // string sql = "UPDATE [dbo].[Customer] SET [FirstName] = @FirstName   WHERE CustomerID=@CustomerID";
                 //string sql = "UPDATE [Customer] SET [FirstName] = @FirstName,  [LastName] = @LastName, [Email] = @Email, [Password] = @Password, [Address1] = @Address1, [Address2] = @Address2, [City] = @City, [State] = @State, [zip] = @zip, [Phone] = @Phone, [Fax] = @Fax  WHERE CustomerID=@CustomerID";
                 // string sql = "UPDATE [db_owner].[Customer] SET FirstName=@FirstName, LastName=@LastName, Email=@Email, Password=@Password, Address1=@Address1, Address2=@Address2, City=@City, State=@State, Zip=@Zip, Phone=@Phone, Fax=@Fax Where CustomerID=@CustomerID";
                 //string sql = "INSERT INTO Customer(FirstName, LastName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax) VALUES(@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax) Where CustomerID=@CustomerID";
                 // string sql = "INSERT INTO [Customer] ([FirstName], [LastName], [Email], [Password], [Address1], [Address2], [City], [State], [Zip], [Phone], [Fax]) VALUES (@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax)";
                 //Declare the Command
                 SqlCommand cmd = new SqlCommand(sqlQuery, Conn);

                 //Add the parameters needed for the SQL query
                 cmd.Parameters.AddWithValue("@cusID", CustomerID);
                 cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
                  cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
                  cmd.Parameters.AddWithValue("@Email", txtEmailA.Text);
                  cmd.Parameters.AddWithValue("@Password", txtPasswordA.Text);
                  cmd.Parameters.AddWithValue("@Address1", txtAddress1.Text);
                  cmd.Parameters.AddWithValue("@Address2", txtAddress2.Text);
                   cmd.Parameters.AddWithValue("@City", txtCity.Text);
                  cmd.Parameters.AddWithValue("@State", txtState.Text);
                   cmd.Parameters.AddWithValue("@Zip", txtZip.Text);
                  cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);
                   cmd.Parameters.AddWithValue("@Fax", txtFax.Text);



                 //Execute the query

                 cmd.ExecuteNonQuery();

                 Conn.Close();
                 Response.Redirect("Customers.aspx");
             
         

        }

        if (Request.QueryString["id"] == null)
        {

            //Declare the connection object
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);

            //Make the connection 
            Conn.Open();

            //Define you query
            //string sql = "UPDATE [db_owner].[Customer] SET FirstName=@FirstName, LastName=@LastName, Email=@Email, Password=@Password, Address1=@Address1, Address2=@Address2, City=@City, State=@State, Zip=@Zip, Phone=@Phone, Fax=@Fax  Where CustomerID=@CustomerID";
            string sql = "INSERT INTO Customer(FirstName, LastName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax) VALUES(@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax)";
            //string sqlN = "INSERT INTO [Customer] ([FirstName], [LastName], [Email], [Password], [Address1], [Address2], [City], [State], [Zip], [Phone], [Fax]) VALUES (@FirstName, @LastName, @Email, @Password, @Address1, @Address2, @City, @State, @Zip, @Phone, @Fax)";
            //Declare the Command
            SqlCommand cmd = new SqlCommand(sql, Conn);

            //Add the parameters needed for the SQL query
            cmd.Parameters.AddWithValue("@CustomerID", ID);
            cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
            cmd.Parameters.AddWithValue("@Email", txtEmailA.Text);
            cmd.Parameters.AddWithValue("@Password", txtPasswordA.Text);
            cmd.Parameters.AddWithValue("@Address1", txtAddress1.Text);
            cmd.Parameters.AddWithValue("@Address2", txtAddress2.Text);
            cmd.Parameters.AddWithValue("@City", txtCity.Text);
            cmd.Parameters.AddWithValue("@State", txtState.Text);
            cmd.Parameters.AddWithValue("@Zip", txtZip.Text);
            cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);
            cmd.Parameters.AddWithValue("@Fax", txtFax.Text);



            //Execute the query

            cmd.ExecuteNonQuery();

            Conn.Close();
            Response.Redirect("Customers.aspx");

        }
    }

.net:c#

5 Contributors
9 Replies
11K Views
6 Years Discussion Span
Latest Post 7 Years Ago Latest Post by vishal_30

Dani AI

Generated 11 Months Ago

Common causes of a silent no-op UPDATE in this pattern are all present here: (1) rebinding the form on every postback (you already fixed with !IsPostBack), (2) logic that never enters the update path because both branches check id == null instead of splitting insert vs. update, and (3) a parameter name mismatch. Your SQL uses WHERE CustomerID=@CustomerID, but the code adds @cusID. ADO.NET matches by name, so the WHERE parameter is never supplied. Also ensure the querystring id is parsed to the correct type and that you inspect the rows affected to detect a missing record.

A robust approach is:

Only load the record on first load: if (!IsPostBack && int.TryParse(Request.QueryString["id"], out var id)) { ... } (Page.IsPostBack).
In the update click, require an id for updates; otherwise branch to insert. Use consistent parameter names and types, and verify ExecuteNonQuery result.

Example skeleton:

protected void btnUpdate_Click(object sender, EventArgs e)
{
    if (!int.TryParse(Request.QueryString["id"], out var id)) { /* insert path */ return; }

    using var con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
    using var cmd = new SqlCommand("UPDATE db_owner.Customer SET FirstName=@FirstName, LastName=@LastName WHERE CustomerID=@CustomerID", con);
    cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = id;
    cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = txtFirstName.Text;
    cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = txtLastName.Text;
    con.Open();
    var rows = cmd.ExecuteNonQuery(); // 0 means id not found
}

Avoid AddWithValue when types/lengths matter (parameter types), and check ExecuteNonQuery’s return for success (docs). Also, never store or echo plaintext passwords.

All 9 Replies

hericles 289 Master Poster

14 Years Ago

You have two if statements that check if the query string is empty or null but not one that checks if it isn't. Therefore, if the query string["ID"] is set to some value nothing will happen.
Your first check needs to be

 if (Request.QueryString["id"] != null)

Then the code that uses the id in the SQL statement will run.

Edited 13 Years Ago by Dani because: Fixed formatting

hericles 289 Master Poster

14 Years Ago

OK, any errors getting displayed? You may need to debug your code for the update function and step through it until you find the line that is causing the problem.

hericles 289 Master Poster

14 Years Ago

If there are no errors and the code runs then there is something wrong with the SQL statement. Most likely cause is that the @ID you pass in isn't found and so nothing can be updated.
From what I've seen your code is OK so double check all values as you step through it and pay close attention to the SQl statement.

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.

mike888 -2 Newbie Poster · Answer 1 · 2012-03-10T03:23:00+00:00

I fixed it and I also convert the query string to an intger. So it match the id in the database but it still doesn't work

mike888 -2 Newbie Poster · Answer 2 · 2012-03-10T07:08:03+00:00

I don't get any errors .when I click update. IT takes me back to the customer.aspx page.but when I check the data it still not updated.

mike888 -2 Newbie Poster · Answer 3 · 2012-03-11T22:24:57+00:00

I found the problem .
i put if (!IsPostBack) in the page load. and that fixed the problem ,
thank you

skkakde 0 Newbie Poster · Answer 4 · 2012-10-10T06:50:28+00:00

Hi all,
Thanks for your replies to this post it really helps me to solv my problems

raya bp 0 Newbie Poster · Answer 5 · 2012-11-19T05:57:15+00:00

SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
conn.open();

vishal_30 0 Newbie Poster · Answer 6 · 2019-02-19T11:38:20+00:00

try using sqlAdapter rathe than sqlcommand
For Example follow below code and it's updating the data in database.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["projectdashboardConnectionString"].ConnectionString);

            con.Open();
            string update = " UPDATE [Project]  SET [Project_Name]  = @Project_Name ,[Project_StartDate]  = @Project_StartDate  ,[Project_ExpectedEndDate]  = @Project_ExpectedEndDate  ,[Project_ActualEndDate]  = @Project_ActualEndDate  ,[Project_UpdateDate]  = @Project_UpdateDate  ,[Project_ClosureDate]  = @Project_ClosureDate  ,[Project_Requirement]  = @Project_Requirement  ,[Project_Subscription]  = @Project_Subscription  ,[MasterProjectStatus_ID]  = @MasterProjectStatus_ID  ,[Client_ID]  = @Client_ID  ,[Project_CreationDate]  = @Project_CreationDate  ,[ProjectType_ID]  = @ProjectType_ID  ,[Remarks]  = @Remarks  ,[Project_Percentage_Completion]  = @Project_Percentage_Completion  ,[SalesManager_ID]  = @SalesManager_ID  ,[ProjectManager_ID]  = @ProjectManager_ID  ,[TechLead_ID]  = @TechLead_ID  ,[Partner_ID]  = @Partner_ID   WHERE [Project_Name]  = @Project_Name   ";

            SqlDataAdapter sqlda = new SqlDataAdapter(update, con);

            sqlda.SelectCommand.Parameters.AddWithValue("@Project_Name", TextBoxProjectName.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_StartDate", TextBox1.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_ExpectedEndDate",TextBox2.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_ActualEndDate", TextBox3.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_UpdateDate", TextBox4.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_ClosureDate", TextBox5.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_Requirement", TextBoxProjectRequirement.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_Subscription", TextBoxProjectSubscription.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@MasterProjectStatus_ID", DropDownList1.SelectedValue);
            sqlda.SelectCommand.Parameters.AddWithValue("@Client_ID", DropDownList2.SelectedValue);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_CreationDate", DateTime.Now.ToString());
            sqlda.SelectCommand.Parameters.AddWithValue("@ProjectType_ID", DropDownList3.SelectedValue);
            sqlda.SelectCommand.Parameters.AddWithValue("@Remarks", TextBoxRemarks.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@Project_Percentage_Completion", TextBoxProjectPercentageCompletion.Text);
            sqlda.SelectCommand.Parameters.AddWithValue("@SalesManager_ID", DropDownList4.SelectedValue);
            sqlda.SelectCommand.Parameters.AddWithValue("@ProjectManager_ID", DropDownList5.SelectedValue);
            sqlda.SelectCommand.Parameters.AddWithValue("@TechLead_ID", DropDownList6.SelectedValue);
            sqlda.SelectCommand.Parameters.AddWithValue("@Partner_ID", DropDownList7.SelectedValue);

            sqlda.SelectCommand.ExecuteNonQuery();
            con.Close();
            Response.Redirect("displayupdateproject.aspx");

        }

C# ASP.net Update Data into a SQL server database

Dani AI

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers