VB.NET SQL - 字符串或二进制数据将被截断。该语句已终止

VB.NET SQL - String or binary data would be truncated. The statement has been terminated

提问by Legend1989

I am new to this and getting the following error when running my form. 'String or binary data would be truncated.The statement has been terminated.'

我是新手，在运行我的表单时出现以下错误。'字符串或二进制数据将被截断。语句已终止。'

This is the code for the form

这是表格的代码

<form runat="server">
        <table style="width:500px">
            <tr>
                <td>Customer Name*: </td>
                <td><asp:TextBox ID="CustomerName" runat="server" MaxLength="50"></asp:TextBox></td>
                <td><asp:RequiredFieldValidator id="rfvValidator" runat="server" Enabled="true" ControlToValidate="CustomerName" ErrorMessage="Customer Name is Required." ></asp:RequiredFieldValidator></td>
            </tr>
            <tr>
                <td>Address 1: </td>
                <td><asp:TextBox ID="Address1" runat="server" MaxLength="100"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Address 2:</td> 
                <td><asp:TextBox ID="Address2" runat="server" MaxLength="100"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Address 3:</td> 
                <td><asp:TextBox ID="Address3" runat="server" MaxLength="100"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Town: </td>
                <td><asp:TextBox ID="Town" runat="server" MaxLength="100"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Country:</td> 
                <td><asp:TextBox ID="Country" runat="server" MaxLength="50"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Postcode:</td>
                <td><asp:TextBox ID="PostCode" runat="server" MaxLength="8"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Telephone:</td> 
                <td><asp:TextBox ID="Telephone" runat="server" MaxLength="50"></asp:TextBox></td>
            </tr>
            <tr>
                <td>Fax:</td> 
                <td><asp:TextBox ID="Fax" runat="server" MaxLength="50"></asp:TextBox></td>
            </tr>
        </table>
                <asp:Button id="btnSave" Text="Save" runat="server" />
                <input type=button name="cancel" value="Cancel" onClick="parent.jQuery.fancybox.close()">
        <asp:Label ID="Label1" runat="server"></asp:Label>
    </form>

This is my code for the submit button

这是我提交按钮的代码

 Imports System.Data
 Imports System.Data.SqlClient

 Public Class EditCustomer
     Inherits System.Web.UI.Page

     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
     End Sub

     Dim con As SqlConnection
     Dim cmd As SqlCommand

     Protected Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
         con = New SqlConnection("Data Source=IPAddress;Initial Catalog=medical01;Persist Security Info=True;User ID=******;Password=******")
         con.Open()
         cmd = New SqlCommand("INSERT INTO Customer_tbl (customername,address1,address2,address3,town,country,postcode,telephone,fax,isDeleted) VALUES('" & CustomerName.Text & "','" & Address1.Text & "','" & Address2.Text & "','" & Address3.Text & "','" & Town.Text & "', " & PostCode.Text & ", " & Telephone.Text & "," & Fax.Text & ", 1)", con)
         cmd.ExecuteNonQuery()
         Label1.Text = "Customer Added."
         con.Close()
     End Sub
 End Class

Here is my database:

这是我的数据库：

I am getting this error:

我收到此错误：

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.

说明：在执行当前 Web 请求期间发生未处理的异常。请查看堆栈跟踪以获取有关错误及其在代码中的来源的更多信息。

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

异常详细信息：System.Data.SqlClient.SqlException：字符串或二进制数据将被截断。该语句已终止。

回答by Patrick Hofman

You didn't quote all fields:

您没有引用所有字段：

cmd = New SqlCommand("INSERT INTO Customer_tbl (customername,address1,address2,address3,town,country,postcode,telephone,fax,isDeleted)"
& "VALUES('" & CustomerName.Text & "','" & Address1.Text & "','" & Address2.Text & "','" & Address3.Text & "','" & Town.Text & "', '" & PostCode.Text & "', '" & Telephone.Text & "','" & Fax.Text & "', 1)", con)

I added them on PostCode, Telephoneand Fax. This will help you out this time, but you should REALLY use parameters. It will make your life easier and this statement better:

我在PostCode,Telephone和上添加了它们Fax。这次这将帮助您解决问题，但您应该真正使用参数。它会让你的生活更轻松，这句话更好：

cmd = New SqlCommand("INSERT INTO Customer_tbl (customername,address1,address2,address3,town,country,postcode,telephone,fax,isDeleted)"
& "VALUES(@customername,@address1,@address2,@address3,@town,@country,@postcode,@telephone,@fax,@isDeleted)", con)

cmd.Parameters.AddWithValue("customername", CustomerName.Text)
cmd.Parameters.AddWithValue("address1", Address1.Text)
cmd.Parameters.AddWithValue("address2", Address2.Text)
cmd.Parameters.AddWithValue("address3", Address3.Text)
cmd.Parameters.AddWithValue("town", Town.Text)
cmd.Parameters.AddWithValue("country", Country.Text)
cmd.Parameters.AddWithValue("postcode", PostCode.Text)
cmd.Parameters.AddWithValue("telephone", Telephone.Text)
cmd.Parameters.AddWithValue("fax", Fax.Text)
cmd.Parameters.AddWithValue("isDeleted", 1)