<CLSCompliant(True)> Public Sub PopulateDropDownListFromDB_WithWhereClauseX1(ByVal cboName As Elegant.Ui.ComboBox, _
ByVal PullProtectionTools As Boolean, _
ByVal PullDispatcherConsultantNames As Boolean, _
ByVal PullComputerType As Boolean, _
ByVal IsDispatcher As Boolean, _
ByVal IsConsultant As Boolean, _
ByVal bUseAutoComplete As Boolean, _
ByVal bUseSQLDistinct As Boolean, _
ByVal GetDBConInfo As String, _
Optional ByVal WhereClause1 As String = Nothing, _
Optional ByVal WhereClause2 As Integer = 0)
'Define some new string variables.
Dim TableName, ColumnName As String
Try
'Create a new SQLCommand object.
Dim GetInformation As New SqlCommand
'Start a new database connection.
Using SetDatabaseConnection As New SqlConnection(GetDBConInfo)
'Open the database connection.
SetDatabaseConnection.Open()
If PullProtectionTools = True Then
'Set some string variables.
TableName = "ProtectionTools"
ColumnName = "ProgramName"
'Set some values to the SQLCommand object.
With GetInformation
.Connection = SetDatabaseConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetProtectionTools"
.Parameters.AddWithValue("@ProtectionToolType", WhereClause2)
End With
ElseIf PullComputerType = True Then
'Set some string variables.
TableName = "ComputerType"
ColumnName = "ComputerTypeName"
'Set some values to the SQLCommand object.
With GetInformation
.Connection = SetDatabaseConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetComputerType"
.Parameters.AddWithValue("@Manufacturer", WhereClause2)
End With
ElseIf PullDispatcherConsultantNames = True Then
'Set some string variables.
TableName = "DispatcherConsultantNames"
ColumnName = "DispatcherOrConsultantName"
If IsDispatcher = True _
And Not IsConsultant = True Then
'Set some values to the SQLCommand object.
With GetInformation
.Connection = SetDatabaseConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetDispatchers"
.Parameters.AddWithValue("@Dispatcher", WhereClause1)
End With
ElseIf IsConsultant = True _
And Not IsDispatcher = True Then
'Set some values to the SQLCommand object.
With GetInformation
.Connection = SetDatabaseConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetConsultants"
.Parameters.AddWithValue("@Consultant", WhereClause1)
End With
End If
End If
'Create a new SQLDataAdapter and DataSet.
Dim GetInformationDA As New SqlDataAdapter(GetInformation)
'Create a new DataSet.
Dim GetInformationDS As New DataSet(GetInformation.CommandText)
'Set the Current Culture of the DataSet.
GetInformationDS.Locale = CurrentCulture
'Clear the DataSet of any previously obtained database information.
GetInformationDS.Clear()
'Refill the DataAdapter with new information obtained from the database.
GetInformationDA.Fill(GetInformationDS, TableName)
With cboName
'First check to see if any records. If no records, add a default item.
Dim GetInformationDT As DataTable = New DataTable
'Set the culture of the DataTable.
GetInformationDT.Locale = CurrentCulture
'Assign the table to the DataTable.
GetInformationDT = GetInformationDS.Tables(TableName)
'Fill the ComboBox.
'Assign the database table to the DataSource property.
.DataSource = GetInformationDS.Tables(TableName)
'See if the ComboBoxes are autocomplete or not.
If bUseAutoComplete = True Then
.AutoCompleteMode = AutoCompleteMode.SuggestAppend
.AutoCompleteSource = AutoCompleteSource.ListItems
End If
'Assign the database column name to the DisplayMember and ValueMember properties.
.DisplayMember = ColumnName
.ValueMember = ColumnName
If GetInformationDT.Rows.Count > 0 Then 'Has records.
If .Editable = False Then 'Not editable.
'Select a default option.
.Text = "Please Select"
Else
'Set the DataSource to nothing because you cant edit if set to something.
.DataSource = Nothing
.Items.Add("[Please Add An Item]")
.SelectedIndex = 0
End If
End If
End With
'Close the database connection.
SetDatabaseConnection.Close()
End Using
Catch ex As SqlException
'Show the end user a message if an error is generated.
MessageBox.Show("There was an error retrieving data from table name " & TableName & "." & vbNewLine & vbNewLine & _
"We are going to prepopulate the dropdown menu with a default value; therefore, you can disregard the error." & vbNewLine & vbNewLine & _
"We highly suggest that you click on the plus sign next to the dropdown menu to add some options of your own." & vbNewLine & vbNewLine & _
"The reason why you got this error in the first place is:" & vbNewLine & _
"No data items in the database " & TableName & " to pull." & vbNewLine & vbNewLine & _
"The exception error is:" & vbNewLine & _
ex.Message, _
"Database Information Retrieval Issues.", _
MessageBoxButtons.OK, _
MessageBoxIcon.Error, _
MessageBoxDefaultButton.Button1, _
MessageBoxOptions.DefaultDesktopOnly, _
False)
'If no rows are returned to populate the ComboBox, this will return an error, prepopulate with [Please Add An Item]
With cboName
.DataSource = Nothing
.Items.Add("[Please Add An Item]")
.SelectedIndex = 0
End With
Catch ex As InvalidOperationException
MessageBox.Show(ex.Message, _
"Invalid Operation Exception.", _
MessageBoxButtons.OK, _
MessageBoxIcon.Error, _
MessageBoxDefaultButton.Button1, _
MessageBoxOptions.DefaultDesktopOnly, _
False)
Catch ex As NullReferenceException
MessageBox.Show(ex.Message, _
"Null Reference Exception.", _
MessageBoxButtons.OK, _
MessageBoxIcon.Error, _
MessageBoxDefaultButton.Button1, _
MessageBoxOptions.DefaultDesktopOnly, _
False)
End Try
End Sub
