C# ASP.NET Registration page won't update database anymore

Question

My registration page (C# ASP.NET on VS 2015) updated a few entries in my SQL Server database, then I changed the database, updated the code a bit (updated web.config string, etc) and now it only shows this error:

Error:System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Registration.Button1_Click(Object sender, EventArgs e) in c:\Users\718358\Documents\Visual Studio 2015\WebSites\Registration2\Registration.aspx.cs:line 61 ClientConnectionId:d0e00199-8bf8-435b-a2ea-03d6f5c8dc40 Error Number:8152,State:13,Class:16 Registration

This is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public partial class Registration : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        this.UnobtrusiveValidationMode = System.Web.UI.UnobtrusiveValidationMode.None;

        if (IsPostBack)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationDatabaseConnectionString"].ConnectionString);
            conn.Open();
            string checkuser = "SELECT count(*) FROM Customers WHERE CustUserName='" + txtCustUserName.Text + "'";

            SqlCommand com = new SqlCommand(checkuser, conn);
            int temp = Convert.ToInt32(com.ExecuteScalar().ToString());

            if (temp == 1)
            {
                Response.Write("User already exists");
            }

            conn.Close();
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationDatabaseConnectionString"].ConnectionString);
            conn.Open();

            string insertQuery = "INSERT into Customers (CustUserName, CustPassword, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail) values (@custUsername ,@custPassword ,@custFirstName ,@custLastName ,@custAddress ,@custCity ,@custProv ,@custPostal, @custCountry ,@custHomePhone ,@custBusPhone ,@custEmail)";

            SqlCommand com = new SqlCommand(insertQuery, conn);
            com.Parameters.AddWithValue("@custUsername", txtCustUserName.Text);
            com.Parameters.AddWithValue("@custPassword", txtCustPassword.Text);
            com.Parameters.AddWithValue("@custFirstName", txtCustFirstName.Text);
            com.Parameters.AddWithValue("@custLastName", txtCustLastName.Text);
            com.Parameters.AddWithValue("@custAddress", txtCustAddress.Text);
            com.Parameters.AddWithValue("@custCity", txtCustCity.Text);
            com.Parameters.AddWithValue("@custProv", txtCustProv.Text);
            com.Parameters.AddWithValue("@custPostal", txtCustPostal.Text);
            com.Parameters.AddWithValue("@custCountry", txtCustCountry.Text);
            com.Parameters.AddWithValue("@custHomePhone", txtCustHomePhone.Text);
            com.Parameters.AddWithValue("@custBusPhone", txtCustBusPhone.Text);
            com.Parameters.AddWithValue("@custEmail", txtCustEmail.Text);

            com.ExecuteNonQuery();

            Response.Redirect("Manager.aspx");
            Response.Write("Registration is successful" );

            conn.Close();
        }
        catch(Exception ex)
        {
            Response.Write("Error:"+ex.ToString());
        }
    }
}

Thanks.

Answer 1

Based on the error message, one or more of the fields is getting a value that is longer than the corresponding column. You should double check the lengths of the columns. Without knowing more details it's impossible to say how or why changing your database caused this.

Answer 2

Replace The Following Code with Your Existing One : -- )

---

protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationDatabaseConnectionString"].ConnectionString);
            conn.Open();
            string insertQuery = "INSERT into Customers (CustUserName, CustPassword, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail) values (@custUsername ,@custPassword ,@custFirstName ,@custLastName ,@custAddress ,@custCity ,@custProv ,@custPostal, @custCountry ,@custHomePhone ,@custBusPhone ,@custEmail)";
            SqlCommand com = new SqlCommand(insertQuery, conn);
            com.Parameters.Add("@custUsername", txtCustUserName.Text);
            com.Parameters.Add("@custPassword", txtCustPassword.Text);
            com.Parameters.Add("@custFirstName", txtCustFirstName.Text);
            com.Parameters.Add("@custLastName", txtCustLastName.Text);
            com.Parameters.Add("@custAddress", txtCustAddress.Text);
            com.Parameters.Add("@custCity", txtCustCity.Text);
            com.Parameters.Add("@custProv", txtCustProv.Text);
            com.Parameters.Add("@custPostal", txtCustPostal.Text);
            com.Parameters.Add("@custCountry", txtCustCountry.Text);
            com.Parameters.Add("@custHomePhone", txtCustHomePhone.Text);
            com.Parameters.Add("@custBusPhone", txtCustBusPhone.Text);
            com.Parameters.Add("@custEmail", txtCustEmail.Text);

            com.CommandType = CommandType.Text;

            com.ExecuteNonQuery();
            Response.Redirect("Manager.aspx");
            Response.Write("Registration is successful" );

            conn.Close();
        }
        catch(Exception ex)
        {
            Response.Write("Error:"+ex.ToString());
        }
    }
}

---

You were creating a small flaw just replace AddWithValue with Add only And also Add The following line "com.CommandType = CommandType.Text;"

Answer 3

Thanks to all. @JohnEphraimTugado answered the question. The error occurred because the field received input larger than the limit accepted by the field.

Answer 4

Well the error speaks out of itself, there is a field in DB which is getting more data to accommodate than it's limit (explaining in lame language). Please check the field, make sure you do either of the two :

1. Trim the data string being sent to the DB to a particular length, below the character limit in the DB.
2. Change the limit of the DB field, so that it can accommodate more length.

Answer 5

If you're still getting the error then following may be the reasons :

Error : (String or binary data would be truncated)

Reason :

1) Data you are entering in the database field is of different type.

2) Data you are entering in database field is bigger in size than the declared one.

3)You are not converting the incoming data form to appropriate field type.

My Guess : (You're Directly Entering Data as From Fields)

Remedies :

1) Convert string data to String (concat .toString() in last of input recieved) + Convert data according to other data types as well like int/float/etc

2) Increase the field input Size in Database (Ex: City(25) to city(MAX))