1,105,416 Community Members

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

Member Avatar
mike888
Newbie Poster
6 posts since Jun 2011
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

        }
    }
Member Avatar
hericles
Veteran Poster
1,078 posts since Nov 2007
Reputation Points: 98 [?]
Q&As Helped to Solve: 232 [?]
Skill Endorsements: 14 [?]
Featured
 
0
 

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.

Member Avatar
mike888
Newbie Poster
6 posts since Jun 2011
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
hericles
Veteran Poster
1,078 posts since Nov 2007
Reputation Points: 98 [?]
Q&As Helped to Solve: 232 [?]
Skill Endorsements: 14 [?]
Featured
 
0
 

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.

Member Avatar
mike888
Newbie Poster
6 posts since Jun 2011
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
hericles
Veteran Poster
1,078 posts since Nov 2007
Reputation Points: 98 [?]
Q&As Helped to Solve: 232 [?]
Skill Endorsements: 14 [?]
Featured
 
0
 

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.

Member Avatar
mike888
Newbie Poster
6 posts since Jun 2011
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

skkakde
Newbie Poster
1 post since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

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

Member Avatar
raya bp
Newbie Poster
1 post since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: