Cannot set Column 'name'. The value violates the MaxLength limit of this column

Question

I have a table with 5 columns.

They are:

fItemID - Int IDENTITY Primary Key
fItemName - varchar(50)
fItemType - varchar(50)
fItemModel - varchar(50)
fItemWeight - int

When I try to add data to the table, using a DataGridView, I have no problems as long as I keep the values under 10 characters, but when I try to add 11 or more, I get an error message on three of the columns.

For fItemName and fItemType, I get:

Cannot set Column 'name'. The value violates the MaxLength limit of this column.

When I try to add more then 10 characters to fItemWeight, I get:

System.FormatException: Value was either too large or too small for an Int32. ---> System.OverFlowException: Value was either too large or too small for an Int32.

I have no idea why I am getting these errors, as adding info to fItemModel works perfectly.

As usual, I am using VS2013 Ultimate and VB.Net.

---

The following is all the code for the form:

Public Class frmBOMNonSteel

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DbStarFliteSystemsDataset.tblItemsQuery' table. You can move, or remove it, as needed.
        Me.TblItemsQueryTableAdapter.Fill(Me.DbStarFliteSystemsDataset.tblItemsQuery)
        'TODO: This line of code loads data into the 'DbStarFliteSystemsDataset.tblItems' table. You can move, or remove it, as needed.
        Me.TblItemsTableAdapter.Fill(Me.DbStarFliteSystemsDataset.tblItems)
        'TODO: This line of code loads data into the 'DbStarFliteSystemsDataset.tblItems' table. You can move, or remove it, as needed.
        Me.TblItemsTableAdapter.Fill(Me.DbStarFliteSystemsDataset.tblItems)
        'TODO: This line of code loads data into the 'DbStarFliteSystemsDataset.tblItems' table. You can move, or remove it, as needed.
        Me.TblItemsTableAdapter.Fill(Me.DbStarFliteSystemsDataset.tblItems)
        'TODO: This line of code loads data into the 'DbStarFliteSystemsDataset.tblItems' table. You can move, or remove it, as needed.
        Me.TblItemsTableAdapter.Fill(Me.DbStarFliteSystemsDataset.tblItems)
        Me.txtJobNumber.Text = frmParent.ToolStripTextBox1.Text

        With FItemTypeComboBox
            .DataSource = DbStarFliteSystemsDataset.tblItemsQuery
            .ValueMember = "fItemType"
        End With
    End Sub

    Private Sub SteelToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SteelToolStripMenuItem.Click

        Me.Close()
        frmBOMSteel.MdiParent = frmParent
        frmBOMSteel.Show()
        frmBOMSteel.WindowState = FormWindowState.Maximized
    End Sub

    Private Sub NonSteelToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles NonSteelToolStripMenuItem.Click

        Me.MdiParent = frmParent
        Me.Show()
    End Sub

    Private Sub WeightsRevisionsToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles WeightsRevisionsToolStripMenuItem.Click

        Me.Close()
        frmBOMWeightsRevisions.MdiParent = frmParent
        frmBOMWeightsRevisions.Show()
        frmBOMWeightsRevisions.WindowState = FormWindowState.Maximized
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        frmModalNewItem.ShowDialog()
    End Sub

    Private Sub FItemTypeComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles FItemTypeComboBox.SelectedIndexChanged

        Me.FItemNameComboBox.Text = ""

        Dim selected As Object = Me.FItemTypeComboBox.SelectedItem

        If (TypeOf selected Is DataRowView) Then

            Dim row As DataRow = DirectCast(selected, DataRowView).Row
            Dim fItemType As String = CStr(row.Item("fItemType"))
            Dim view As New DataView(DbStarFliteSystemsDataset.tblItems)

            view.RowFilter = String.Format("[fItemType]='{0}'", fItemType)
            Me.FItemNameComboBox.DataSource = view.ToTable(True, "fItemName")
            Me.FItemNameComboBox.DisplayMember = "fItemName"

        End If
    End Sub

    Private Sub TblItemsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
        Me.Validate()
        Me.TblItemsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DbStarFliteSystemsDataset)

    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs)
        Me.Validate()
        Me.TblItemsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DbStarFliteSystemsDataset)

    End Sub

    Private Sub TblItemsBindingNavigatorSaveItem_Click_1(sender As Object, e As EventArgs)
        Me.Validate()
        Me.TblItemsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DbStarFliteSystemsDataset)

    End Sub

    Private Sub TblItemsBindingNavigatorSaveItem_Click_2(sender As Object, e As EventArgs)
        Me.Validate()
        Me.TblItemsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.DbStarFliteSystemsDataset)

    End Sub
End Class

---

The following is the code for my Table:

CREATE TABLE [dbo].[tblItems] (
    [fItemID]     INT          IDENTITY (1, 1) NOT NULL,
    [fItemName]   VARCHAR (50) NULL,
    [fItemType]   VARCHAR (50) NULL,
    [fItemModel]  VARCHAR (50) NULL,
    [fItemWeight] INT          NULL,
    CONSTRAINT [PK_tblItems] PRIMARY KEY CLUSTERED ([fItemID] ASC)
);

Solution 1

I believe that your adapters are not in sync with the database. You could try remapping or you could try appending this within the load method:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.DbStarFliteSystemsDataset.tblItems.Columns("fItemName").MaxLength = 50
    Me.DbStarFliteSystemsDataset.tblItems.Columns("fItemType").MaxLength = 50
    Me.DbStarFliteSystemsDataset.tblItems.Columns("fItemModel").MaxLength = 50
    '.....
End Sub

Remapping

When you add a DB (database) to your project VS (Visual Studio) creates a strongly typed in-memory cache representing the data. This enables you to use a DataSet in your application with the same schema as your DB. However, if the DB schema changes afterwards (e.g. you have changed a type/length/name of a column), the cache is not updated. In the "Solution Explorer" pane there should be a file named [NameOfDB]DataSet.xsd. Double-clicking this file will open an editor allowing you to edit the DS and all its tables. Ensure it reflects the updated DB schema - close - save - rebuild.

Solution 2

I know this is an old post, but for those struggling with a similar issue:

I resolved this by simply deleting the dataset out of the project explorer and re-adding it via the "Add New Data Source" option in the project menu. As long as you don't delete the table references that sit at the bottom of the form editor, you're databindings will be OK.

When you re-add the dataset, it must be called the exact same name as the one you had before you deleted it. If done correctly, any edited columns in the database will now be back in sync.