Unable to update database in ASP.NET

Question

I wanted to update a record to the database but it just keep reverting to its original value. Below is my code. No error was display to me either.

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CandidateConnectionString"].ConnectionString);
conn.Open();

string updateData = "UPDATE Resume SET [Work_Experience] = @work_exp, [Educational_Level] = @edu_level, [Field_Of_Study] = @field_study, [University_Name] = @uni_name, [University_Location] = @uni_locate, [Graduation_Year] = @gra_year WHERE Cand_ID = (SELECT Cand_ID FROM Candidate WHERE Cand_Username = '"+ usernamelbl.Text +"')";
SqlCommand cmd = new SqlCommand(updateData, conn);
cmd.Parameters.AddWithValue("@work_exp", Work_Exp.Text);
cmd.Parameters.AddWithValue("@edu_level", Edu_Level.SelectedItem.Text);
cmd.Parameters.AddWithValue("@field_study", Field_Study.SelectedItem.Text);
cmd.Parameters.AddWithValue("@uni_name", Uni_Name.Text);
cmd.Parameters.AddWithValue("@uni_locate", Uni_Locate.Text);
cmd.Parameters.AddWithValue("@gra_year", Year.Text);

cmd.ExecuteNonQuery();

conn.Close();

Any Problem with the code?

If you step through the code, are you positive that your code is executing? Also, are you sure you where clause is correct? If you directly execute this SQL against the database (instead of via code) what effect does it have? — mason, Nov 26 '14 at 18:57
can u put a break point after query and see usernamelbl.text is giving u the value or not . please check tht once — Friyank, Nov 26 '14 at 19:01
@mason I'm not sure whether my code is executing or not, but the `WHERE` clause is correct. I just directly execute on the database and the record update perfectly. — Chuah Cheng Jun, Nov 26 '14 at 19:02
Set a breakpoint in your code, then step through it line by line. Time to learn to use the debugger! If you're using Visual Studio, directions are [here](http://msdn.microsoft.com/en-us/library/y740d9d3.aspx). Learning to step through the code is an invaluable skill that you *must* learn to be a successful programmer. — mason, Nov 26 '14 at 19:04
Check your update query by directly executing it in sql server. — RajeshKannan, Nov 26 '14 at 19:04
@ChuahChengJun If you want to know what's happening behind ado.net and sql server while cmd.ExecuteNonQuery then use Sql Server Profiler which you can find it under tools in sql server management studio. Sql server profiler shows the query while the cmd.ExceuteNonQuery excecuted. — RajeshKannan, Nov 26 '14 at 19:07
@mason tried the debugger and usernamelbl.text did give me the value. — Chuah Cheng Jun, Nov 26 '14 at 19:11
And when you stepped through the code line by line, did you see if `cmd.ExecuteNonQuery();` was executed or not? — mason, Nov 26 '14 at 19:12
@ChuahChengJun In Sql Server Management Studio --> Tools --> Sql Server Profiler --> connect --> Run. After doing this, a window opens which displays all the query executed through c# code. So first do the above mentioned steps and then execute the c# code. — RajeshKannan, Nov 26 '14 at 19:15
Your code is massively vulnerable to [SQL Injection attacks](http://stackoverflow.com/questions/601300/what-is-sql-injection). Also, you aren't properly disposing of your connection. You should wrap it in a [using statement](http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx). — mason, Nov 26 '14 at 19:16
@mason Sorry I'm quite new to debugger, how do I identify whether is `cmd.ExecuteNonQuery();` was executed? — Chuah Cheng Jun, Nov 26 '14 at 19:16
@ChuahChengJun You step through it line by line. If it goes over that line *and* no exception is raised, then you can be sure the line executed (go watch some YouTube videos about using the VS debugger, because it's absolutely critical that you learn to use it). In which case you should listen to Rajesh who is trying to help you make sure that the SQL statement executed on the database is what you think it is. — mason, Nov 26 '14 at 19:18
@mason thank you. i just sorted out. its the page_load event problem — Chuah Cheng Jun, Nov 26 '14 at 19:25
There you go. You had no idea if/when your code was executing. Using the debugger will help you understand exactly what's going on with your code. — mason, Nov 26 '14 at 19:26

score 0 · Answer 1 · answered Nov 26 '14 at 19:25

I just found out a stupid mistake..

On the page_Load event, I have a line of code which is fetching value from the database

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CandidateConnectionString"].ConnectionString);
                conn.Open();
                string getdata = "SELECT * FROM Resume WHERE Cand_ID = (SELECT Cand_ID FROM Candidate WHERE Cand_Username = '" + usernamelbl.Text + "')";
                SqlCommand com = new SqlCommand(getdata, conn);
                SqlDataAdapter sda = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                sda.Fill(ds, "Resume");
                Work_Exp.Text = ds.Tables["Resume"].Rows[0]["Work_Experience"].ToString();
                Edu_Level.Text = ds.Tables["Resume"].Rows[0]["Educational_Level"].ToString();
                Field_Study.Text = ds.Tables["Resume"].Rows[0]["Field_Of_Study"].ToString();
                Uni_Name.Text = ds.Tables["Resume"].Rows[0]["University_Name"].ToString();
                Uni_Locate.Text = ds.Tables["Resume"].Rows[0]["University_Location"].ToString();
                Year.Text = ds.Tables["Resume"].Rows[0]["Graduation_Year"].ToString();
                conn.Close();

But I didn't include it under if(!IsPostBack) and that's the reason whenever I submit, it overwrites my current value and revert it back to the original state. Thanks to you guys who trying to help me sort out and teaches me about new stuff. :)

Now that you've got working code, your next step should be to fix the SQL Injection problem and properly wrap it in using statements as I commented above. — mason, Nov 26 '14 at 19:27

Unable to update database in ASP.NET

1 Answers1