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

Question

0.00/5 (No votes)

See more:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Web.Configuration;
using System.Drawing;
using System.ComponentModel;
using System.Data;
using System.Web.DataAccess;
using System.Data.SqlClient;
using System.Windows.Forms;

public partial class Registration : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=UNITECHSYSTEM\\SQLEXPRESS;Initial Catalog=Rajj1;Integrated Security=True");
int i = 0, j = 0, k = 0;
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click1(object sender, EventArgs e)
{

SqlCommand cmd = new SqlCommand("INSERT INTO Reg_WebSite4(UID, PWD, EMail, Loc, Address, Sex, Lang, Accept) VALUES('" + TextBoxUN.Text + "','" + PWD1.Text + "','" + Email.Text + "','" + LocList.Items[i].ToString() + "','" + AddressTXT.Text + "','" + SexList.Items[j].ToString() + "','" + LangBox.Items[k].ToString() + "','" + AcceptBox.Text + "')", conn);

conn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Registration SuccessFul Welcome Mr." + TextBoxUN);
conn.Close();
}
}

Posted 29-Jan-15 0:51am

Member 11411651

Updated 29-Jan-15 0:52am

v2

Add a Solution

Comments

Member 11411651 29-Jan-15 6:53am

Error Shows At
cmd.ExecuteNonQuery();

Rajesh waran 29-Jan-15 7:29am

Error ??? What exception you got? Debug your code and check what exception is showing?

Nathan Minier 29-Jan-15 7:48am

It's the nested quote game that you're playing. Break those apart to troubleshoot.

2 solutions

Solution 2

Most Common problem here would be that if the column size and the size of the data that you are pushing in doesn't match.

check each column size in the table and check how many characters/values you're passing from the front end.

if column1 size is 20 and you're passing 30 characters from the front end to insert into column1, this exception occurs.

P.S : this is the most times exception occurring if this happens and there could be other thousand reasons why it is coming.

to do a quick check try this soln else it could be an other issue.

Posted 29-Jan-15 3:30am

Sanman Marathe

Updated 29-Jan-15 3:33am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-01-29T01:50:00

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

C#

using (SqlConnection conn = new SqlConnection("Data Source=UNITECHSYSTEM\\SQLEXPRESS;Initial Catalog=Rajj1;Integrated Security=True"))
using (SqlCommand cmd = new SqlCommand("INSERT INTO Reg_WebSite4(UID, PWD, EMail, Loc, Address, Sex, Lang, Accept) VALUES (@UID, @PWD, @EMail, @Loc, @Address, @Sex, @Lang, @Accept)", conn))
{
    cmd.Parameters.AddWithValue("@UID", TextBoxUN.Text);
    cmd.Parameters.AddWithValue("@PWD", PWD1.Text);
    cmd.Parameters.AddWithValue("@EMail", Email.Text);
    cmd.Parameters.AddWithValue("@Loc", LocList.Items[i]);
    cmd.Parameters.AddWithValue("@Address", AddressTXT.Text);
    cmd.Parameters.AddWithValue("@Sex", SexList.Items[j]);
    cmd.Parameters.AddWithValue("@Lang", LangBox.Items[k]);
    cmd.Parameters.AddWithValue("@Accept", AcceptBox.Text);
    
    conn.Open();
    cmd.ExecuteNonQuery();
}

It looks like you're storing passwords in plain text. That's a very bad idea. You should only ever store a salted hash of the password.
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

MessageBox.Show will not work in an ASP.NET application. If you're lucky, you'll get an exception telling you that the current process is not interactive. Otherwise, the message will display on the server, where nobody will ever see it, and your code will hang waiting for someone to press "OK".

It might appear to work when you're developing the site in Visual Studio; but that's only because the server and client are the same computer in that scenario.

The "String or binary data would be truncated" error means that one of the values you're trying to insert is longer than the defined length of the column. Unfortunately, it doesn't tell you which column is the problem. You'll need to debug your code, check the lengths of the values you're trying to insert, and compare them to the defined lengths of the columns.

(There's an active bug report[^] asking Microsoft to improve this error message in a future version of SQL Server.)