SqlCommand UPDATE problem...

[Nuzzi]

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_Click(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.Parameters.Add("@title", SqlDbType.NVarChar).Value =
tb_title.Text;
id_cmd.Parameters.Add("@body", SqlDbType.NText).Value =
fb_item.Text;
id_cmd.Parameters.Add("@descriptor", SqlDbType.NText).Value =
fb_descriptor.Text;
id_cmd.Parameters.Add("@item", SqlDbType.Int).Value = item_id;

con.Open();
Int32 count = id_cmd.ExecuteNonQuery();
con.Close();

if ( count 0 )
{
// Step 2
id_cmd.Parameters.Clear();
id_cmd.CommandText = "UPDATE web_news SET news_category_id =
@cat_id, news_published = @publish, news_user_id = @user_id " +
"WHERE news_id = @news";

id_cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value =
Convert.ToInt32(dd_cat.SelectedValue);
id_cmd.Parameters.Add("@publish", SqlDbType.Bit).Value =
cb_publish.Checked;
id_cmd.Parameters.Add("@user_id", SqlDbType.Int).Value =
Convert.ToInt32(dd_author.SelectedValue);
id_cmd.Parameters.Add("@news", SqlDbType.Int).Value = news_id;

con.Open();
count = id_cmd.ExecuteNonQuery();
con.Close();

if (count 0)
Response.Redirect("news.aspx?status=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_Click(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.Parameters.Add("@title", SqlDbType.NVarChar).Value =
tb_title.Text;
id_cmd.Parameters.Add("@body", SqlDbType.NText).Value =
fb_item.Text;
id_cmd.Parameters.Add("@item", SqlDbType.Int).Value = item_id;

con.Open();
Int32 count = id_cmd.ExecuteNonQuery();
con.Close();

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

id_cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value =
tb_title.Text;
id_cmd.Parameters.Add("@product", SqlDbType.Int).Value =
product_id;

con.Open();
count = id_cmd.ExecuteNonQuery();
con.Close();

if (count 0)
Response.Redirect("products.aspx?status=updated");
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

[Alexey Smirnov]

On May 8, 9:37 pm, Nuzzi <[e-mail]_nospamwrote:

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_Click(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.Parameters.Add("@title", SqlDbType.NVarChar).Value =
tb_title.Text;
id_cmd.Parameters.Add("@body", SqlDbType.NText).Value =
fb_item.Text;
id_cmd.Parameters.Add("@descriptor", SqlDbType.NText).Value =
fb_descriptor.Text;
id_cmd.Parameters.Add("@item", SqlDbType.Int).Value = item_id;

con.Open();
Int32 count = id_cmd.ExecuteNonQuery();
con.Close();

if ( count 0 )
{
// Step 2
id_cmd.Parameters.Clear();
id_cmd.CommandText = "UPDATE web_news SET news_category_id =
@cat_id, news_published = @publish, news_user_id = @user_id " +
"WHERE news_id = @news";

id_cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value =
Convert.ToInt32(dd_cat.SelectedValue);
id_cmd.Parameters.Add("@publish", SqlDbType.Bit).Value =
cb_publish.Checked;
id_cmd.Parameters.Add("@user_id", SqlDbType.Int).Value =
Convert.ToInt32(dd_author.SelectedValue);
id_cmd.Parameters.Add("@news", SqlDbType.Int).Value = news_id;

con.Open();
count = id_cmd.ExecuteNonQuery();
con.Close();

if (count 0)
Response.Redirect("news.aspx?status=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_Click(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.Parameters.Add("@title", SqlDbType.NVarChar).Value =
tb_title.Text;
id_cmd.Parameters.Add("@body", SqlDbType.NText).Value =
fb_item.Text;
id_cmd.Parameters.Add("@item", SqlDbType.Int).Value = item_id;

con.Open();
Int32 count = id_cmd.ExecuteNonQuery();
con.Close();

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

id_cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value =
tb_title.Text;
id_cmd.Parameters.Add("@product", SqlDbType.Int).Value =
product_id;

con.Open();
count = id_cmd.ExecuteNonQuery();
con.Close();

if (count 0)
Response.Redirect("products.aspx?status=updated");
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.

[Nuzzi]

On Tue, 08 May 2007 16:18:37 -0400, Alexey Smirnov
<al************@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

[Alexey Smirnov]

On May 8, 11:18 pm, Nuzzi <[e-mail]_nospamwrote:

On Tue, 08 May 2007 16:18:37 -0400, Alexey Smirnov

<[e-mail]:

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?status=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 :-)

[Roland Dick]

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

[Nuzzi]

On Wed, 09 May 2007 00:30:10 -0400, Roland Dick <br*****@web.dewrote:

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

[Alexey Smirnov]

On May 10, 6:38 pm, Nuzzi <[e-mail]_nospamwrote:

On Wed, 09 May 2007 00:30:10 -0400, Roland Dick <[e-mail]:

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 misunderstanding 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 :-)