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

asp.netsqlvb.net

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:

Table

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.

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

Best Answer

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, Telephone and Fax. This will help you out this time, but you should REALLY use parameters. It will make your life easier and this statement better:

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)