Cannot able to perform update

Question

1.00/5 (1 vote)

See more:

, +

I populated a table with repeater control.when the user click edit button it will redirect to next page where the form for modification is available.and the data from table is automatically populated into those fields.After update if the user click update button it should updated into the database.but in my case after clicking update it again turn back to the original data. some one plz help me.i attached my code

What I have tried:

public partial class StudEdit : System.Web.UI.Page
    {

        protected void Page_Load(object sender, EventArgs e)
        {
            String s = Request.QueryString["ID"].ToString();
            lblStudentID.Text = s;
            string constr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            SqlConnection con = new SqlConnection(constr);
            con.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(
               "select * from tbl_Student where StudentID=" + s + "", con);
                SqlDataReader sd = cmd.ExecuteReader();
                while (sd.Read())
                {
                    inputName.Text = sd.GetValue(1).ToString();
                    inputClass.Text = sd.GetValue(2).ToString();
                }
            }
            catch (SqlException ex)
            {

            }
            con.Close();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            String id = Request.QueryString["ID"];
            string constr2 = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            SqlConnection con2 = new SqlConnection(constr2);
            con2.Open();
            try
            {
                SqlCommand cmd2 = new SqlCommand("update [informationdb].[dbo].[tbl_Student] set StudentName='" + inputName.Text + "',StudentClass=" + inputClass.Text + "where StudentID=" + id + "", con2);
                cmd2.ExecuteNonQuery();
                status.Text = "Data updated sucessfully";
                cmd2.Dispose();
            }catch(Exception ex)
            {
                status.Text =""+ ex;
            }
            con2.Close();
        }
    }
}

Posted 4-Aug-18 2:15am

Member 13937760

Updated 4-Aug-18 2:53am

Add a Solution

1 solution

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)

Eric Lynch · Answer 1 · 2018-08-04T02:53:00

Solution 1

A few things...on your page load, you have a catch that simply discards any SQL exception. You should almost never do that. In general, you should either (at least) log or mitigate the exception.

In your button click, you save the exception in some status text. Does an exception occur? Could you please share the text if it does?

Some additional observation, you have resource leaks. You don't dispose of cmd at all. You don't dispose of sd. And, you don't dispose of cmd2, if an exception occurs.

I suggest you familiarize yourself with the C# using statement. A typical pattern would be:

using(var con = new SqlConnection(conStr))
{
  con.Open();
  using(var cmd = new SqlCommand(cmdStr, con))
  {
    // Do work here
  }
}

With the using statements, there is far less chance you'll accidentally introduce a resource leak.

Adding one other point, you should use SQL parameters instead of string concatenation when building your commands. Otherwise, you are a very easy target for hackers via a SQL injection attack.

See: SQL Injection | Microsoft Docs[^]

Finally, without any exception text, I cannot say definitively what your problem might be. However, I can say that your SQL update command is not valid.

At a minimum, you are missing quoting on the value of StudentClass column and you are missing a space before the WHERE command.

Posted 4-Aug-18 2:53am

Eric Lynch

Updated 4-Aug-18 2:56am

v5

Comments

Member 13937760 4-Aug-18 9:00am

that status field shows data updated successfully only. i gave space to where and StudentClass is a number that's why i not gave single quotes.

Eric Lynch 5-Aug-18 0:58am

OK. Without the space before the WHERE, which it sounds like you have now added, you would *definitely* get an exception. Hopefully, you've also corrected the many other problems I mentioned as well.

I'll add a new one. Many methods return a value. You should really check it. ExecuteNonQuery is one of those methods. It returns the number of rows that are affected (in this case updated) by the command.

If you have no exception, there are two possibilities. If the return value is non-zero, you are updating a different row than you expected. Otherwise, and more likely, if the return value is zero, then your WHERE clause is incorrect. In either case, most likely the value of "id" is not what you expect it to be.

I've been doing this a very long time. SQL is very accurate in what it reports. Assuming every thing you've mentioned is accurate, SQL will either throw an exception or return a zero value (to indicate no row was updated). The only other possibilities are that the row you expected was updated (but you didn't notice) or a different row than you expected was updated. I'll assume neither of these other possibilities is the case here (but its worth your double checking both).

The good news is that your WHERE clause is very simple. It should be easy to fix. Try either displaying the value of "id" in your "success" message or learning to use the debugger. I suggest the latter. The debugger will allow you to not only examine the "id" variable, but examine other variables and step through to check the flow of your code as well.

Regarding your code, while it is not the cause of your symptom, I'll add another problem to the list. You add the empty string ("") to the start and end of many of your strings. This is not necessary. For example, "" + "A" + "" is still only "A".

If you continue to have a problem, I suggest updating your question with all of the corrections to the code included. Also, I would update the question to include all of the missing information you fail to mention in your question. For example...add that the status field shows "Data updated successfully" and no exception occurs. And, if you check the return value, add that "the return value from ExecuteNonQuery is ?".

All of this missing information, when added, will help both you and anyone who attempts to answer your question to diagnose the problem.

Member 13937760 5-Aug-18 13:35pm

protected void Button1_Click(object sender, EventArgs e)
{
String id = Request.QueryString["ID"];
string constr2 = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
using (SqlConnection con2 = new SqlConnection(constr2))
{
con2.Open();
String query = "update tbl_Student set StudentName='@name',StudentClass=@stcls where StudentID=@stid";
using (SqlCommand cmd2 = new SqlCommand(query, con2))
{
cmd2.Parameters.AddWithValue("@name", inputName.Text.Trim());
cmd2.Parameters.AddWithValue("@stcls", inputClass.Text.Trim());
cmd2.Parameters.AddWithValue("@stid", id);
cmd2.ExecuteNonQuery();
}
}
}
here i updated it as specified by you but still when i click update button it is again change back to its old value.i have only few records so other records are not updated. i don't know why it is not updating

Eric Lynch 5-Aug-18 14:11pm

You are still not checking the return status from cmd2.ExecuteNonQuery(), as recommended. This would actually tell you if the update succeeded or not.

You are not displaying either success of failure, based on that return status. This would allow you to share that information with anyone who tries to help you. You also are still not reporting the value of the id variable, so you can check that it has the value that you think it does.

I can't tell if you've also removed the exception handling. If you have, this is also not good.

Finally, you do not want the quotes around the @name parameter. This will change the value of StudentName to the actual text '@name' instead of the parameter value.

I also suggested updating your question, not adding another comment. This might allow others to help you as well. When you do so, you should include relevant information, for the question, which is currently missing. Things like:

"no exception occurred" or "the exception text was ???" -AND-
"the return status from ExecuteNonQuery was ???"