Cannot update records in SQL server

Question

3.00/5 (2 votes)

See more:

I need to update my enteries in the SQL server. Im able to delete and insert new items, but I am not able to update existing items.

Here is my code for updating items:

C#

con.Open(); 
str = "update tb set ename = '" + textBox5.Text + "', expe= '" + textBox6.Text + "', eplace='" + textBox7.Text + "' where eno='" + textBox1.Text + "' ";
cmd = new SqlCommand(str, con); 
dr = cmd.ExecuteReader(); 
MessageBox.Show("Successfully updated"); 
con.Close();

Posted 12-Aug-12 8:25am

vasanthkumarmk

Updated 12-Aug-12 8:47am

Kenneth Haugland

v3

Add a Solution

Comments

Kenneth Haugland 12-Aug-12 14:37pm

??? I do not understand... SO you want to update the SQL server, but you doint want to connect to the SQL server, Im I reading you right here?

vasanthkumarmk 12-Aug-12 14:40pm

I can able to insert,delete everything but i can't do the update

vasanthkumarmk 12-Aug-12 14:41pm

con.Open();
str = "update tb set ename = '" + textBox5.Text + "', expe= '" + textBox6.Text + "', eplace='" + textBox7.Text + "' where eno='" + textBox1.Text + "' ";
cmd = new SqlCommand(str, con);
dr = cmd.ExecuteReader();
MessageBox.Show("Successfully updated");
con.Close();

This is my update coding

Kenneth Haugland 12-Aug-12 14:48pm

I have updated your question to include your comments, if you dont agree with the update you could use the Improve Question button to revise it :)

StianSandberg 12-Aug-12 17:08pm

Your code is vulnerable for sql-injectons. You should use sql parameters. I wrote an article about this a few weeks ago: read it here

AmitGajjar 13-Aug-12 1:19am

Why you are doing executeReader instead you can use executeNonQuery.

6 solutions

Solution 6

Your sql query is correct.
Find another mistake in your code.

Suggestion 1-

C#

int affected=cmd.ExecuteNonQuery();
if(affected==1)//or affected>=1 
{
//Updated
}
else if(affected==0)
{
//Nothing has been changed.
}

Posted 12-Aug-12 20:30pm

Rock (Multithreaded)

Updated 15-Aug-12 2:19am

v3

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)

Kenneth Haugland · Accepted Answer · 2012-08-12T08:52:00

Solution 1

You should look at the documentation, as your update command looks strange to me:
http://msdn.microsoft.com/en-us/library/aa260662%28v=sql.80%29.aspx[^]

Looks like you have a white space at the end of the str line too, have you checked that the Textboxes are not empty, or have white spaces too?

Posted 12-Aug-12 8:52am

Kenneth Haugland

Updated 12-Aug-12 8:56am

v2

AshishChaudha · Accepted Answer · 2012-08-12T20:19:00

For preventing from SQL Injection always make a habbit to use parameterized query.

SQL

str = "update tb set ename = '" + @textBox5.Text + "', expe= '" + @textBox6.Text + "', eplace='" + @textBox7.Text + "' where eno='" + @textBox1.Text + "' ";


con.Open();
cmd = new SqlCommand(str, con);

cmd.Parameters.Add("@textBox5",textBox5.Text.Trim())
cmd.Parameters.Add("@textBox6",textBox6.Text.Trim())
cmd.Parameters.Add("@textBox7",textBox7.Text.Trim())
cmd.Parameters.Add("@textBox1",textBox1.Text.Trim())
int recupd=cmd.ExecuteNonQuery();

MessageBox.Show("Successfully updated");
con.Close();

Thanks
Ashish

StianSandberg · Accepted Answer · 2012-08-12T20:25:00

Watch out for sql-injections. Heres an example of your code using SqlParamters

C#

con.Open();
str = "update tb set ename=@ename, expe=@expe, eplace=@eplace where eno=@eno";
cmd = new SqlCommand(str, con);
cmd.Parameters.AddWithValue("@ename", textBox5.Text);
cmd.Parameters.AddWithValue("@expe", textBox6.Text);
cmd.Parameters.AddWithValue("@eplace", textBox7.Text);
cmd.Parameters.AddWithValue("@eno", textBox1.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("Successfully updated");
con.Close();

Easier to read and safer to execute!

Run your debugger to see if your textbox values are as expected. And run sql profiler to what your sql-command is when it is sent to your database server.

Pankaj Chamria has written an excellent article about how to debug using Visual Studio:
Advanced Debugging in Visual Studio[^]

ridoy · Accepted Answer · 2012-08-12T10:24:00

Solution 2

Your code is OK.The only problem may be here..

where eno='" + textBox1.Text + "' "

Make sure your textbox will not empty during program run or it contains the right text that you need to search your database.

Posted 12-Aug-12 10:24am

ridoy

__TR__ · Accepted Answer · 2012-08-12T19:38:00

Hi,
It looks like you are trying to use datareader to perform an update on the row.
DataReader is just used for reading the data in forward only mode. You can achieve this using Dataset but not by DataReader.

Below link should give you more information.

DataAdapters and DataReaders[^]

Also as pointed out by AlluvialDeposit your code is vulnerable to SQL Injecction[^]