Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server ADO.NET , +
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
Joy1979224
Comments
joshrduncan2012 at 6-Dec-12 17:25pm
   
I'd suggest looking at how parameterized queries work.
Akbar Ali Hussain at 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 at 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 at 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 at 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 at 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 at 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 at 10-Dec-12 10:02am
   
Hi, Thanks a lot for your help. Yes It worked!!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

#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).
  Permalink  
v2
Comments
Joy1979 at 10-Dec-12 10:07am
   
Hello Debasis.Logica..Thanks a lot for your help. I am able to get expected result with this code.
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  

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



Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 10 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid