#Region "Imports"
Imports System.Data.SqlClient
Imports System.Text
#End Region
Public Class StudentRecordForm
#Region "Record Types"
Private Enum RecordType
[NameType] = 0
[GradeType]
End Enum
#End Region
Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)
Me.cboName.DataSource = Me.GetStudentNames(RecordType.NameType)
Me.cboName.DisplayMember = "Record"
Me.SetDefault(RecordType.NameType, "John")
Me.cboGrades.DataSource = Me.GetStudentNames(RecordType.GradeType)
Me.cboGrades.DisplayMember = "Record"
Me.SetDefault(RecordType.GradeType, "A")
End Sub
#Region "Data Source Assignment"
Private Function GetStudentNames(ByVal type As RecordType) As ICollection(Of String)
Dim returnList As ICollection(Of String) = New List(Of String)
Dim queryString As New StringBuilder
queryString.AppendLine("Select [Student_Data].[StudentRecords] As Record From [Student_Data]")
Select Case type
Case RecordType.NameType
queryString.AppendLine(String.Format(My.Application.Culture, "Where [Student_Data].[StudentDetail] = '{0}'", "StudentName"))
Case RecordType.GradeType
queryString.AppendLine(String.Format(My.Application.Culture, "Where [Student_Data].[StudentDetail] = '{0}'", "StudentGrade"))
End Select
Using connection As IDbConnection = Me.GetConnection
Using command As IDbCommand = connection.CreateCommand
command.CommandTimeout = 0
command.CommandType = CommandType.Text
command.CommandText = queryString.ToString
Using reader As IDataReader = command.ExecuteReader(CommandBehavior.SequentialAccess)
While (reader.Read)
returnList.Add(CStr(reader("Record")))
End While
End Using
End Using
End Using
Return returnList
End Function
Private Function GetConnection() As SqlConnection
Dim connection As New SqlConnection
Dim connectionString As New SqlConnectionStringBuilder
Try
connectionString.DataSource = "ADITY\SQLEXPRESS"
connectionString.InitialCatalog = "BillingPro"
connectionString.IntegratedSecurity = True
connectionString.PersistSecurityInfo = False
connection.ConnectionString = connectionString.ToString
connection.Open()
Catch sqlEx As SqlException
MessageBox.Show(sqlEx.Message)
Catch openEx As InvalidOperationException
MessageBox.Show(openEx.Message)
End Try
Return connection
End Function
#End Region
#Region "Set Default"
Private Sub SetDefault(ByVal type As RecordType, ByVal defaultValue As String)
Select Case type
Case RecordType.NameType
If (Me.cboName.Items.Count > 0) Then
Me.cboName.SelectedIndex = -1
For intIndex As Integer = 0 To Me.cboName.Items.Count - 1
If (String.Compare(defaultValue, Me.cboName.Items(intIndex).ToString, StringComparison.CurrentCultureIgnoreCase) = 0) Then
Me.cboName.SelectedIndex = intIndex
Exit For
End If
Next
End If
Case RecordType.GradeType
If (Me.cboGrades.Items.Count > 0) Then
Me.cboGrades.SelectedIndex = -1
For intIndex As Integer = 0 To Me.cboGrades.Items.Count - 1
If (String.Compare(defaultValue, Me.cboGrades.Items(intIndex).ToString, StringComparison.CurrentCultureIgnoreCase) = 0) Then
Me.cboGrades.SelectedIndex = intIndex
Exit For
End If
Next
End If
End Select
End Sub
#End Region
End Class
Hope, this helps you friend. Please let me know if I understood anything wrong here. In the attached code snippet; I have taken the following constants ("ADITY\SQLEXPRESS" which is the name of my SQL Server instance and "BillingPro" which is my database name).