c# - SQL Query with single quotes -


getting error while executing query , because column text may contain text single quotes also. how can use query w/o error code is

public bool updatecmstable(int id, string columnname, string columntext) { try { string sql = "update cmstable set " + columnname + "='" + columntext + "' cmsid=" + id; int = sqlhelper.executenonquery(connection.connectionstring, commandtype.text, sql); if (i > 0) { return true; } else { return false; } } catch (exception ee) { throw ee; } }

to fix code, escape single quotes additional single quote. however agree oded... need using parameterized query, or possibly stored proc.

public bool updatecmstable(int id, string columnname, string columntext) { if(!string.isnullorempty)) { switch(columnname) { // todo: change 50 & 100 real sizes of columns, // , column names too... case "column1": if(columntext.length > 50) columntext = columntext.substring(0, 50); break; case "column2": if(columntext.length > 100) columntext = columntext.substring(0, 100); break; etc... } } // replace single quote double single quotes columntext = columntext.replace("'", "''"); string sql = string.format("update cmstable set {0} = '{1}' cmsid={2}", columnname, columntext, id); int = sqlhelper.executenonquery(connection.connectionstring, commandtype.text, sql); return (i > 0); }

i made additional corrections code.

  1. you can return result of if statement when you're returning true | false
  2. you don't need catch exceptions if you're going throw in catch block
  3. if catch exception, meaningful it, , decide rethrow it, use throw itself, or you'll reset stack trace. don't use throw ee;
  4. replace + type concatination string.format if becomes unreadable.

edit:

the error posted happening because length of data being passed in longer specified length of column. since you're using dynamic sql, way around can see use case statement. each field may have different size, or maybe not, string have truncated fit avoid error. if field sizes same, won't need case statement.


Comments

Post a Comment

Popular posts from this blog

javascript - backbone.js Collection.add() doesn't `construct` (`initialize`) an object -

<!doctype html public "-//w3c//dtd xhtml 1.0 strict//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-strict.dtd"> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script> <script src="http://documentcloud.github.com/underscore/underscore-min.js"></script> <script src="http://documentcloud.github.com/backbone/backbone-min.js"></script> </head> <body> <button id="cmd_create_event" name="cmd_create_event" type="button">create new `event`</button> <script type="text/javascript"> var eventmodel = backbone.model.extend({ initialize : function() { console.log("`event` initialized. id: " + this.cid); this.bind("change:status", function() { console.log(this.get("status") + " value `status`"); }); this.bind("error", function(model, e...
Read more

php - Get uncommon values from two or more arrays -

is there function in php give array of uncommon values 2 or more arrays? for example: $array1 = array( "green", "red", "blue"); $array2 = array( "green", "yellow", "red"); .... $result = function_needed($array1, $array2,...); print_r($result); should give output: array("blue", "yellow", ...); use array_diff , array_merge : $result = array_merge(array_diff($array1, $array2), array_diff($array2, $array1)); here's demo. for multiple arrays, combine callback , array_reduce : function unique(&$a, $b) { return $a ? array_merge(array_diff($a, $b), array_diff($b, $a)) : $b; } $arrays = array( array('green', 'red', 'blue'), array('green', 'yellow', 'red') ); $result = array_reduce($arrays, 'unique'); and here's demo of that.
Read more

Adding duplicate array rows in Php -

i create function in php adds rows of array shared column value. so input. $test = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); and output. $testduplicatefree = array( array("c", 7, 9), array("test", 5, 6) ) ); i'm thinking function combine_duplicates($array,$col){ ... ... return $duplicatefreearray; } where $col duplicate free array. so, in case, combine_duplicates($test,0); would me desired output. this. <?php function combine_duplicates($array,$col) { $index = array(); foreach ($array $row) { $key = $row[$col]; if (!isset($index[$key])) { $index[$key] = $row; } else { ($i = 0; $i < count($row); ++$i) { if ($i != $col) { $index[$key][$i] += $row[$i]; } } } } return array_values($index); } $array = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); print_r(combine_duplicates($array, 0)); try here: http://codepad.org/mdhesqhi
Read more