Storing Ascii Code in Database

Question

2.00/5 (1 vote)

See more:

Hello
I have a string variable contains Ascii Code in it .
I tried to save it in (nvachar),(Text) fields in the database but the database
adds several (?) characters to it . Why does that happens?
And how to store that variable correctly?

Here is the code

C#

using (SqlConnection connection = new SqlConnection(connectionstring))
                {
                    try
                    {
                        connection.Open();
                        string insrt_statment = "";
                        string insrt_statment2 = "";
                         string str_sign = "Ã\<4vÛeÑÊU§U£MÌ¡suV#ÌüÛ_<v";>

                        int id;
                        SqlCommand command;



                        insrt_statment = "INSERT INTO [Folders] VALUES('" + folderPath + "','" + parameters + "');SELECT SCOPE_IDENTITY();";
                        command = new SqlCommand(insrt_statment, connection);
                        command.CommandType = CommandType.Text;
                        id = Convert.ToInt32( command.ExecuteScalar());
                        insrt_statment2 = "INSERT INTO [Files] VALUES('" + fileNames[0].FullName + "','" + id + "', '" + fileNames[0].LastAccessTime + "','" + fileNames[0].LastWriteTime + "','" + str_sign + "',GETDATE());";

                        command = new SqlCommand(insrt_statment2, connection);
                        command.CommandType = CommandType.Text;
                        int aux = command.ExecuteNonQuery();


                    }
                    catch (Exception err)
                    {
                        label2.Text = "error in connection" + err.Message;
                    }
                    finally
                    {
                        if (connection.State == ConnectionState.Open)
                        {
                            connection.Close();
                        }
                    }
                }

Posted 23-Nov-13 8:46am

AhmadDb

Updated 24-Nov-13 0:42am

CHill60

v2

Add a Solution

Comments

PIEBALDconsult 23-Nov-13 15:31pm

"the database adds several (?) characters to it "

That is probably only when it is displayed (in SSMS?), try accessing the values with a program.

Also, you should probably show us the code you are using to store and retrieve the values; it may help.

1 solution

Add a Solution

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

OriginalGriff · Accepted Answer · 2013-11-24T01:14:00

The first thing to do is stop doing that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you build a new string that is sent as is direct to SQL - so when you send your special characters list like that it causes two problems:
1) It leaves you wide open to be destroying your DB.
2) it means SQL has to try to interpret your data as a "normal" text string - which it isn't, it's a Unicode string.

So, use a parameterised query, and both your problems will disappear!

C#

insrt_statment2 = "INSERT INTO [Files] VALUES(@FILENAME, @LASTATIME @LASTWTIME, @DATA, GETDATE());";
command = new SqlCommand(insrt_statment2, connection);
command.Parameters.AddWithValue("@FILENAME", fileNames[0].FullName);
command.Parameters.AddWithValue("@LASTATIME", fileNames[0].LastAccessTime);
command.Parameters.AddWithValue("@LASTWTIME", fileNames[0].LastWriteTime);
command.Parameters.AddWithValue("@DATA", str_sign);

And as a bonus, it makes your code a lot more readable, too...

BTW: When inserting values, it is a very good idea to name all the columns you want to insert in teh INSERT query...

Storing Ascii Code in Database

1 solution

Solution 2

Add your solution here

Preview 0

Storing Ascii Code in Database

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us