I inherited an ASP.NET app that I updated today. One update was changing a SQL varchar field stored as numbers to letters, as in “1,2,3” to “A,B,C.” (I'm saying its inherited because I probably wouldn't have stored comma-separated values in a single field had I designed the app.)
As numbers, the following worked fine: “...where code in (1,5,7).“
string sql = "SELECT * FROM table WHERE code IN (" + dr["code"] + ")";
But as letters it spit out an Invalid column name 'G' exception. I needed to convert the “A,B,C“ string to 'A','B','C'.
This solution isn't clever, and as usual I would bet I get recommendations on how to improve it. I'm posting it only because it was a 10 minute (at most) coding fix which in the not too distant past would have taken me a lot longer to do. I can only attribute the decreased coding time to .NET, the economy of the C# syntax, and I'm a lucky cus who gets to write a lot of code.
string sql = "SELECT * FROM table WHERE code IN (" + GetCode(dr["code"].ToString()) + ")";
private string GetCode(string actions)
{
// actions string = “A, B, C“
string s = actions.Replace(" ","");
string m = "";
string[] arraychars = s.Split(",".ToCharArray());
for (int i = 0 ; i < arraychars.Length ; i++)
{
if (i > 0)
m += ",'" + arraychars[i].ToString() + "'";
else
m += "'" + arraychars[i].ToString() + "'";
}
return m; // returns 'A','B','C'
}
[UPDATE] I knew I'd get improvement suggestions on this. Now instead of being happy about a 10 minute code fix, I SHOULD have been happy about a 90 second fix! The updated function now consists of a single line. Excellent, Jerry!
private string GetActionChars(string actions)
{
return "'" + String.Join("', '", actions.Replace(" ", "").Split(',')) + "'";
}