C# - Issue with update statements to SQL Server

Question

0.00/5 (No votes)

See more:

VS2012

Hi everyone,

I am in the process of testing update statements to SQL using C#.
I am finding that the update statements pass, but the db doesn't get updated.

Am I missing a commit (or something similar) ?

Here is what I have so far:

SQL

try
{
      SqlCeCommand updateKeyCommand = new SqlCeCommand("UPDATE testtable SET Test_Col_1 = '" + resultingString + "' WHERE Test_Col_1 = '" + replaceKeyCommand_dr.GetString(0) + "'", conn);
      
      updateKeyCommand.CommandType = CommandType.Text;
      updateKeyCommand.ExecuteNonQuery();

      MessageBox.Show("Successful...");
}
catch (Exception ex)
{
      MessageBox.Show(ex.Message);
}

No errors are being thrown, but the db doesn't get updated according to what I'm seeing.
Can anyone shed some light as to what I'm missing?

Posted 7-Mar-13 3:40am

joshrduncan2012

Updated 7-Mar-13 4:04am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

[no name] 7-Mar-13 9:50am

The only time I have ever seen this is when you are connecting to one database and updating it but verifying the data in another database. That is, you might be looking at a copy of the database.

Zoltán Zörgő 7-Mar-13 9:54am

And are you sure that the dml statement you build with concatenation is the one you think you need? Build it as string, copy it during a debug session, paste in management studio and see how it works.

ZurdoDev 7-Mar-13 10:26am

As Zoltan pointed out, look to see what the actual SQL is.

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)

sorawit amorn · Answer 1 · 2013-03-07T04:48:00

(1) Make sure that you update an existing table/column/row
(2) Make sure that the command that you use does not contain the character (') because sql will not be able to recognize the syntax as a command.
For example :

SQL

"UPDATE testtable SET Test_Col_1 = 'someString' WHERE Test_Col_1 = 'someSt'ring'

So what you could do in this is to write a method to make sure that (') will never be stand alone

This should work :

C#

private string generateQueryableString(string s)
{
	if (s == null){
	     return "";
         }

	 StringBuilder stringBuilder = new StringBuilder();

         for (int i = 0; i < s.Length; i++){
               if (s[i] == '\''){
                    if (!((((i - 1) >= 0) && s[i - 1] == s[i]) ^ 
                        (((i + 1) < s.Length) && s[i + 1] == s[i]))){
				stringBuilder.Append(s[i]);
			 }
		}
			  stringBuilder.Append(s[i]);
	    }

          return stringBuilder.ToString();

}

Side Note: It might be good for you to use, String.format() to organize your command