[Solved] Insert to Database Exception: String or binary data would be truncated for NVARCHAR field in ASP.Net

RichardS...

SOLVED CLOSED ACTIVE ANSWERED
User: RichardSa
Posted: on Jun 05, 2022 05:52 AM
Forum: Data Controls
Answer: 1
Views: 3120

I tried to insert word document into database table and got this error. Maybe the value is greater than what the table column would carry, but I used VarBinary(MAX).

THE ERROR

Server Error in '/' Application.

String or binary data would be truncated. The statement has been terminated.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated. Source Error:

Line 163:  cmd.Parameters.AddWithValue("@Organization", named.Text.Trim());
Line 164:  con.Open();
Line 165:  object Invoice = cmd.ExecuteScalar();
Line 166:  con.Close();
Line 167:  Session["indocx"] = Invoice;

Source File: c:\Users\BlocBondy\Documents\Visual Studio 2015\WebSites\quirver\invoicedocx.aspx.cs Line: 165 Stack Trace:

[SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2582954
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +6034042
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +297
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4291
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +59
   System.Data.SqlClient.SqlDataReader.get_MetaData() +91
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +446
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2698
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +1611
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +65
   System.Data.SqlClient.SqlCommand.ExecuteScalar() +283
   invoicedocx.buttonmail_Click(Object sender, EventArgs e) in c:\Users\BlocBondy\Documents\Visual Studio 2015\WebSites\quirver\invoicedocx.aspx.cs:165
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9796650
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +211
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1696

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.4494.0

protected void continue_Click(object sender, EventArgs e)
{
    string filename = Path.GetFileName(files.PostedFile.FileName);
    string contentType = files.PostedFile.ContentType;
    using (Stream fs = files.PostedFile.InputStream)
    {
        using (BinaryReader br = new BinaryReader(fs))
        {
            byte[] bytes = br.ReadBytes((Int32)fs.Length);
            using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True"))
            {
                string query = "INSERT INTO tableinvoice(Name, ContentType, Data, CreatedDate)" +
                               " VALUES(@Name, @ContentType, @Data, @CreatedDate); SELECT @@IDENTITY";
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@Name", filename);
                    cmd.Parameters.AddWithValue("@ContentType", contentType);
                    cmd.Parameters.AddWithValue("@Data", bytes);
                    cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                    con.Open();
                    object Invoice = cmd.ExecuteScalar();
                    con.Close();
                    Session["indocx"] = Invoice;
                    Response.Redirect("Worddoc.aspx");
                }
            }
        }
    }
}

RichardS...

0 3

Reply
ANSWER
User: RichardSa
Replied: on Jun 06, 2022 03:43 AM
Report

I changed the dataType of some columns to nvarchar(MAX) and it worked.

[Solved] Insert to Database Exception: String or binary data would be truncated for NVARCHAR field in ASP.Net

Server Error in '/' Application.

String or binary data would be truncated. The statement has been terminated.

Company

Explore

Follow us

Contact Us