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

Question

5.00/5 (1 vote)

See more:

I'm getting error when I click submit button. Please help me.

This is the Error: Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

C#

protected void Button1_Click(object sender, EventArgs e)
        {
            //upload image

             if (FileUpload1.HasFile)
            {
              
                string str = FileUpload1.FileName;
                FileUpload1.PostedFile.SaveAs(Server.MapPath(".") + "//images//" + str);
                string path = "~//images//";
                cn.Open();

                SqlCommand cmd = new SqlCommand("insert into tblStudent(std_ID,std_Name,std_Password,std_ContactNumber,std_Address,std_Image,module_ID,intake_Code) Values('" + TextBox2.Text + "','" +TextBox1.Text+"','"+TextBox3.Text+ "','"
                    +TextBox4.Text+"','" +TextBox5.Text+"','"+ path + "','" +CheckBoxList1+"','" +DropDownList1+"')", cn);
                cmd.Parameters.AddWithValue("@std_ID", TextBox2.Text);
                cmd.Parameters.AddWithValue("@std_Name", TextBox1.Text);
                cmd.Parameters.AddWithValue("@std_Password", TextBox3.Text);
                cmd.Parameters.AddWithValue("@std_ContactNumber", TextBox4.Text);
                cmd.Parameters.AddWithValue("@std_Address", TextBox5.Text);
                cmd.Parameters.AddWithValue("@std_Image", path);
                cmd.Parameters.AddWithValue("@module_ID", CheckBoxList1.SelectedValue);
                cmd.Parameters.AddWithValue("@intake_Code", DropDownList1.SelectedValue); 


                cmd.ExecuteNonQuery();
                cn.Close();

              System.Windows.Forms.MessageBox.Show("New Account Register Successfully!");
               
       
       
               

            }
            else
            {
                Label3.Text = "Please upload a image";
            }
            

        }

Posted 13-Dec-14 17:10pm

Member 11308948

Updated 13-Dec-14 18:09pm

v4

Add a Solution

Comments

PIEBALDconsult 13-Dec-14 23:37pm

string str = FileUpload1.FileName;
string path = "~//images//" + str.ToString();

There is no need to call ToString on a string.

4 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)

PIEBALDconsult · Answer 1 · 2014-12-13T17:20:00

Solution 1

Use a parameterized statement and many problems will go away.
But if you think you are storing an image in the database, you will have to do more than pass the name of the file.

C#

SqlCommand cmd = new SqlCommand("insert into tblStudent(std_ID,std_Name,std_Password,std_ContactNumber,std_Address,std_Image,module_ID,intake_Code) Values(@std_ID,@std_Name,@std_Password,@std_ContactNumber,@std_Address,@std_Image,@module_ID,@intake_Code)", cn);

cmd.Parameters.AddWithVlaue ( "@std_ID" , TextBox2.Text  ) ;
// repeat for the other values

cmd.ExecuteNonQuery();

Posted 13-Dec-14 17:20pm

PIEBALDconsult

Updated 13-Dec-14 17:31pm

v4

Comments

Member 11308948 14-Dec-14 0:00am

after adding parametererized statement. now im facing this issue
No mapping exists from object type System.Web.UI.WebControls.CheckBoxList to a known managed provider native type.

PIEBALDconsult 14-Dec-14 0:03am

Right, you will need to access the SelectedItem (or whatever) of the control.
Please use Improve question to include the code as it now stands.

Member 11308948 14-Dec-14 0:17am

Sir,I had updated my question. Im still facing the same issue Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

PIEBALDconsult 14-Dec-14 11:28am

Solution 2 should help. And I seem to recall that there is even an article here on CP concerning stoping images to a database.

DamithSL · Answer 2 · 2014-12-13T17:46:00

use parameterized sql statement and also you need to read the file content and set it as parameter value. sample code :

C#

protected void Upload(object sender, EventArgs e)
{
    string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string contentType = FileUpload1.PostedFile.ContentType;
    using (Stream fs = FileUpload1.PostedFile.InputStream)
    {
        using (BinaryReader br = new BinaryReader(fs))
        {
            byte[] bytes = br.ReadBytes((Int32)fs.Length);
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string query = "insert into tblFiles values (@Name, @ContentType, @Data)";
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Name", filename);
                    cmd.Parameters.AddWithValue("@ContentType", contentType);
                    cmd.Parameters.AddWithValue("@Data", bytes);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
    Response.Redirect(Request.Url.AbsoluteUri);
}

Refer: Upload and Download files from SQL Server Database in ASP.Net[^]

Member 11308948 · Answer 3 · 2014-12-16T02:21:00

Solution 3

changed the datatype to image.

Posted 16-Dec-14 2:21am

Member 11308948

Chris Ross 2 · Answer 4 · 2014-12-16T04:05:00

As others have suggested, it appears you are attempting to save the path to the image file as it if were the image itself. I emphasize appears because you haven't demonstrated having done any research to narrow down which column in the insert statement is causing the problem.

Do you have the use of SQL Server Management Studio ('SSMS'; and do you have a development database in which you can do SQL experiments)? If so, I recommend that you use them to
(a) capture the set of actual parameter values you are attempting to insert (use SSMS tools menu, SQL Server Profiler), and
(b) with that information, run the INSERT statement in SSMS, stripping columns and values until it works. Then, you know exactly which column does not support the value you are attempting to insert. NOW, you have the power to solve the problem for yourself.

I suspsect that the problem is as others have said, that you're attempting to put an image file path into a column typed for the image itself. In which case, you really should pay attention to Solution 2, which shows loading the image and saving its bytes.

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

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us