8 Replies - 998 Views - Last Post: 01 July 2010 - 01:00 PM Rate Topic: -----

#1 Guest_thursdayniac*


Reputation:

Updating Database

Posted 30 June 2010 - 02:15 PM

I'm trying to create an edit account page where users can update some of their account info.
I have written code that seems like it should work to me, but it doesn't. The only part that
seems to actually work is the last few lines(changing the lbl status to green etc)

Here's my code:

protected void save_info_btn_Click(object sender, EventArgs e)
        {
            if (Page.IsValid)
            {
                int vidAdd = 0;

                if (!int.TryParse(video_address_edit_in.Text.Trim(), out vidAdd))
                {
                    edit_status_lbl.ForeColor = System.Drawing.Color.Red;
                    edit_status_lbl.Text = "Video address must be a number.";
                    return;
                }

                string connString =
                          @"Data Source=.\SQLEXPRESS;AttachDbFilename=
                          |DataDirectory|\Database1.mdf; Integrated Security=True;
                          User Instance=True";

                using (SqlConnection myConn = new SqlConnection(connString))
                {
                    try
                    {
                        myConn.Open();                                       //attempting to connect                                        
                        edit_status_lbl.Text = "Connected!";
                    }
                    catch (Exception)
                    {
                        edit_status_lbl.ForeColor = System.Drawing.Color.Red;
                        edit_status_lbl.Text = "Connection to server has failed...";
                        return;
                    }

                    SqlCommand cmd = new SqlCommand(
                                     "UPDATE Users SET FirstName = @FirstName, LastName = @LastName, JobTitle = @JobTitle, VideoAddress = @VideoAddress WHERE Email = @Email", myConn);

                    SqlParameter param1 = new SqlParameter();
                    param1.ParameterName = "@FirstName";
                    param1.Value = first_name_edit_in.Text;

                    SqlParameter param2 = new SqlParameter();
                    param2.ParameterName = "@LastName";
                    param2.Value = last_name_edit_in.Text;

                    SqlParameter param3 = new SqlParameter();
                    param3.ParameterName = "@JobTitle";
                    param3.Value = job_title_edit_in.Text;

                    SqlParameter param4 = new SqlParameter();
                    param4.ParameterName = "@VideoAddress";
                    param4.Value = video_address_edit_in.Text;

                    SqlParameter param5 = new SqlParameter();
                    param5.ParameterName = "@Email";
                    param5.Value = Session["UserSession"].ToString();

                    cmd.Parameters.AddWithValue("@FirstName", first_name_edit_in.Text);
                    cmd.Parameters.AddWithValue("@LastName", last_name_edit_in.Text);
                    cmd.Parameters.AddWithValue("@JobTitle", job_title_edit_in.Text);
                    cmd.Parameters.AddWithValue("@VideoAddress", video_address_edit_in.Text);
                    cmd.Parameters.AddWithValue("@Email", Session["UserSession"].ToString());

                    try
                    {
                        cmd.ExecuteScalar();
                    }

                    finally
                    {
                        cmd.Connection.Close();
                    }                    
                    myConn.Close();
                }

                edit_status_lbl.ForeColor = System.Drawing.Color.Green;
                edit_status_lbl.Text = "Information Saved!";

                home_btn.Visible = true;
                Make_Form_Invis();
                return;
            }
        }


Is This A Good Question/Topic? 0

Replies To: Updating Database

#2 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 421
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Updating Database

Posted 30 June 2010 - 04:26 PM

ExecuteScaler is used when running a SQL query that will return only a single value, you should be using ExecuteNonQuery.

cmd.ExecuteNonQuery();

Was This Post Helpful? 0
  • +
  • -

#3 Guest_thursdayniac*


Reputation:

Re: Updating Database

Posted 01 July 2010 - 07:23 AM

View PostJayman, on 30 June 2010 - 03:26 PM, said:

ExecuteScaler is used when running a SQL query that will return only a single value, you should be using ExecuteNonQuery.

cmd.ExecuteNonQuery();


I changed it but it still doesn't update anything in the database =(
Was This Post Helpful? 0

#4 W3bDev  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 42
  • View blog
  • Posts: 379
  • Joined: 15-March 09

Re: Updating Database

Posted 01 July 2010 - 07:41 AM

Why is there no catch implemented? If it errors out, you will get no warnings! add a catch that will output the exception if it is hit!
Was This Post Helpful? 0
  • +
  • -

#5 Guest_thursdayniac*


Reputation:

Re: Updating Database

Posted 01 July 2010 - 08:01 AM

View PostW3bDev, on 01 July 2010 - 06:41 AM, said:

Why is there no catch implemented? If it errors out, you will get no warnings! add a catch that will output the exception if it is hit!


I added a catch statement to show the error message in a label but I don't think the problem is an error.
Was This Post Helpful? 0

#6 Guest_thursdayniac*


Reputation:

Re: Updating Database

Posted 01 July 2010 - 08:20 AM

Something that I left out:
Before this function is called, in the page_load I set the text box's to display what is already in the datadase. To change the info the user deletes what is in the text box and fills in what they want to change. Could this be the problem?

Here is my code in the page_load:

protected void Page_Load(object sender, EventArgs e)
        {
            string connString =
                   @"Data Source=.\SQLEXPRESS;AttachDbFilename=
                   |DataDirectory|\Database1.mdf; Integrated Security=True;
                   User Instance=True";

            using (SqlConnection myConn = new SqlConnection(connString))
            {
                try
                {
                    myConn.Open();
                }
                catch
                {
                    edit_status_lbl.ForeColor = System.Drawing.Color.Red;
                    edit_status_lbl.Text = "ERROR: COULD NOT CONNECT TO SERVER";
                    return;
                }
                
                SqlCommand cmd = new SqlCommand(
                           "SELECT * FROM Users WHERE Email = @Email", myConn);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@Email";
                param1.Value = Session["UserSession"].ToString();

                cmd.Parameters.Add(param1);

                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    first_name_edit_in.Text = reader["FirstName"].ToString();
                    last_name_edit_in.Text = reader["LastName"].ToString();
                    job_title_edit_in.Text = reader["JobTitle"].ToString();
                    video_address_edit_in.Text = reader["VideoAddress"].ToString();
                }

                reader.Close();
                myConn.Close();
            }
        }


Was This Post Helpful? 0

#7 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 205
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Updating Database

Posted 01 July 2010 - 11:28 AM

I don't see anything that jumps out at me (now that you've said you've made some changes).
Could you please post your current code so that we can see what you're working with right now.
Guessing at what you've done is not going to help us to help you.

Have you tried stepping through the application to see where it fails, what stage it gets to etc?
The ExecuteNonQuery() method returns an integer that represents the number of rows effected by the sql statement. You can use this number to see if any rows were updated.

-Frinny
Was This Post Helpful? 0
  • +
  • -

#8 Guest_thursdayniac*


Reputation:

Re: Updating Database

Posted 01 July 2010 - 12:42 PM

View PostFrinavale, on 01 July 2010 - 10:28 AM, said:

I don't see anything that jumps out at me (now that you've said you've made some changes).
Could you please post your current code so that we can see what you're working with right now.
Guessing at what you've done is not going to help us to help you.

Have you tried stepping through the application to see where it fails, what stage it gets to etc?
The ExecuteNonQuery() method returns an integer that represents the number of rows effected by the sql statement. You can use this number to see if any rows were updated.

-Frinny


Well I now know what the problem is, I just dont know how to fix it.
Here is what I am trying to do:
When a user goes to the Edit Account page, There are 4 fields that they can edit: First name, lastname, video address and job title. I have it to where when you first go to the page, the textboxes are all filled in with what is currently in the database. For example, the first name field would have your first name filled in as it is in the database. The idea is that if there is anything the user wants to edit, they just replace what is in the textbox with what they want it to be updated to.
Now, my update code works fine if I comment out the part that fills in the text boxes when the page loads... but if I dont comment this part out... it seems to ignore what the user puts in the textbox and just updates the database with what it had originally had. So if, for example I go to the account edit page and for first name it says: Zach and I want to delete this and put Zachary, it updates the database again with Zach -- it doesnt seem to recognize the changed text. Sorry for the long post.
Here is my code:

//CODE FOR PAGE LOAD -- FILLS IN TEXT BOXES WITH EXISTING INFO
        protected void Page_Load(object sender, EventArgs e)
        {
            string connString =
                   @"Data Source=.\SQLEXPRESS;AttachDbFilename=
                   |DataDirectory|\Database1.mdf; Integrated Security=True;
                   User Instance=True";

            using (SqlConnection myConn = new SqlConnection(connString))
            {
                try
                {
                    myConn.Open();
                }
                catch
                {
                    edit_status_lbl.ForeColor = System.Drawing.Color.Red;
                    edit_status_lbl.Text = "ERROR: COULD NOT CONNECT TO SERVER";
                    return;
                }
                
                SqlCommand cmd = new SqlCommand(
                           "SELECT * FROM Users WHERE Email = @Email", myConn);

                SqlParameter param1 = new SqlParameter();
                param1.ParameterName = "@Email";
                param1.Value = Session["UserSession"].ToString();

                cmd.Parameters.Add(param1);

                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    first_name_edit_in.Text = reader["FirstName"].ToString();
                    last_name_edit_in.Text = reader["LastName"].ToString();
                    job_title_edit_in.Text = reader["JobTitle"].ToString();
                    video_address_edit_in.Text = reader["VideoAddress"].ToString();
                }

                reader.Close();
                myConn.Close();
            }
        }

        //CODE FOR SAVE BUTTON -- IT SHOULD UPDATE DATABASE WITH CHANGED TEXT -- BUT DOESNT
        //RECOGNIZE THE USER'S INPUT, ONLY WHAT WAS ORIGINALLY IN THE TEXTBOXES
        protected void save_info_btn_Click(object sender, EventArgs e)
        {
            if (Page.IsValid)
            {
                int vidAdd = 0;

                if (!int.TryParse(video_address_edit_in.Text.Trim(), out vidAdd))
                {
                    edit_status_lbl.ForeColor = System.Drawing.Color.Red;
                    edit_status_lbl.Text = "Video address must be a number.";
                    return;
                }

                string connString =
                          @"Data Source=.\SQLEXPRESS;AttachDbFilename=
                          |DataDirectory|\Database1.mdf; Integrated Security=True;
                          User Instance=True";

                using (SqlConnection myConn = new SqlConnection(connString))
                {
                    try
                    {
                        myConn.Open();                                       //attempting to connect                                        
                        edit_status_lbl.Text = "Connected!";
                    }
                    catch (Exception)
                    {
                        edit_status_lbl.ForeColor = System.Drawing.Color.Red;
                        edit_status_lbl.Text = "Connection to server has failed...";
                        return;
                    }

                    SqlCommand cmd = new SqlCommand(
                                     @"UPDATE Users SET FirstName = @FirstName, LastName = @LastName, 
                                     JobTitle = @JobTitle, VideoAddress = @VideoAddress WHERE Email = @Email", myConn);

                    SqlParameter param1 = new SqlParameter();
                    param1.ParameterName = "@FirstName";
                    param1.Value = first_name_edit_in.Text;

                    SqlParameter param2 = new SqlParameter();
                    param2.ParameterName = "@LastName";
                    param2.Value = last_name_edit_in.Text;

                    SqlParameter param3 = new SqlParameter();
                    param3.ParameterName = "@JobTitle";
                    param3.Value = job_title_edit_in.Text;

                    SqlParameter param4 = new SqlParameter();
                    param4.ParameterName = "@VideoAddress";
                    param4.Value = video_address_edit_in.Text;

                    SqlParameter param5 = new SqlParameter();
                    param5.ParameterName = "@Email";
                    param5.Value = Session["UserSession"].ToString();

                    cmd.Parameters.AddWithValue("@FirstName", first_name_edit_in.Text);
                    cmd.Parameters.AddWithValue("@LastName", last_name_edit_in.Text);
                    cmd.Parameters.AddWithValue("@JobTitle", job_title_edit_in.Text);
                    cmd.Parameters.AddWithValue("@VideoAddress", video_address_edit_in.Text);
                    cmd.Parameters.AddWithValue("@Email", Session["UserSession"].ToString());

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }

                    catch(Exception ex)
                    {
                        edit_status_lbl.Text = ex.Message;
                    }

                    finally
                    {
                        cmd.Connection.Close();
                    }                    
                    myConn.Close();
                }

                edit_status_lbl.ForeColor = System.Drawing.Color.Green;
                edit_status_lbl.Text = "Information Saved!";

                home_btn.Visible = true;
                Make_Form_Invis();
                return;
            }
        }


Was This Post Helpful? 0

#9 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 205
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Updating Database

Posted 01 July 2010 - 01:00 PM

Um..this sounds way too simple...but have you tried checking if the page's IsPostback?

If you check the IsPostback property and it's false then you know it's the first time the page is loaded. In this case you populate the textboxes with data from the database. If IsPostback is true, then the page is posting back to the server (it is not the first time the page is loaded) so you don't want to populate the textboxes with data from the database in this case....

-Frinny

This post has been edited by Frinavale: 01 July 2010 - 01:00 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1