Increment the value of a record in a table through sql. (simple)

Question

4.00/5 (1 vote)

See more:

SqlConnection connection = new SqlConnection(ConnectionString.Conn);
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "UPDATE MS4 SET Views = Views+1 WHERE SheetName='" +textBox2.Text + "'"; //updates the notes coloumn in MS4
SqlDataReader dataread = command.ExecuteReader(); //executes command

this is the code I currently have, it doesn't work well for me.
what am I doing wrong please? I don't want it to be an auto increment.

Posted 24-May-11 7:13am

LighthouseCall

Add a Solution

Comments

dbrenth 24-May-11 13:30pm

Please define "it doesn't work well for me." That could mean anything, Including that the table keeps disappearing everytime you enter "' DROP TABLE MS4 --" in the textbox.

LighthouseCall 24-May-11 13:34pm

' DROP TABLE MS4 -- ? Didn't get that part.
By it doesn't work well for me, I mean that the table is fine, but the value does not increment as it should.

dbrenth 24-May-11 13:47pm

I am just warning you of potential sql injection attacks. You may want to look at using SqlParameter's instead of just blindly throwing in whatever a user types in at the text box.

Anyway, do you get a better result when you try command.ExecuteNonQuery();?

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)

OriginalGriff · Answer 1 · 2011-05-24T08:27:00

As dbrenth has said: use ExecuteNonQuery rather than Executereader: it should work then.

The first comment he made refers to your concatenation of strings to form the SQL staement, and that it is a dangerous thing to do: Not only can it make code very difficult yo read, but it can also allow accidental or deliberate SQL injection accatks which can destroy your database.

In the example he gave, if your user types "x';DROP TABLE MS4--" and you submit your query as is, SQL will interpret it as two separate commands: An UPDATE command followed by a DROP TABLE command. It will then delete (with no undo facility) your entire MS4 table.

If you think this is unlikely, it is one of the first things people tried with the UK on-line census recently...and they were mostly upset when it didn't do any damage.

There are other malicious things you can do with it, but prevention is easy:

using (SqlConnection connection = new SqlConnection(ConnectionString.Conn))
   {
   connection.Open();
   using (SqlCommand command = new SqlCommand("UPDATE MS4 SET Views = Views + 1 WHERE SheetName=@SN",connection))
      {
      command.Parameters.AddWithValue("@SN", textBox2.Text);
      command.ExecuteNonQuery();
      }
   }

Please note that SQL connections and so forth are scarce resources, and you are responsible for disposing of those you create. The above is probably the easiest way.

[edit]Typo: should have been SqlCommand, typed SqlConnection :O - OriginalGriff[/edit]