Failed to convert parameter value from a Byte[] to a String

Question

I'm building an app with c# asp.net. I need to insert some data into database. Everything works fine, but i get problem with inserting an image.

My database table:

Order

OrderID int
description varchar(50)
numOfItems int
material varchar(50)
image varbinary(max)

My code for inserting data into database

protected void btnAddItem_Click(object sender, EventArgs e)
    {
        string filepath = fileUpload.PostedFile.FileName;
        string filename = Path.GetFileName(filepath);
        string ext = Path.GetExtension(filename);
        string contentType = String.Empty;

        switch (ext)
        {
            case ".jpg":
                contentType = "image/jpg";
                break;
            case ".png":
                contentType = "image/png";
                break;
            case ".gif":
                contentType = "image/gif";
                break;
            case ".pdf":
                contentType = "application/pdf";
                break;
        }
        if (contentType != String.Empty)
        {
            Stream fs = fileUpload.PostedFile.InputStream;
            BinaryReader br = new BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);

            string kon = ConfigurationManager.ConnectionStrings["mk"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(kon))
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Order(description, numOfItems, material, image"))
                {
                    cmd.Connection = conn;
                    cmd.Parameters.AddWithValue("@description", inputTextArea.Text);
                    cmd.Parameters.AddWithValue("@numOfItems", inputTextArea.Text);
                    cmd.Parameters.AddWithValue("@material", inputTextArea.Text);
                    cmd.Parameters.Add("@image", SqlDbType.VarChar).Value = bytes;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    Response.Write("Success!");
                }
            }
        }
    }

When i do that, i get the following error : Failed to convert parameter value from a Byte[] to a String.

Any ideas?

UPDATE - new error

Incorrect syntax near 'image '. error. any ideas?

http://stackoverflow.com/questions/5824620/what-sqldbtype-maps-to-varbinarymax — Narek Arzumanyan, Nov 30 '16 at 07:29

score 2 · Answer 1 · answered Nov 30 '16 at 07:29

2

cmd.Parameters.Add("@image", SqlDbType.VarChar).Value = bytes;

Your image is not of type VarChar you need to fix that. Most likely, you need Binary.

answered Nov 30 '16 at 07:29

nvoigt

75,013
26
93
142

actually now i get Incorrect syntax near 'image '. error. any ideas? – aiden87 Nov 30 '16 at 07:40

sujith karivelil · Accepted Answer · 2016-11-30T07:44:53.810

2

For the parameter @image the passed value is a byte array but you ware specified that the input will be VarChar change it to Binary. so the statement that add specific parameter will be looks like the following

cmd.Parameters.Add("@image", SqlDbType.Binary).Value = bytes;

And you have to add the placeholders to your query, which means the query text should be like this:

"INSERT INTO Order(description, numOfItems, material, image)values(@description, @numOfItems,@material,@image)"

edited Nov 30 '16 at 07:44

answered Nov 30 '16 at 07:30

sujith karivelil

28,671
6
55
88

actually now i get Incorrect syntax near 'image '. error. any ideas? – aiden87 Nov 30 '16 at 07:40
@fokz8 : You have to add placeholders to the query, check the updates in my answer – sujith karivelil Nov 30 '16 at 07:45
that seems to solve that problem. but yet again i get another one...i also have "OrderID" as my primary key, but i didn't want to put it into my webform. So how do i create and input id, without user inputting it? btw, i get this error = "annot insert the value NULL into column 'OrderID', table '.dbo.Order'; column does not allow nulls. INSERT fails." – aiden87 Nov 30 '16 at 07:50
Make the column `OrderID` as auto increment in the table. so it will automatically updates as you inserted each row – sujith karivelil Nov 30 '16 at 07:53
Coool..! Always happy to help you – sujith karivelil Nov 30 '16 at 08:00

score 1 · Answer 3 · edited Nov 30 '16 at 07:59

1

Change the SqlDbType based on your database column type :

Possible datatypes for saving images in SQL are :

String types:

Data types and Description :

binary(n) Fixed width binary string. Maximum 8,000 bytes

varbinary Variable width binary string. Maximum 8,000 bytes

varbinary(max) Variable width binary string. Maximum 2GB

image Variable width binary string. Maximum 2GB

cmd.Parameters.Add("@image", SqlDbType.image).Value = bytes;

or

// Replace 8000, below, with the correct size of the field
     cmd.Parameters.Add("@image", SqlDbType.VarBinary, 8000).Value = bytes;

Modify your SQL Command to :

SqlCommand("INSERT INTO Order(description, numOfItems, material, image) values (@description,@numOfItems,@material,@image)")

edited Nov 30 '16 at 07:59

sujith karivelil

28,671
6
55
88

answered Nov 30 '16 at 07:36

Alina Anjum

1,178
6
30
53

actually now i get Incorrect syntax near 'image '. error. any ideas? – aiden87 Nov 30 '16 at 07:40
what modification you did before getting this incorrect syntax error ? – Alina Anjum Nov 30 '16 at 07:44
think there is problem with my insert statement, as break point gets past image part – aiden87 Nov 30 '16 at 07:45
@fokz8 : yes definitely, you missed the place holders, check the updates in my answer – sujith karivelil Nov 30 '16 at 07:47
I've added the sql command , you have to replace your sql command , I've added – Alina Anjum Nov 30 '16 at 07:48
@AlinaAnjum : it wont compile, check the syntax errors – sujith karivelil Nov 30 '16 at 07:51
Thanks @un-lucky :) – Alina Anjum Nov 30 '16 at 08:00
cooll.......! we are all here to help others, so happy to help you as well – sujith karivelil Nov 30 '16 at 08:01

Failed to convert parameter value from a Byte[] to a String

3 Answers3