none
ComboBox not reacting the way I thought. RRS feed

  • Question

  • I have a Win Form that displays personnel information.  The form is bound to a "Profiles" table. Within the form I have two drop down style combo boxes which allows the user to select a state from a "States" table and a group from a "Levels" table.  Both tables contain two columns: "State" has a PK string ID ("Abrev") in column 1 and the full state name in column 2.  "Levels" has an auto integer PK ("GID") in column 1 and a string name in column 2. The comboboxes are FK to their respective tables.  The relationship is many-to-one from "Profiles" to the respective lookup tables.  All tables are in SQL Server .MDF files.

    My goal is to display a name lists (col2) in the respective combobox and update the respective "Profiles" column with the lookup table IDs.  The combos loads the desired display data, the drop down shows the names in each list however:

    1. Using information already in the 'Profiles' table, the first profile record shows the first record name in each respective combo box, not the intended information in the "Profiles" table.

    2. When I advance through the records the lookup table's key (Abrev and GID values) appears in the combo boxes, the drop list still shows the name list.

    3. If I select a list name it appears in the combo box (as intended), but when I advance to the next record the app breaks and displays "Cannot set column 'State'. The value violates the MaxLength limit of this column" (the maxlength in the 'Profiles.State ' column is nchar(2) which should hold the state's abbreviation).  This this does not occur with the  'GroupComboBox'.

    I've done this before  . . . a long, long time ago and never had these problems.  I've spent way to much time on this and need a second look . . . I'm obviously doing something wrong.

    Here's my code:

    Form_Load -

        ' Fill comboboxes with lookup table values.
        FillCombo("SELECT Abrev, StateName FROM States", "StateName", "Abrev", StateComboBx)
        FillCombo("SELECT GID, GroupName FROM Levels", "GroupName", "GID", GroupComboBx)

    Utilities Module -

    # Region " Fill Combox Method "

      Public Sub FillCombo(ByVal query As String, ByVal dMember As String, ByRef vMember As Object, ByRef cBox As ComboBox)

        'Insure the combobox Is empty.
        cBox.Items.Clear()
        Try
          'Connect to the lookup table.
          Using con As SqlConnection = New SqlConnection(userConStr)
            'Query the lookup table for desired items.
            Using da As SqlDataAdapter = New SqlDataAdapter(query, con)
              'Fill a DataTable with the desired items.
              Dim dt As DataTable = New DataTable()
              da.Fill(dt)
              'Place desired items in the combobox display & value attributes.
              cBox.DisplayMember = dMember
              If TypeOf vMember Is Integer Then
                cBox.ValueMember = CType(vMember, String)
              Else
                cBox.ValueMember = vMember.ToString
              End If
              'Assign the DataTable as a DataSource.
              cBox.DataSource = dt
            End Using
          End Using
        Catch ex As Exception
          UserMsg(3, ex.ToString)
        End Try

      End Sub

    #End Region

    Thank you for your prompt help with this dilemma.



    Boget1

    mercredi 21 octobre 2020 22:02

Toutes les réponses

  • Hi Boget1,

    Thank you for posting here.

    >>when I advance to the next record the app breaks and displays "Cannot set column 'State'. The value violates the MaxLength limit of this column"

    I make a test based on your code, but I cannot reproduce your problem.

    Result of my test.

    Could you provide more related code here? It will help us analyze your problem.

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    jeudi 22 octobre 2020 08:56
    Modérateur
  • Thank you for your quick response Xingyu, sorry for my delay.

    I'm not sure what "more related code" you are asking for therefore I am providing you with what I have developed thus far plus the full version of code I've written.

    Code -

      Private Sub MemberProfileForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        ' Loads database tables into the appropriate table adapter.
        Try
          ProfilesTableAdapter.Fill(MembersDataSet.Profiles)
          AccessTableAdapter.Fill(MembersDataSet.Access)
        Catch ex As Exception
          UserMsg(3, ex.ToString)
        End Try
    
        ' Fill comboboxes with lookup table values.
        FillCombo("SELECT Abrev, StateName FROM States", "StateName", "Abrev", StateComboBx)
        FillCombo("SELECT GID, GroupName FROM Levels", "GroupName", "GID", GroupComboBx)
    
        ' Insure ComboBoxes display the first-up record.
        FirstUp("State", StateComboBx)
        FirstUp("Group", GroupComboBx)
    
        ' Set beginning table row count.
        'dataRow = 0
    
        ' Places cursor in the Prefix textbox.
        PrefixComboBx.Select()
    
      End Sub
      Public Sub FillCombo(ByVal query As String, ByVal dMember As String, ByRef vMember As Object, ByRef cBox As ComboBox)
    
        'Insure the combobox Is empty.
        cBox.Items.Clear()
        Try
          'Connect to the lookup table.
          Using con As SqlConnection = New SqlConnection(userConStr)
            'query the lookup table for desired items.
            Using da As SqlDataAdapter = New SqlDataAdapter(query, con)
              'Fill a DataTable with the desired items.
              Dim dt As DataTable = New DataTable()
              da.Fill(dt)
    
              '' Insert a Default Item into the DataTable.
              'Dim row As DataRow = dt.NewRow()
              'row(0) = 0
              'row(1) = "Please select"
              'dt.Rows.InsertAt(row, 0)
    
              'Place desired items in the combobox display & value attributes.
              cBox.DisplayMember = dMember
              If TypeOf vMember Is Integer Then
                cBox.ValueMember = CType(vMember, String)
              Else
                cBox.ValueMember = vMember.ToString
              End If
              'Assign the DataTable as a DataSource.
              cBox.DataSource = dt
            End Using
          End Using
        Catch ex As Exception
          UserMsg(3, ex.ToString)
        End Try
    
    
      ' Work around bug where ComboBoxes may not properly display the first-up record.
      Public Sub FirstUp(ByVal comboItem As String, ByRef combo As ComboBox)
    
        If Not IsDBNull(MembersDataSet.Tables("Profiles").Rows.Item(dataRow).Item(comboItem)) Then
          Dim firstKey As Object = MembersDataSet.Tables("Profiles").Rows.Item(dataRow).Item(comboItem)
          'Display first - up State value.
          If IsNothing(firstKey) Then
            combo.SelectedValue = Nothing
          Else
            combo.SelectedValue = firstKey
          End If
        End If
    
      End Sub
    

    Schema -


    STATES TableAdapter(Fill, Getdata) –

    SELECT        States.Abrev, States.StateName, Profiles.State

    FROM            States INNER JOIN

                            Profiles ON States.Abrev = Profiles.State

    LEVELS TableAdapter(Fill, Getdata) –

    SELECT        Levels.GroupName, Levels.GID, Profiles.[Group]

    FROM            Levels INNER JOIN

                            Profiles ON Levels.GID = Profiles.[Group]


    PROFILES TableAdapter (Fill, Getdata) –

    SELECT UserID, Prefix, FirstName, MI, LastName, Suffix, Street, Appt, City, State, Zip, H_Phone, C_Phone, W_Phone, W_Ext, H_Email, W_Email, [Group], Notes FROM dbo.Profiles

      Form: 

    NOTE:

    1.  ID, Username &Password textboxes are for troubleshooting only.  Working as expected.

    2.  Form textboxes are bound to members dataset.  All working as expected.

    3.  State dropdown combobox Databindings (Text) – ProfilesBindingSource – State.

    4.  Group Assignment dropdown combobox Databindings (Text) – ProfilesBindingSource – Levels.

    5…BindingNavigator (ProfilesNavigator) BindingSource is bound to ProfilesBindingSource.

    Only the native navigation controls are used.  All other controls are coded.

    PROBLEMS:

    1.  Form loads properly except for STATE & GROUP ASSIGNMENT comboboxes.  They display the first record item in each respective table (i.e. STATE – Alaska, should be Kansas. GROUP – Administrator, is correct for user but not correct programmatically.).  The dropdown list is correct (i.e. shows StateName & GroupName).

    2.  As you move to the next records (record 2), both comboboxes display their respective key (i.e. KS for KANSAS, 101 for Administrator).  The dropdown list still shows the State Name & GroupName lists.

    3.  As you move through the records everything except the comboboxes display the proper information.  The comboboxes display the correct key (Abrev & GID) for the record but not the intended state or group name.

    4.  You can MoveNext, MovePrevious & MoveLast through all records with no problems (except combobox displays), however if you move to the first record & try moving through the records again the app breaks with this error message:

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

    5.  If you select a state from the drop down list the proper name appears in the combobox, however if you try moving to the next or previous record a break occurs showing the above error.  The Group box does not cause a break.

    I have tried commenting out the FirstUp method with no change in the above problems.

    I have not coded a ChangeSelect for the combos or gotten to the point of setting any changed values to the Profiles dataset or table and will not until I resolve these errors.

    Any help would be greatly appricated.

    Thank you,

    Boget1


    Boget1

    vendredi 23 octobre 2020 18:41
  • Hi Boget1,

    Thanks for your feedback.

    It seems that your adapters are out of sync with the database.

    Try deleting the dataset out of the project explorer and re-adding it via the "Add New Data Source" option in the project menu.

    For more details, you can refer to the following reference.

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

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    lundi 26 octobre 2020 06:43
    Modérateur