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

Question

0.00/5 (No votes)

See more:

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//

C#

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

C#

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

Mike988

Updated 10-Mar-12 1:40am

v5

Add a Solution

2 solutions

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

C#

using(SqlConnection conn = new SqlConnection(...))
{

}

Posted 9-Mar-12 5:11am

Not Active

Updated 9-Mar-12 5:13am

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

[no name] 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);

[no name] 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)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mike988 · Accepted Answer · 2012-03-10T02:08:00

Solution 2

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

if (!IsPostBack)
{

Posted 10-Mar-12 2:08am

Mike988

Comments

loony malik 7-Jun-13 12:21pm

Thank you so much, it saved my life!