Insert image to local SQL Server database in ASP.NET

Question

I want to add image from FileUpload to a local SQL Server database in ASP.NET.

I have read many same questions, but they didn't help me:(

int length = FileUpload.PostedFile.ContentLength;
byte[] picSize = new byte[length];

HttpPostedFile uplImage= FileUpload.PostedFile;

uplImage.InputStream.Read(picSize, 0, length);

SqlCommand com = new SqlCommand("INSERT INTO [Table] (Name, Picture) values (@Name, @Picture)", con);
com.Parameters.AddWithValue("@Name", TextName.Text);
com.Parameters.AddWithValue("@Picture", picSize);
com.ExecuteNonQuery();

So if I simply add column Name - all okay. But when I want also add image to database there is mistake:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Incorrect syntax near the keyword 'Picture'.

In my local database Picture have an image type. Please help me.

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Oct 10, 2014 at 18:50
image will be removed in a future version of SQL Server. Avoid using this data type in new development work, and plan to modify applications that currently use them. Use varbinary(max) instead. See details here — marc_s
– marc_s, Commented Oct 10, 2014 at 18:53

meda · Accepted Answer · 2014-10-10 18:37:44Z

You need to catch SqlException and then handle it:

int length = FileUpload.PostedFile.ContentLength;
byte[] picSize = new byte[length];
HttpPostedFile uplImage= FileUpload.PostedFile;
uplImage.InputStream.Read(picSize, 0, length);

using (SqlConnection con = new SqlConnection(connectionString))
{
    SqlCommand com = new SqlCommand("INSERT INTO [Table] (Name, Picture) values (@Name, @Picture)", con);
    try
    {
        com.Parameters.AddWithValue("@Name", TextName.Text);
        com.Parameters.AddWithValue("@Picture", picSize);
        com.ExecuteNonQuery();
    }
    catch (SqlException ex)
    {
        for (int i = 0; i < ex.Errors.Count; i++)
        {
            errorMessages.Append("Index #" + i + "\n" +
                "Message: " + ex.Errors[i].Message + "\n" +
                "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                "Source: " + ex.Errors[i].Source + "\n" +
                "Procedure: " + ex.Errors[i].Procedure + "\n");
        }
        Console.WriteLine(errorMessages.ToString());
    }
}

Collectives™ on Stack Overflow

Insert image to local SQL Server database in ASP.NET

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related