Click here to Skip to main content
12,406,687 members (56,772 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server
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 5:06am
Mike988530
Updated 10-Mar-12 1: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 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 9-Mar-12 11:13am
   
this is my problem . the customerID is equal to querystring id.

CustomerID = Request.QueryString["id"];
Mike988 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 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 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 9-Mar-12 11:39am
   
like that or ? int CustomerID = Convert.ToInt32(Request.QueryString["id"]);
PopeDarren 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 9-Mar-12 11:45am
   
it's like that
http://localhost:15110/Project2/CustomerEdit.aspx?MODE=E&id=1000
Mike988 9-Mar-12 11:53am
   
every time i select different row the id change. and it match the id in the database.
Mike988 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 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 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 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 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 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 9-Mar-12 15:17pm
   
Is the debugger sitting on the breakpoint when you run this immediate window command?
Mike988 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160730.1 | Last Updated 30 May 2013
Copyright © CodeProject, 1999-2016
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