SqlCommand UPDATE problem...

N

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
 
G

Guest

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
Click to expand...

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.
 
N

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.
Click to expand...

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
 
G

Guest

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
Click to expand...

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

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
 
N

Nuzzi

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
Click to expand...

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
 
G

Guest

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
Click to expand...

great :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

System.ApplicationError in Data access class 4
SQL Timeout -- shouldn't happen 0
Update based on SQ LDynamic Stored Proc Problem 2
problem passing parameters 2
data grid fails to update! 3
Problem with multiple update 1
Can't find a property name 1
Error Message could not find a non-generic method 6

Members online

No members online now.
Total: 92 (members: 0, guests: 92)
Robots: 456

Forum statistics

Threads
473,995
Messages
2,570,230
Members
46,818
Latest member
Brigette36

Latest Threads

Top