locked
System.ArgumentException: 'Cannot set column 'ort'. The value violates the MaxLength RRS feed

  • Question

  • I have an exception that dosen't make any sense to me.
    I have a column in my database that has 'P' for Portrait and 'L' for Landscape, which in the database is a varchar(1).
    Here is the full output message:

    System.ArgumentException: 'Cannot set column 'ort'. The value violates the MaxLength limit of this column.'
    Display
    System.ArgumentException
    HResult=0x80070057
    Message=Cannot set column 'ort'. The value violates the MaxLength limit of this column.
    Source=System.Data
    StackTrace:
    at System.Data.DataColumn.CheckMaxLength(DataRow dr)
    at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
    at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean suppressEnsurePropertyChanged, Int32 position, Boolean fireEvent, Exception& deferredException)
    at System.Data.DataRow.EndEdit()
    at System.Data.DataRowView.EndEdit()
    at System.Windows.Forms.CurrencyManager.EndCurrentEdit()
    at System.Windows.Forms.CurrencyManager.ChangeRecordState(Int32 newPosition, Boolean validating, Boolean endCurrentEdit, Boolean firePositionChange, Boolean pullData)
    at System.Windows.Forms.CurrencyManager.set_Position(Int32 value)
    at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
    at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m)
    at System.Windows.Forms.ComboBox.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
    at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m)
    at System.Windows.Forms.Control.WmCommand(Message& m)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
    at System.Windows.Forms.Form.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
    at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
    at System.Windows.Forms.Control.DefWndProc(Message& m)
    at System.Windows.Forms.Control.WmCommand(Message& m)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ComboBox.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
    at My_Greeting.My.MyApplication.Main(String[] Args) in :line 81 

    Why does it say that it excedes the MaxLength limit of this column, as the column is one character wide and only has either the character 'P' or 'L' in the column.
    I don't know if this a clue, but it works upto CID = 9? (Where CID is the ID column and is an int.)

    'ort' was my variable name that represented Orientation. As I stated before in the database it is varchar(1). this column has either P or L in the database.
    I still find it strange that it does not get an exception error until the 10th row and all rows after that?

    As the error to some extent concerns the data table here is a screen shot of the settings:

    here is the code for the form:

    Imports PdfSharp
    Imports PdfSharp.Drawing
    Imports PdfSharp.Fonts
    Imports PdfSharp.PageOrientation
    Imports PdfSharp.PageSize
    Imports PdfSharp.Pdf
    Imports PdfSharp.Internal
    Imports PdfSharp.Drawing.Layout
    Imports PdfSharp.Forms
    Imports System.Data.SqlClient
    
    Enum pageOrientation
        Landscape
        Portrait
    End Enum
    Enum pagesize
        A4
        A5
    End Enum
    
    Public Class frmPrintFrm
    
        Private boxX As Double
        Private boxY As Double
        Private cellw As Double
        Private cellh As Double
        Private ort As String = Nothing
        Private FSize As Integer
        Private CFont As String = Nothing
        Private TxtColor As String
        Private sqlAdaptor As SqlDataAdapter
        Private dt As New DataTable
        Private CSizeSql As String = Nothing
        Private cmd As Object = Nothing
        Private CSizeValue As Double
        Private Narrative As String = Nothing
        Private CID As Integer
        Private Y As Double
        Private Verse As String
        Private connectionString As String = "Data Source=DESKTOP-S7FRNAL\SQLEXPRESS;Initial Catalog=Verses_Find;Integrated Security=True"
        Private Sub myBase_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Me.CSizeTableAdapter.Fill(Me.Verses_FindDataSet5.CSize)
            Dim Print As New frmPrintFrm
            Me.TopMost = True
            Me.WindowState = FormWindowState.Normal
    
            For Each oFont As FontFamily In FontFamily.Families 'This line populates the font combo with the system installed fonts
                cboFont.Items.Add(oFont.Name)
            Next
            'fetch the XKnownColor values into cboColor
            Dim knownColours() As XKnownColor = XColorResourceManager.GetKnownColors(includeTransparent:=False)
            cboColor.DataSource = knownColours
            cboColor.SelectedIndex = 0
        End Sub
        Private Sub btnPaste_Click(sender As Object, e As EventArgs) Handles btnPaste.Click
    
            ' Determine if there is any text in the Clipboard to paste into the text box.
            If Clipboard.GetDataObject().GetDataPresent(DataFormats.Text) = True Then
                ' Determine if any text is selected in the text box.
                If txbVerse.SelectionLength > 0 Then
                    ' Ask user if they want to paste over currently selected text.
                    If MessageBox.Show("Do you want to paste over current selection?",
                        "Cut Example", MessageBoxButtons.YesNo) = DialogResult.No Then
                        ' Move selection to the point after the current selection and paste.
                        txbVerse.SelectionStart = txbVerse.SelectionStart + txbVerse.SelectionLength
                    End If
                End If
                ' Paste current text in Clipboard into text box.
                txbVerse.Paste()
            End If
        End Sub
        Private Sub cboCSize_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboCSize.SelectedIndexChanged
            CSizeValue = CInt(cboCSize.SelectedValue)
        End Sub
        Private Sub nudFSize_ValueChanged(sender As Object, e As EventArgs) Handles nudFSize.ValueChanged
            FSize = CInt(nudFSize.Value)
        End Sub
    
        Private Sub cboFont_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboFont.SelectedIndexChanged
            CFont = cboFont.Text
        End Sub
    
        Private Sub nudTop_ValueChanged_1(sender As Object, e As EventArgs) Handles nudTop.ValueChanged
            boxY = CInt(nudTop.Value)
        End Sub
        Private Sub btnPrint_Click(sender As Object, e As EventArgs) Handles btnPrint.Click
    
            Using connection As New SqlConnection(connectionString), da As New SqlDataAdapter("SELECT CID, BoxX, Cellw, Cellh, ort, Narrative FROM CSize WHERE CID = @CID", connection), dt As New DataTable
                    connection.Open()
    
                da.SelectCommand.Parameters.AddWithValue("@CID", CSizeValue)
                    da.Fill(dt)
                    If dt.Rows.Count > 0 Then
                        Dim row As DataRow = dt.Rows(0)
                        printIt(row)
    
    
    
                End If
    
            End Using
    
    
        End Sub
        Private Sub printIt(row As DataRow)
            Dim ID As Double = CDbl(row("CID"))
            Dim X As Double = CDbl(row("BoxX")) * 2.83465
            Dim W As Double = CDbl(row("Cellw")) * 2.83465
            Dim H As Double = CDbl(row("Cellh")) * 2.83465
            Dim O As String = CStr(row("ort"))
            Dim N As String = CStr(row("Narrative"))
            Dim Y As Double
            Dim NL As String = CStr(Chr(13) & Chr(10))
            Verse = txbVerse.Text
            Replace(Verse, NL, " VBCrLf ")
            Dim document As PdfDocument
            ' Create a new PDF document
            document = New PdfDocument()
            document.Info.Title = "Created with PDFsharp"
    
            ' Create an empty page
            Dim page As PdfPage = document.AddPage
    
            If O = "L" Then
                page.Orientation = CType(pageOrientation.Landscape, PdfSharp.PageOrientation)
                page.Width = XUnit.FromMillimeter(297)
                page.Height = XUnit.FromMillimeter(210)
            Else
                page.Orientation = CType(pageOrientation.Portrait, PdfSharp.PageOrientation)
                page.Width = XUnit.FromMillimeter(210)
                page.Height = XUnit.FromMillimeter(297)
            End If
    
            '   create the brush
            Dim xClr As XColor = XColor.FromKnownColor(CType(cboColor.SelectedItem, XKnownColor))
            Dim brush As XSolidBrush = New XSolidBrush(xClr)
    
            ' Draw the text
            Y = boxY * 2.834665
    
            Dim gfx As XGraphics
            gfx = XGraphics.FromPdfPage(page)
            Dim tf As XTextFormatter
            tf = New XTextFormatter(gfx)
            Dim font As XFont = New XFont(CFont, FSize, XFontStyle.Regular)
            Dim rect As XRect
            rect = New XRect(X, Y, W, H)
            gfx.DrawRectangle(XBrushes.SeaShell, rect)
            tf.Alignment = XParagraphAlignment.Center
            tf.DrawString(Verse, font, brush, rect, XStringFormat.TopLeft)
    
            ' Save the document
            Dim filename As String = "verse.pdf"
            document.Save(filename)
    
            ' ...and start a viewer.
            Process.Start(filename)
            Me.Close()
        End Sub
    End Class

    'ort' was my variable name that represented Orientation. In the database it is varchar(1) and this column has either P or L in the database.
    I find it strange that it does not get an exception error until the 10th row and all rows after that?

    I have tried debugging and I found the following:

    The exception appears after the CSize sub is exited, but only in the case that the ID field (CID) reaches 10.

    I tried selecting one of the rows where CID was less than 10 and the combobox did not show the selected text util it exited the sub.  The combo was set using the VS combobox control.

    As you will see that I use 'ort ' as the selected value, beacause the code requires this to determine the orientation of the paper.  Whilst writing this I just realised that I had the conversion set to 'int' whereas the selected value is a string character. I think I should be using the 'Value Member' (CID), but the normal type of the 'Value Member' is a string and I have tried to convert it to an integer by using Cint and also CType, but it says you can't do that.

    I am sure the problem lies in the fact that I want the value of 'ort' (string), but also I need the CID value (int) in the SELECT statement which sets all of the positioning dimensions of the text.

    However I can't see why it calls the exception on the Selected Value (ort)?

    Can anyone see where I am going wrong?


    TEH


    • Edited by Rocky48 Thursday, July 11, 2019 7:55 PM
    Thursday, July 11, 2019 7:54 PM

All replies

  • Hello,

    Set the column to nvarchar(MAX) (this is simply a test) and see what happens as there is something odd going on and there is way to much code to go though so this is the best way to see what's going on, please report back what happens.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, July 11, 2019 9:27 PM
  • Does it work if you do not specify Selected Value, i.e. set it to “(none)”?

    Since Value Member is CID, an integer, then Selected Value should be a value that has sense.

    Friday, July 12, 2019 5:39 AM
  • Tried your suggestion, but it still caused an exception error!

    Woke up this morning with a thought!

    I was creating a variable CSizeValue  then was using it here:

     Private Sub btnPrint_Click(sender As Object, e As EventArgs) Handles btnPrint.Click
    
            Using connection As New SqlConnection(connectionString), da As New SqlDataAdapter("SELECT CID, BoxX, Cellw, Cellh, ort, Narrative FROM CSize WHERE CID = @CID", connection), dt As New DataTable
                    connection.Open()
    
                da.SelectCommand.Parameters.AddWithValue("@CID", CSizeValue)
                    da.Fill(dt)
                    If dt.Rows.Count > 0 Then
                        Dim row As DataRow = dt.Rows(0)
                        printIt(row)
    
    
    
                End If
    
            End Using
    
    
        End Sub

    This value is by default a string, and I have tried converting it to an integer but you can't do that!

    Tried this:

     Private Sub cboCSize_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboCSize.SelectedIndexChanged
            CID = cboCSize.ValueMember
            CSizeValue = CInt(CSizeValue)
        End Sub

    Still get the MaxLength limit exception.

    So how can I get around this problem?


    TEH

    Friday, July 12, 2019 6:17 AM
  • Regarding the following:Question

    Does it work if you do not specify Selected Value, i.e. set it to “(none)”?

    Since Value Member is CID, an integer, then Selected Value should be a value that has sense.

    Can't change the selected value box to 'None' as once you enter a value in there that option is no longer available?


    TEH

    Friday, July 12, 2019 7:03 AM
  • When you set the field to nvarchar(MAX) where exactly did you do this. For this to work you can't do this anyplace but directly in the database, can't be done in through a TableAdapter by selecting a field in a selected table in a .xsd file which shows tables for a database as this is local not at the database table level. To properly do this a ALTER statement is needed or do this via SQL-Server Management Studio. Then the .xsd needs to be regenerated.

    Another thing you can do is use something like Dim SomeItem As Char rather than SomeItem As String where a Char is a match for a single character in the database table.

    Any ways what I would suggest now is to use assertion to ensure only one char is being sent. Since this works up to 10 records the assertion to ensure one char only is being sent to the field in the table.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, July 12, 2019 10:38 AM
  • Here is a "Clinical" way to look at the problem which also encompasses assertion as per my last reply.

    NOTE I realize this entire code sample is very different from how you are performing actions but no matter it shows that by constraining ort to a char that it's completely impossible for data going to the table can be a problem which means if this were to fail it's an issue back at the table or with a TableAdapter definition of the column.

    Imports System.Data.SqlClient
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim ops As New DataOperations
            Dim listItems = New List(Of Item) From {
                New Item() With {.Ort = "P"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "L"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "P"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "L"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "P"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "L"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "P"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "L"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "L"c, .Narrative = "1234567890"},
                New Item() With {.Ort = "L"c, .Narrative = "1234567890"}
            }
    
            For Each o As Item In listItems
                ops.Insert(o.Ort, o.Narrative)
            Next
    
        End Sub
    End Class
    Public Class DataOperations
        Private ConnectionString As String =
                    "Data Source=KARENS-PC;" &
                    "Initial Catalog=ForumExample;Integrated Security=True"
    
        Public Sub Insert(pOrt As Char, pNarrative As String)
    
            Const insertStatement As String =
                      "INSERT INTO dbo.CSize (ort,Narrative) VALUES (@ort,@Narrative)"
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.Parameters.AddWithValue("@ort", pOrt)
                    cmd.Parameters.AddWithValue("@Narrative", pNarrative)
                    cmd.CommandText = insertStatement
    
                    cn.Open()
    
                    cmd.ExecuteNonQuery()
    
                End Using
            End Using
        End Sub
    End Class
    Public Class Item
        Public Property Ort() As Char
        Public Property Narrative() As String
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, July 12, 2019 11:23 AM
  • [...]

    Can't change the selected value box to 'None' as once you enter a value in there that option is no longer available?

    If the “Selected Value” field has a drop-down list, then open it and select “None”.


    Friday, July 12, 2019 11:52 AM