SqlCommand UPDATE problem...

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Nuzzi
    #1

    SqlCommand UPDATE problem...

    Hello All,

    I have two pages that are very similar. One is working, one is not. Here
    is the code for both:

    Page 1 (Working):

    protected void btn_update_Clic k(object sender, EventArgs e)
    {
    Int32 item_id = Convert.ToInt32 (ViewState["item_id"]);
    Int32 news_id = Convert.ToInt32 (ViewState["news_id"]);

    string sql = "UPDATE web_items SET item_title = @title, item_body
    = @body, item_descriptor = @descriptor " +
    "WHERE item_id = @item";

    SqlConnection con = new SqlConnection(* My Connection String *);
    SqlCommand id_cmd = new SqlCommand(sql, con);

    id_cmd.Paramete rs.Add("@title" , SqlDbType.NVarC har).Value =
    tb_title.Text;
    id_cmd.Paramete rs.Add("@body", SqlDbType.NText ).Value =
    fb_item.Text;
    id_cmd.Paramete rs.Add("@descri ptor", SqlDbType.NText ).Value =
    fb_descriptor.T ext;
    id_cmd.Paramete rs.Add("@item", SqlDbType.Int). Value = item_id;

    con.Open();
    Int32 count = id_cmd.ExecuteN onQuery();
    con.Close();

    if ( count 0 )
    {
    // Step 2
    id_cmd.Paramete rs.Clear();
    id_cmd.CommandT ext = "UPDATE web_news SET news_category_i d =
    @cat_id, news_published = @publish, news_user_id = @user_id " +
    "WHERE news_id = @news";

    id_cmd.Paramete rs.Add("@cat_id ", SqlDbType.Int). Value =
    Convert.ToInt32 (dd_cat.Selecte dValue);
    id_cmd.Paramete rs.Add("@publis h", SqlDbType.Bit). Value =
    cb_publish.Chec ked;
    id_cmd.Paramete rs.Add("@user_i d", SqlDbType.Int). Value =
    Convert.ToInt32 (dd_author.Sele ctedValue);
    id_cmd.Paramete rs.Add("@news", SqlDbType.Int). Value = news_id;

    con.Open();
    count = id_cmd.ExecuteN onQuery();
    con.Close();

    if (count 0)
    Response.Redire ct("news.aspx?s tatus=updated") ;
    else
    litResult.Text = "An error has occurred, please see site
    Administrator." ;
    }
    else
    litResult.Text = "An error has occurred, please see site
    Administrator." ;
    }

    Page 2 : (Not Working)

    protected void btn_update_Clic k(object sender, EventArgs e)
    {
    Int32 item_id = Convert.ToInt32 (ViewState["item_id"]);
    Int32 product_id = Convert.ToInt32 (ViewState["product_id "]);

    string sql = "UPDATE web_items SET item_title = @title, item_body
    = @body " +
    "WHERE item_id = @item";

    SqlConnection con = new SqlConnection(* My Connection String*);
    SqlCommand id_cmd = new SqlCommand(sql, con);

    id_cmd.Paramete rs.Add("@title" , SqlDbType.NVarC har).Value =
    tb_title.Text;
    id_cmd.Paramete rs.Add("@body", SqlDbType.NText ).Value =
    fb_item.Text;
    id_cmd.Paramete rs.Add("@item", SqlDbType.Int). Value = item_id;

    con.Open();
    Int32 count = id_cmd.ExecuteN onQuery();
    con.Close();

    if (count 0)
    {
    // Step 2
    id_cmd.Paramete rs.Clear();
    id_cmd.CommandT ext = "UPDATE web_products SET product_name =
    @name " +
    "WHERE product_id = @product";

    id_cmd.Paramete rs.Add("@name", SqlDbType.NVarC har).Value =
    tb_title.Text;
    id_cmd.Paramete rs.Add("@produc t", SqlDbType.Int). Value =
    product_id;

    con.Open();
    count = id_cmd.ExecuteN onQuery();
    con.Close();

    if (count 0)
    Response.Redire ct("products.as px?status=updat ed");
    else
    litResult.Text = "An error has occurred, please see site
    Administrator." ;
    }
    else
    litResult.Text = "An error has occurred, please see site
    Administrator." ;

    }

    The strange part about it not working is that it redirects to the
    "Updated" page like both updates were successful, but the data is not
    changing. I have checked the DB to confirm this.

    Thank You in advance.

    NUZZI
    Tags: None
    • Alexey Smirnov
      #2
      Re: SqlCommand UPDATE problem...

      On May 8, 9:37 pm, Nuzzi <n...@smokemyto ol.com_nospamwr ote:
      Hello All,
      >
      I have two pages that are very similar. One is working, one is not. Here
      is the code for both:
      >
      Page 1 (Working):
      >
      protected void btn_update_Clic k(object sender, EventArgs e)
      {
      Int32 item_id = Convert.ToInt32 (ViewState["item_id"]);
      Int32 news_id = Convert.ToInt32 (ViewState["news_id"]);
      >
      string sql = "UPDATE web_items SET item_title = @title, item_body
      = @body, item_descriptor = @descriptor " +
      "WHERE item_id = @item";
      >
      SqlConnection con = new SqlConnection(* My Connection String *);
      SqlCommand id_cmd = new SqlCommand(sql, con);
      >
      id_cmd.Paramete rs.Add("@title" , SqlDbType.NVarC har).Value =
      tb_title.Text;
      id_cmd.Paramete rs.Add("@body", SqlDbType.NText ).Value =
      fb_item.Text;
      id_cmd.Paramete rs.Add("@descri ptor", SqlDbType.NText ).Value =
      fb_descriptor.T ext;
      id_cmd.Paramete rs.Add("@item", SqlDbType.Int). Value = item_id;
      >
      con.Open();
      Int32 count = id_cmd.ExecuteN onQuery();
      con.Close();
      >
      if ( count 0 )
      {
      // Step 2
      id_cmd.Paramete rs.Clear();
      id_cmd.CommandT ext = "UPDATE web_news SET news_category_i d =
      @cat_id, news_published = @publish, news_user_id = @user_id " +
      "WHERE news_id = @news";
      >
      id_cmd.Paramete rs.Add("@cat_id ", SqlDbType.Int). Value =
      Convert.ToInt32 (dd_cat.Selecte dValue);
      id_cmd.Paramete rs.Add("@publis h", SqlDbType.Bit). Value =
      cb_publish.Chec ked;
      id_cmd.Paramete rs.Add("@user_i d", SqlDbType.Int). Value =
      Convert.ToInt32 (dd_author.Sele ctedValue);
      id_cmd.Paramete rs.Add("@news", SqlDbType.Int). Value = news_id;
      >
      con.Open();
      count = id_cmd.ExecuteN onQuery();
      con.Close();
      >
      if (count 0)
      Response.Redire ct("news.aspx?s tatus=updated") ;
      else
      litResult.Text = "An error has occurred, please see site
      Administrator." ;
      }
      else
      litResult.Text = "An error has occurred, please see site
      Administrator." ;
      }
      >
      Page 2 : (Not Working)
      >
      protected void btn_update_Clic k(object sender, EventArgs e)
      {
      Int32 item_id = Convert.ToInt32 (ViewState["item_id"]);
      Int32 product_id = Convert.ToInt32 (ViewState["product_id "]);
      >
      string sql = "UPDATE web_items SET item_title = @title, item_body
      = @body " +
      "WHERE item_id = @item";
      >
      SqlConnection con = new SqlConnection(* My Connection String*);
      SqlCommand id_cmd = new SqlCommand(sql, con);
      >
      id_cmd.Paramete rs.Add("@title" , SqlDbType.NVarC har).Value =
      tb_title.Text;
      id_cmd.Paramete rs.Add("@body", SqlDbType.NText ).Value =
      fb_item.Text;
      id_cmd.Paramete rs.Add("@item", SqlDbType.Int). Value = item_id;
      >
      con.Open();
      Int32 count = id_cmd.ExecuteN onQuery();
      con.Close();
      >
      if (count 0)
      {
      // Step 2
      id_cmd.Paramete rs.Clear();
      id_cmd.CommandT ext = "UPDATE web_products SET product_name =
      @name " +
      "WHERE product_id = @product";
      >
      id_cmd.Paramete rs.Add("@name", SqlDbType.NVarC har).Value =
      tb_title.Text;
      id_cmd.Paramete rs.Add("@produc t", SqlDbType.Int). Value =
      product_id;
      >
      con.Open();
      count = id_cmd.ExecuteN onQuery();
      con.Close();
      >
      if (count 0)
      Response.Redire ct("products.as px?status=updat ed");
      else
      litResult.Text = "An error has occurred, please see site
      Administrator." ;
      }
      else
      litResult.Text = "An error has occurred, please see site
      Administrator." ;
      >
      }
      >
      The strange part about it not working is that it redirects to the
      "Updated" page like both updates were successful, but the data is not
      changing. I have checked the DB to confirm this.
      >
      Thank You in advance.
      >
      NUZZI
      Are you sure that the values of the controls (tb_title.Text,
      fb_item.Text etc) were changed? Try to debug to see what values they
      have.


        Comment

        • Nuzzi
          #3
          Re: SqlCommand UPDATE problem...

          On Tue, 08 May 2007 16:18:37 -0400, Alexey Smirnov
          <alexey.smirnov @gmail.comwrote :
          Are you sure that the values of the controls (tb_title.Text,
          fb_item.Text etc) were changed? Try to debug to see what values they
          have.
          >
          >
          Hello Alexey,

          Thanks for the response. Yes, I am sure the values are changing. It is
          the damnedest thing. I have tried so many different things and cannot get
          it. The only real difference is on the non-working page I am doing some
          DB stuff on page_load, but making sure to close all connections and
          readers, etc. I am ready to pull hair out...and there is not much left.

          Thanks,

          NUZZI

            Comment

            • Alexey Smirnov
              #4
              Re: SqlCommand UPDATE problem...

              On May 8, 11:18 pm, Nuzzi <n...@smokemyto ol.com_nospamwr ote:
              On Tue, 08 May 2007 16:18:37 -0400, Alexey Smirnov
              >
              <alexey.smir... @gmail.comwrote :
              Are you sure that the values of the controls (tb_title.Text,
              fb_item.Text etc) were changed? Try to debug to see what values they
              have.
              >
              Hello Alexey,
              >
              Thanks for the response. Yes, I am sure the values are changing. It is
              the damnedest thing. I have tried so many different things and cannot get
              it. The only real difference is on the non-working page I am doing some
              DB stuff on page_load, but making sure to close all connections and
              readers, etc. I am ready to pull hair out...and there is not much left.
              >
              Thanks,
              >
              NUZZI
              Well, if I were you, I would debug it (F11). The code looks good, and
              if you see the products.aspx?s tatus=updated at the end, the code is
              working. It probably means that you update another row or something
              like this.

              Try to debug.

              Try to set hard code the values.

              Try to add Response.Write( "Hello I'm Here").

              Just try :-)

                Comment

                • Roland Dick
                  #5
                  Re: SqlCommand UPDATE problem...

                  In addition, run SQL profiler and see what commands are in fact issued
                  to the database. Copy them and execute them in SQL Management studio.
                  Does it work there?

                  Roland

                    Comment

                    • Nuzzi
                      #6
                      Re: SqlCommand UPDATE problem...

                      On Wed, 09 May 2007 00:30:10 -0400, Roland Dick <brischt@web.de wrote:
                      In addition, run SQL profiler and see what commands are in fact issued
                      to the database. Copy them and execute them in SQL Management studio.
                      Does it work there?
                      >
                      Roland
                      Thanks Roland and Alexey. I got it. It was a misunderstandin g about the
                      sequence of events. I had code in the Page_Load that filled the fields.
                      The Page_Load was actually being implemented prior to the Button_Click
                      event and refilling the fields with old data and then resetting the old
                      data back in. So, like Alexey's first question about the fields
                      changing...they really weren't even though I was changing them manually.
                      I just protected it by using the IsPostBack variable.

                      Thanks Agains,

                      NUZZI

                        Comment

                        • Alexey Smirnov
                          #7
                          Re: SqlCommand UPDATE problem...

                          On May 10, 6:38 pm, Nuzzi <n...@smokemyto ol.com_nospamwr ote:
                          On Wed, 09 May 2007 00:30:10 -0400, Roland Dick <bris...@web.de wrote:
                          In addition, run SQL profiler and see what commands are in fact issued
                          to the database. Copy them and execute them in SQL Management studio.
                          Does it work there?
                          >
                          Roland
                          >
                          Thanks Roland and Alexey. I got it. It was a misunderstandin g about the
                          sequence of events. I had code in the Page_Load that filled the fields.
                          The Page_Load was actually being implemented prior to the Button_Click
                          event and refilling the fields with old data and then resetting the old
                          data back in. So, like Alexey's first question about the fields
                          changing...they really weren't even though I was changing them manually.
                          I just protected it by using the IsPostBack variable.
                          >
                          Thanks Agains,
                          >
                          NUZZI
                          great :-)

                            Comment

                            Previous template Next
                            Working...