Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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:
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 4:40am
v2
Comments
ThePhantomUpvoter at 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ő at 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.
ryanb31 at 7-Mar-13 10:26am
   
As Zoltan pointed out, look to see what the actual SQL is.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

(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 :
"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 :
 
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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 290
1 Jochen Arndt 165
2 DamithSL 125
3 PIEBALDconsult 110
4 Garth J Lancaster 90
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 7 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100