How to display only selected data in combo box at run time from database?

Question

0.00/5 (No votes)

See more:

, +

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 6-Dec-12 11:20am

Joy1979

Add a Solution

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. :)

Nolensville 7-Dec-12 13:21pm

Sorry didn't cut and paste the whole query. It is:
<pre lang="sql">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
Where StudentDetail = "Student Name"</pre>

Nolensville 7-Dec-12 21:44pm

You aren't going to use any keys to join because you are trying to get every combination possible. The query is only returning the data headers because it isn't returning any results to your result set. Looking at what you wrote and my query, It looks as if the where statement in the SQL is off. Looks like a put a space in the "Student Grade" when it should have been "StudentGrade"

Try this:
<pre lang="sql">SELECT (Student_data.StudentDetail + ' ' + Student_data.StudentRecords + ' ' + t1.gradedesc + ' ' + t1.grade) as Txt4CmbBox
FROM (SELECT Student_data.StudentDetail as gradedesc, Student_data.StudentRecords as grade
FROM Student_data
Where StudentDetail = "StudentGrade") AS t1, Student_data
Where StudentDetail = "StudentName"</pre>

Hopefully, this will work.

Joy1979 10-Dec-12 10:02am

Hi, Thanks a lot for your help. Yes It worked!!

2 solutions

Solution 2

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

Posted 10-Dec-12 16:20pm

ACE_

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Debasis.logica · Accepted Answer · 2012-12-09T01:33:00

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).