Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
 
//this is my problem//
protected void btnUpdate_Click(object sender, EventArgs e)
    {
 
        if (Request.QueryString["MODE"] != null)
        {
          
            
        
 
          
          //int CustomerID = Convert.ToInt32(Request.QueryString["id"]);

             
 
            //Declare the connection object
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
 
            //Make the connection 
            Conn.Open();
 
            //Define you query
            //  string updateSQL = "UPDATE [dbo].[xSample]" + "  " + "Set Region = " + txtFirstName.Text + " " + "SalesID= 1000";                                  
            // 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";
     
            
            //Declare the Command
            SqlCommand cmd = new SqlCommand(sql, Conn);
 
            //Add the parameters needed for the SQL query
            cmd.Parameters.AddWithValue("@CustomerID", Customer_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");
            
            
        }
 
        
 
        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("@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");
 
        }
          
    }
the problem in this code not in the update. anyone can tell me what is wrong with it
    public int Customer_ID()
   
    {
        int CustomerID = 0;
       CustomerID= int.Parse(Request.QueryString["id"]);
 
      return CustomerID;
             
    }
 

 
 

    protected void Page_Load(object sender, EventArgs e)
    {
       this.Master.HighlightMenu = "Customers";
        
       // if (Request.QueryString["MODE"] != null)
       // {
        if (Customer_ID()!=0)
        {
        
            
                    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", Customer_ID());
 
            //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();
        }   
    }
Posted 9-Mar-12 6:06am
Mike988630
Edited 10-Mar-12 2:40am
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

my problem was i forgot to put postback in when the page load.
 

if (!IsPostBack)
{
  Permalink  
Comments
loony malik at 7-Jun-13 12:21pm
   
Thank you so much, it saved my life!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
 
Where are you setting CustomerID? If it is not set it will always be 0 and the WHERE clause will not match any record.
 
You should also learn about using statements
 
using(SqlConnection conn = new SqlConnection(...))
{
 
}
  Permalink  
v2
Comments
Mike988 at 9-Mar-12 11:13am
   
this is my problem . the customerID is equal to querystring id.
 
CustomerID = Request.QueryString["id"];
Mike988 at 9-Mar-12 11:19am
   
Do you know how i setting customerID to make it work,
i tried to put CustomerID = Request.QueryString["id"];
but it still doesn't work
Mark Nischalke at 9-Mar-12 11:31am
   
Where are you setting this? You haven't included that in the code snippet that I see.
 
QueryString is also a string. If the CustomerID in the database is not a string then you will need to convert it.
Mike988 at 9-Mar-12 11:37am
   
i updated .
CustomerID = Request.QueryString["id"];
how can i convert the querystring to int. i think that is might be the problem.
Mike988 at 9-Mar-12 11:39am
   
like that or ? int CustomerID = Convert.ToInt32(Request.QueryString["id"]);
PopeDarren at 9-Mar-12 11:25am
   
Then what is in the querystring? Are you sure that id is being passed? When you are on the page that does the update does the address look like www.someaddress.com/mypage.aspx?id=1234
Mike988 at 9-Mar-12 11:45am
   
it's like that
http://localhost:15110/Project2/CustomerEdit.aspx?MODE=E&id=1000
Mike988 at 9-Mar-12 11:53am
   
every time i select different row the id change. and it match the id in the database.
Mike988 at 9-Mar-12 11:46am
   
I really appreciate that you trying to help me. i been struggling in this h.w assignment for couple days.
Mike988 at 9-Mar-12 11:31am
   
http://localhost:15110/Project2/CustomerEdit.aspx?MODE=E&id=1000
yeah and when the page load i did this statmenet.
 

CustomerID = Request.QueryString["id"];
Label1.Text = CustomerID;
and it shows the id number correct on the label.
i also tried to set the customerID when to this label.
 
cmd.Parameters.AddWithValue("@CustomerID", Label1.Text);
Mark Nischalke at 9-Mar-12 11:39am
   
If the CustomerID field in the database is an int this will not work. This will add the @CustomerID parameter as a string and the WHERE will not work.
Mike988 at 9-Mar-12 11:43am
   
i did edited like that, but still doesn't work.
 
<pre lang="c#">
protected void btnUpdate_Click(object sender, EventArgs e)
{
 

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

int CustomerID = Convert.ToInt32(Request.QueryString["id"]);
 
//Declare the connection object
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyLocal"].ConnectionString);
 
//Make the connection
Conn.Open();
 


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

//Declare the Command
SqlCommand cmd = new SqlCommand(sql, Conn);
 
//Add the parameters needed for the SQL query
cmd.Parameters.AddWithValue("@CustomerID", 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");


 
}
</pre>
PopeDarren at 9-Mar-12 12:04pm
   
Have you stepped through your code? Is it actually getting to the update part of this page? If so, is there an error? Or is it just failing to update? Can you post your cmd.CommandText, please? Put a breakpoint on cmd.ExecuteNonQuery and either do a quick watch on the cmd object to get this, or pull it up in the immediate window using "?cmd.CommandText" and press enter. Not everyone knows what the immediate window is. If you don't, don't worry about it. Do what you know.
Mike988 at 9-Mar-12 12:24pm
   
I am new in C# , I Put a breakpoint on cmd.ExecuteNonQuery and (start debuging) and immediate window but it tell me.,
 
The expression cannot be evaluated while in run mode.
i am sorry
PopeDarren at 9-Mar-12 15:17pm
   
Is the debugger sitting on the breakpoint when you run this immediate window command?
Mike988 at 10-Mar-12 7:38am
   
i tested each code by it self. i found the update code work fine , but when i put the code before it , it make it stop, i updated my code.
i am trying to find out what is wrong with the first code (Populate the data)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 30 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100