Click here to Skip to main content
16,021,757 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am new to .Net and I am working on one task. Below is my scenario.

I have 2 tables:

Table 1: Students

StudentID   StudentDetail
------------------------------   
1           StudentName 
2           StudentGrade


Table 2: Student_data

StudentDetail           StudentRecords
---------------------------------------------
StudentName         John (Default)

StudentName         Jacob

StudentName         Smith

StudentGrade            A    (default)

StudentGrade            B

StudentGrade            C
Question: When window form loads (run time) I need to display StudentRecords in combo box with StudentName "John" and StudentGrade "A" as default followed by other values in it.

StudentName and StudentRecords are in lables and values are in combo box.

I am using VB.Net and VS 2010 with SQL 2008r2.

I would appreciate any step by step help. Apologies If my request is simple.
Posted
Comments
joshrduncan2012 6-Dec-12 17:25pm    
I'd suggest looking at how parameterized queries work.
Akbar Ali Hussain 6-Dec-12 17:31pm    
Why cant you keep StudentName and StudentGrade in two different columns. Keep "John" and "A" in the first row and fetch the values. So that you can consider values in the first row as default. Or create one more column IsDefault (Yes/No).
Joy1979 7-Dec-12 17:08pm    
Thanks for you reply, I agree with your suggestion and I have tried that before but let's say I need to stick to above mentioned data model as task requirement.
Nolensville 7-Dec-12 13:18pm    
The key to getting what you want is in your sql. If you set a Data Source control with a nested query you should be able to get what you want. Here is the query in Access, It should work in SQL Server too.
"SELECT Student_data.StudentDetail, Student_data.StudentRecords, t1.gradedesc, t1.grade
FROM (SELECT Student_data.StudentDetail as gradedesc, Student_data.StudentRecords as grade
FROM Student_data
Where StudentDetail = "Student Grade") AS t1, Student_data"

Results:
StudentDetail StudentRecords gradedesc grade
Student Name John (Default) Student Grade A
Student Name John (Default) Student Grade B
Student Name John (Default) Student Grade C
Student Name Jacob Student Grade A
Student Name Jacob Student Grade B
Student Name Jacob Student Grade C

If you concantate all the fields, you can then load this in the combo box.

Let me know if that helps or you have any questons regarding the vb needed to load the combo box.
Joy1979 7-Dec-12 17:49pm    
Hi, Thanks for your reply and query. It works perfectly fine but when I execute it as Result I get only Headers without data.(i.e. "StudentDetail StudentRecords gradedes grade")only. I do have data in tables. Am I missing something? any PK/FK relationship? between those two tables? Sorry If I am asking basic question as I am new to .Net and trying to learn things. :)

VB
#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).
 
Share this answer
 
v2
Comments
Joy1979 10-Dec-12 10:07am    
Hello Debasis.Logica..Thanks a lot for your help. I am able to get expected result with this code.
In event form load write this example code

Open_Database() 
Dim Query As String = "Select StudentName from Student"
Cmd = New SqlCommand(Query,Con)
Dr = Cmd.ExecuteReader
While Dr.Read
StudentName_Cbo.Items.Add(Dr("StudenName"))
End While
Dr.Close
Close_Database()

StudentName_Cbo.SelectedIndex = 0
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900