Problem with storing file in SQL Server

Question

I'm trying to save in SQL Server DB a file. (It doesn't mutter which type of file) I use for fileContent field with image type that allow NULL. When I executed Command.ExecuteNonQuery() I'm having error-message: "String or binary data would be truncated.\r\nThe statement has been terminated." Below you may see my code:

    CREATE TABLE [NewsContent]
(
         [ID] [int] IDENTITY(1,1) NOT NULL,
         [FileName] [nvarchar](15) NOT NULL,
         [Extension] [nvarchar](5) NOT NULL,
         [Content] [image] NULL
) 


protected void btnUploadFile_Click(object sender, EventArgs e)
{
    if (fileUpload.HasFile)
    {
        try
        {
            Int32 fileLength = fileUpload.PostedFile.ContentLength;
            String fileType = fileUpload.PostedFile.ContentType;
            Stream fileStream = fileUpload.PostedFile.InputStream;
            String fileName = fileUpload.PostedFile.FileName;
            byte[] fileContent = new byte[fileLength];
            fileStream.Read(fileContent, 0, fileLength);
            int status = Utils.DBWorker.UploadFile(fileName, fileType, fileContent);

            if (status == -1)
                StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured";
        }
        catch (Exception ex)
        {
            StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
        }
    }
}

public static int UploadFile(String fileName, String fileType, byte[] fileContent)
{
    try
    {
        string insertSql = "INSERT INTO [NewsContent] (FileName, Extension, Content) VALUES (@FileName, @Extension, @Content)";
        Command.CommandText = insertSql;
        Command.Parameters.Clear();
        Command.Parameters.Add(new SqlParameter("@FileName", SqlDbType.NVarChar, 100));
        Command.Parameters.Add(new SqlParameter("@Extension", SqlDbType.NVarChar, 50));
        Command.Parameters.Add(new SqlParameter("@Content", SqlDbType.VarBinary));
        Command.Parameters["@FileName"].Value = fileName;
        Command.Parameters["@Extension"].Value = fileType;
        Command.Parameters["@Content"].Value = fileContent;

        return Command.ExecuteNonQuery();
    }
    catch (Exception es)
    {
        return -1;
    }
}

Could someone help me?

Well, sounds like a column limit. Please post your CREATE TABLE SQL so we can see how the columns are configured. — Kirk Woll
– Kirk Woll, Commented Oct 12, 2010 at 13:47

Timothy S. Van Haren · Accepted Answer · 2010-10-12 14:04:31Z

1

Make sure your file name is 15 characters or less and that your extension is 5 characters or less.

answered Oct 12, 2010 at 14:04

Timothy S. Van Haren

8,9762 gold badges32 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roman · Accepted Answer · 2010-10-12 14:15:12Z

0

Gyes, I've updated definition of table and it works! Thaks for help!

CREATE TABLE [NewsContent]
(
         [ID] [int] IDENTITY(1,1) NOT NULL,
         [FileName] [nvarchar](50) NOT NULL,
         [Extension] [nvarchar](30) NOT NULL,
         [Content] [image] NULL
)

answered Oct 12, 2010 at 14:15

Roman

5751 gold badge10 silver badges27 bronze badges

Collectives™ on Stack Overflow

Problem with storing file in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related