How do I...asp Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query

Question

1.00/5 (2 votes)

See more:

I have problem "asp Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query"

please help me.

this code for insert file from file upload to DB.

C#

protected void btnUpload_Click(object sender, EventArgs e)
   {
       Class1 cl = new Class1();


       string filename = Path.GetFileName(fileUpload1.PostedFile.FileName);
       Stream str = fileUpload1.PostedFile.InputStream;
       BinaryReader br = new BinaryReader(str);
       Byte[] size =  br.ReadBytes((int) str.Length);

       string Type = "application/word";

       string insert = cl.InsertUpload(filename, Type, size);}

This class code (class1)

C#

public string InsertUpload(string FileName, string FileType, byte[] FileData)
    {
        string insert = "0";
        {
            string myconstring = ConfigurationManager.AppSettings["Reg2"];
            SqlConnection record = new SqlConnection();
            record.ConnectionString = myconstring;
            record.Open();

            try
            {

                SqlCommand comserv = new SqlCommand(" insert into FileInformation(FileName,FileType,FileData) values('" + FileName + "\', '" + FileType + "\',  '" + FileData + "\' )", record);
                comserv.ExecuteNonQuery();
                insert = "5";
            }
            catch (SqlException ex)
            {
                
            }
            record.Close();
            return insert;
        }

Posted 9-Jun-15 3:39am

Member 11411753

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2015-06-09T04:35:00

Don't do it like 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.
Surprisingly, that'll also cure your problem...

When you concatenate an array of bytes into a string:

C#

byte[] data = ...
string s = "Byte Data: " + data;

It calls an implicit ToString on the array - which returns the type of the variable rather than the content as a string of some description.

So use parameterised queries (at all times) and it will pass the content directly:

C#

SqlCommand comserv = new SqlCommand(" insert into FileInformation (FileName,FileType,FileData) values(@FN, @FT, @FD)", record);
comserv.Parameters.AddWithValue("@FN", FileName);
comserv.Parameters.AddWithValue("@FT", FileType);
comserv.Parameters.AddWithValue("@FD", FileData);
comserv.ExecuteNonQuery();

Richard Deeming · Answer 2 · 2015-06-09T04:36:00

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

A side-effect of fixing this serious security vulnerability in your code will be that you will also fix your problem:

C#

public string InsertUpload(string FileName, string FileType, byte[] FileData)
{
    const string query = @"insert into FileInformation (FileName, FileType, FileData) values (@FileName, @FileType, @FileData)";
    string myconstring = ConfigurationManager.AppSettings["Reg2"];
    
    using (SqlConnection connection = new SqlConnection(myconstring))
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@FileName", FileName);
        command.Parameters.AddWithValue("@FileType", FileType);
        command.Parameters.AddWithValue("@FileData", FileData);
        
        connection.Open();
        command.ExecuteNonQuery();
        
        return "5";
    }
}

You might also want to look at moving your connection string from the <appSettings> configuration section to the dedicated <connectionStrings> section:
Connection Strings and Configuration Files (ADO.NET)[^]

How do I...asp Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0