B
Boget1
Guest
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
Continue reading...
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
Continue reading...