Jedi Ablaza Jedi Ablaza - 2 months ago 15
ASP.NET (C#) Question

retrieving special characters from database to C#

i have a data in my database

'A Plus 18" Stand Fan'
without the single quote. i used nvarchar to save the data as it is, but when i try to retrieve it from the database, the data is returning
'A Plus 18\" Stand Fan'
. I tried using
WebUtility.HtmlDecode
and
HtmlUtility.Html.Decode
also
WebUtility.UrlDecode
. Can someone help me? thanks!

here is my code where i read the data from the database for comparison. the "Model" is the one i need to fix

for (int y = 0; y < dt.Rows.Count; y++)
{
model = dt.Rows[y]["ItemModel"].ToString();
string companys = "";

companys = dt.Rows[y]["Company"].ToString();



//getAMS(model, quan);
Utility a = new Utility();
string com = a.PO();
SqlConnection con = new SqlConnection(com);
SqlCommand read = con.CreateCommand();
SqlDataReader reader = null;
string stat = "Delivered";

string mod = "SELECT SUM(Quantity) as Quantity from vConsumables_Balance where Model ='" + model + "' AND Company = '" + companys + "' AND Status = 'Delivered'";

try
{
con.Open();
read.CommandText = mod;
reader = read.ExecuteReader();
}
catch (System.Exception)
{
Console.WriteLine("Error");
}

while (reader.Read())
{

quan = reader.GetDecimal(0).ToString();



}

Answer

The code line string mod = "SELECT SUM(Quantity) as Quantity from vConsumables_Balance where Model ='" + model + "' AND Company = '" + companys + "' AND Status = 'Delivered'"; shows that you are creating SQL queries by concatenating string manually. I assume you did the same when inserting the data into the database and used some tool of your own for escaping some characters.

That is a bad idea. You should change all your queries to parameterized queries. That will help you with other issues you are likely to encounter: formatting of numbers and dates, and it will also provide some protection against SQL injection attacks.

As for the Status column, I'd suggest to use an integer value representing an enumeration value instead of a string.