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

Question

mike888 -2 Newbie Poster

12 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");

        }
    }

asp.net c# client-server sql

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

hericles 289 Master Poster

12 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 11 Years Ago by Dani because: Fixed formatting

hericles 289 Master Poster

12 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

12 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");

        }