asked on

Can 3 custom combo box code be in form load event? I have 3 combo boxes displaying data from database. Causing error,If I place the code in form load event.

Hi all,
I am getting following error When I place 3 combo box code in same load event. When I put Combo box code in seperate dropdown events like ex- Private Sub CmbCategory_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmbCategory.DropDown

The combo box works fine with seperate events. I have same code I combined in form load I get this following error at the 2nd combox code.
Error:
An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll
Additional information: Cast from type 'DBNull' to type 'String' is not valid.

The error is in this line
If CType(.Items(bIndex)(0), String).Trim = LabelStation.Text.Trim Then

Here is my full load event with all 3 combox code
Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.Text = "Repair Information Form"
fraNotes.Visible = False
'Displaying Date and Time for LabelDate
LblToday.Text = "Todays Data - " & Date.Today
Timer1.Enabled = True
'---------------------- Category Combo Box Code--------------------------------------
' create a connection string
Dim conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(CONNECT_STRING)
'OLEDB Connection
Dim Constr As String = OLEDBADAPTER_CONNECT_STRING
Dim connOledb As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(Constr)
Dim strSQLDefectCategory As String
strSQLDefectCategory = "Select DefectKey,Defect From dbo.wtbl_Defects"

' Create data adapter object
Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(strSQLDefectCategory, connOledb)
' Create a dataset object and fill with data using data adapter's Fill method
Dim dsCombo As DataSet = New DataSet
da.Fill(dsCombo, "Defects")

'Create and populate the DataTable to bind to the ComboBox:
Dim dt As New DataTable
dt.Columns.Add("DefectKey", GetType(System.Int32))
dt.Columns.Add("Defect", GetType(System.String))

' Populate the DataTable to bind to the Combobox.
Dim drDSRow As DataRow
Dim drNewRow As DataRow

For Each drDSRow In dsCombo.Tables("Defects").Rows()
drNewRow = dt.NewRow()
drNewRow("DefectKey") = drDSRow("DefectKey")
drNewRow("Defect") = drDSRow("Defect")
dt.Rows.Add(drNewRow)
Next
'Bind the DataTable to the ComboBox by setting the Combobox's DataSource property to the DataTable. To display the "Description" column in the Combobox's list, set the Combobox's DisplayMember property to the name of column. Likewise, to use the "Code" column as the value of an item in the Combobox set the ValueMember property.
CmbCategory.DropDownStyle = ComboBoxStyle.DropDownList

With CmbCategory
.DataSource = dt
.DisplayMember = "Defect"
.ValueMember = "DefectKey"
.SelectedIndex = 0
End With

'To select an item based on the "Code" or ValueMember property:
Dim aIndex As Integer
With CmbCategory
For aIndex = 0 To .Items.Count - 1
If CType(.Items(aIndex)(0), String).Trim = LabelCategory.Text.Trim Then
.SelectedIndex = aIndex
Exit For
End If
Next

If aIndex >= .Items.Count Then .SelectedIndex = -1
End With
'---------------------End Category Combo Box Code---------------------------------------

'---------------------Station Combo Box Code--------------------------------------------
' create a connection string
'Dim conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(CONNECT_STRING)
'OLEDB Connection
'Dim Constr As String = OLEDBADAPTER_CONNECT_STRING
'Dim connOledb As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(Constr)

Dim strSQLStations As String
strSQLStations = "Select StationKey,Station From dbo.wtbl_Stations"

' Create data adapter object
Dim daStations As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(strSQLStations, connOledb)
' Create a dataset object and fill with data using data adapter's Fill method
Dim dsComboStations As DataSet = New DataSet
daStations.Fill(dsComboStations, "Stations")

'Create and populate the DataTable to bind to the ComboBox:
'Dim dt As New DataTable
dt.Columns.Add("StationKey", GetType(System.Int32))
dt.Columns.Add("Station", GetType(System.String))
' Populate the DataTable to bind to the Combobox.
'Dim drDSRow As DataRow
'Dim drNewRow As DataRow
For Each drDSRow In dsComboStations.Tables("Stations").Rows()
drNewRow = dt.NewRow()
drNewRow("StationKey") = drDSRow("StationKey")
drNewRow("Station") = drDSRow("Station")
dt.Rows.Add(drNewRow)
Next
'Bind the DataTable to the ComboBox by setting the Combobox's DataSource property to the DataTable. To display the "Description" column in the Combobox's list, set the Combobox's DisplayMember property to the name of column. Likewise, to use the "Code" column as the value of an item in the Combobox set the ValueMember property.
CmbStation.DropDownStyle = ComboBoxStyle.DropDownList

With CmbStation
.DataSource = dt
.DisplayMember = "Station"
.ValueMember = "StationKey"
.SelectedIndex = 0
End With

'To select an item based on the "Code" or ValueMember property:
Dim bIndex As Integer
With CmbStation
For bIndex = 0 To .Items.Count - 1
If CType(.Items(bIndex)(0), String).Trim = LabelStation.Text.Trim Then
.SelectedIndex = bIndex
Exit For
End If
Next
If bIndex >= .Items.Count Then .SelectedIndex = -1
End With
'---------------------End Station Combo Box Code--------------------------------------------
'---------------------Component Description Combo box code--------------------------------------------
' create a connection string
'Dim conn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(CONNECT_STRING)
'OLEDB Connection
'Dim Constr As String = OLEDBADAPTER_CONNECT_STRING
'Dim connOledb As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(Constr)

Dim strSQLDescription As String
strSQLDescription = "Select ComponentID,ComponentDescription From dbo.wtbl_ComponentDescription"

' Create data adapter object
Dim daDescription As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(strSQLDescription, connOledb)
' Create a dataset object and fill with data using data adapter's Fill method
Dim dsComboDescription As DataSet = New DataSet
daDescription.Fill(dsComboDescription, "ComponentDescription")

'Dim dt As New DataTable

'Create and populate the DataTable to bind to the ComboBox:
dt.Columns.Add("ComponentID", GetType(System.Int32))
dt.Columns.Add("ComponentDescription", GetType(System.String))

'Dim drDSRow As DataRow
'Dim drNewRow As DataRow
For Each drDSRow In dsComboDescription.Tables("ComponentDescription").Rows()
drNewRow = dt.NewRow()
drNewRow("ComponentID") = drDSRow("ComponentID")
drNewRow("ComponentDescription") = drDSRow("ComponentDescription")
dt.Rows.Add(drNewRow)
Next
'Bind the DataTable to the ComboBox by setting the Combobox's DataSource property to the DataTable. To display the "Description" column in the Combobox's list, set the Combobox's DisplayMember property to the name of column. Likewise, to use the "Code" column as the value of an item in the Combobox set the ValueMember property.
CmbDescription.DropDownStyle = ComboBoxStyle.DropDownList

With CmbDescription
.DataSource = dt
.DisplayMember = "ComponentDescription"
.ValueMember = "ComponentID"
.SelectedIndex = 0
End With

''To select an item based on the "Code" or ValueMember property:
'Dim cIndex As Integer
'With CmbDescription
' For cIndex = 0 To .Items.Count - 1
' If CType(.Items(cIndex)(0), String).Trim = LabelComponent.Text.Trim Then
' .SelectedIndex = cIndex
' Exit For
' End If
' Next

' If cIndex >= .Items.Count Then .SelectedIndex = -1
'End With
'--------------------- End Component Description Combo Box Code----------------------------------
End Sub

Can any one help me why the code is not working.

Thanks in advance

ctm5

So the problem probably has nothing to do with the three comboboxes, I think. It has to do with the value of one of the items you are testing. You need to add a test to make sure the value of neither item is not null.

If Not .Items(bIndex)(0) Is Nothing And LabelStation.Text.Trim Is Nothing Then
If CType(.Items(bIndex)(0), String).Trim = LabelStation.Text.Trim Then
.....
End If
End If

ctm5

ctm5

Oh, wait... error message is DBNull. So the test has to be:

If Not IsDBNull(.Items(bIndex)(0)) And Not LabelStation.Text.Trim Is Nothing Then

(two changes there--first part to Not IsDBNull, and then added a Not to second part.

ctm5

pallilu

ASKER

Hi Ctm5
Thanks for your response
I changed to your code now.See below. No error bu,. I have bunch of empty's values first and last in dropdown box in addition to Cmbstation values. Combox have 7 empty's in fornt and at last empty values in middle station values.
Can you please take a look into it.
With CmbStation
For bIndex = 0 To .Items.Count - 1
If Not IsDBNull(.Items(bIndex)(0)) And Not LabelStation.Text.Trim Is Nothing Then
If CType(.Items(bIndex)(0), String).Trim = LabelStation.Text.Trim Then
.SelectedIndex = bIndex
'If CType(.Items(bIndex)(0), String).Trim = LabelStation.Text.Trim Then
End If
End If

'If CType(.Items(bIndex)(0), String).Trim = LabelStation.Text.Trim Then
'.SelectedIndex = bIndex
Exit For
'End If
Next
If bIndex >= .Items.Count Then .SelectedIndex = -1
End With

Thanks
P

ctm5

For bIndex = 0 To .Items.Count - 1
If Not IsDBNull(.Items(bIndex)(0)) And Not LabelStation.Text.Trim Is Nothing Then
If CType(.Items(bIndex)(0), String).Trim = LabelStation.Text.Trim Then
.SelectedIndex = bIndex
'If CType(.Items(bIndex)(0), String).Trim = LabelStation.Text.Trim Then
End If
Else
If IsDBNull(.Items(bIndex)(0)) Then .Items(bIndex)(0)).Remove <-- this will remove those empty items

End If

ctm5

pallilu

ASKER

ctm5
I got the error below
An unhandled exception of type 'System.MissingMemberException' occurred in microsoft.visualbasic.dll

Additional information: Public member 'Remove' on type 'DBNull' not found.

Thanks

ctm5

Try this:

If IsDBNull(.Items(bIndex)(0)) Then .Items(bIndex).Remove

I think I'm reading your code correctly now.

ctm5

pallilu

ASKER

ctm5
I have same error
An unhandled exception of type 'System.MissingMemberException' occurred in microsoft.visualbasic.dll

Additional information: Public member 'Remove' on type 'DataRowView' not found.

Thanks
P

ctm5

Ack! I hate bound controls. Then you will have to handle the DBNull problems back when you build the dataset. Do you have any control over the database? You can prohibit null values there. But if the database already exists, you are stuck trying to fix them as you pull them from the database. You probably can do this back in your SQL command

Select DefectKey,Defect From dbo.wtbl_Defects WHERE Not IsNull(Defect)

or something like that.

ctm5

pallilu

ASKER

ctm5,
The database does not have Null values. When I put these combo box codes in droppeddown event, combo boxes just work fine with no empty values.

Only the problem s occuring when I put the 3 comboboxes in form load event. I am getting that empty values..
The only problem with having 3 different combox boxes code in dropeddown event the user cannot enter data with tab key and select the dropdownlist values with keys, because of dropdown events and not the click or load event.

So I decided to put that combo box code in form load event so combox has values when form loads and user can enter and select with tab keys.

Please advice what to do for tab key working for combo boxes code.

Thanks in advance
Palli

ASKER CERTIFIED SOLUTION

Sancler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pallilu

ASKER

Roger thanks a lot for your response.

The code worked just fine except Combo box values are showing the first value when the form loads. I am planning on fixing like adding selectedIndex= -1 in addition to selecyedindex=0.
For Example cmbcategory - can we add to selected index values?
With CmbCategory
.DataSource = dtDefects
.DisplayMember = "Defect"
.ValueMember = "DefectKey"
.SelectedIndex = 0
.SelectedIndex = -1
End With
Please advice weather this is correct?
Or is there any better way to do this , when form loads dropdown boxes are empty instead of showing first value.

Thanks again for your help.
Palli

pallilu

ASKER

Roger,
One more thing. The form is not adding new values to combo box. I have seperate forms for combo boes.
user got o Each form and add more values to combo boxes and with form combo box unload(Ex-frmcatergory,frmStation,Frmdescription), the main form loads with new item in that combox code in (Ex-cmbCategory,cmbStation,CmbDescription). That functionality is not working with your suugested one. Can we also do that in your code. I want to add new values from the seperate combo box form to main form.

Private Sub MnuEditCategory_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuEditCategory.Click
Me.Hide()
Dim FrmCategory As New FrmCategory
FrmCategory.ShowDialog()
Me.Show()

End Sub

Please help me.

Thanks
Pallilu

pallilu

ASKER

Roger,
If that functionality like i mentioned in last post has to work(Loading combo box with new values), Do I need to put that code in seperate combox droppeddown events(Back to my original code). If I do that seperate for each combox then The tab(Selecting combo box values with tab and ctl keys) is not working when user enters the information with tab for combox will not work.

I really appreciate, If you can help mw with this.

Thanks
Pallilu

Sancler

Pallilu

On your first question, putting

.SelectedIndex = 0
.SelectedIndex = -1

should be OK. Indeed just

.SelectedIndex = -1

ought to be OK, but there is a bug reported in the combobox - see this

http://support.microsoft.com/kb/327244

- and setting .SelectedIndex = 0 first is a recommended workaround.

On the other issue, I'm not sure I've followed it all - about changing forms and so on - but the basic technique to add an item to a databound combobox is to add it to the datasource. As soon as that has been done it is reflected in the combobox. Using cmbCategory as the example, filled in accordance with the code I posted earlier, the code to add an item would be

Dim dr As DataRow = dtDefects.NewRow
dr("DefectKey") = <newDefectKeyValue>
dr("Defect") = <newDefectValue>
dtDefects.Rows.Add(dr)

The one problem that you might have with that approach is that any sub or function in which you try to run it might not be able to "see" dtDefects. It was declared in the Form_Load sub and so only has "scope" for that. The best way to overcome this would be to move the declarations of the datasources -

Dim dtDefects As New DataTable

and so on - to some level where all your Forms can "see" them: e.g. add a Module and declare then

Public dtDefects As New DataTable

in that. Indeed, it might make sense to put all the code relating to filling the comboboxes and adding items to them in a separate, dedicated, Module.

Roger

pallilu

ASKER

Roger,
Then Should I put the combox code to a seperate dropdown events of combo boxes. because form loads only once then it will not know about the new rows added to the combo boxes.
Do you think dropdown events are best to get the combox values refresh when new rows are added.

Private Sub CmbCategory_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmbCategory.DropDown

End Sub

In this case User cannot enter combox information using tab key. User has to click on the dropdown to select an item from drodown box.
Is there any best way to refresh the combox new entered values after form loads. Please help me with code.

Thanks for your help
Palli

pallilu

ASKER

Roger,
I have an idea of duplicating same code in form load event and also dropdown event. It worked fine. But is it good to like that or I'll find any problems later because of having same code in form load and dropdown event?
Thanks
Palli

Sancler

Palli

Have you tried the code I've already given you in my last post? If you add the new rows to the datatable which is the combobox's .DataSource those new rows will immediately be in the combobox. YOu don't have to "refresh" the combobox, simply adding the items to the datasource is enough. If you want the user to see that the new rows have been added, you can set the

myComboBox.DroppedDown = True

at the end of the code that adds the new items.

Roger