combo box selected saved item not diplayed

[gbhs]

Hi folks
Im a newbie.
I have a combobox cboTeachersName that is bound to deptTeachers table.
At runtime, it contains teachers of given subject (Course).
I then select a teacher and save in a field in another table called Summaries.

PROBLEM IS
When I load the form and load the saved record, the selected teacher is not the selected item in combobox.
combobox is empty.
I want that when I load the form and the saved record then combo should display saved teacher as the selecteditem
How can I make this happen?

Here is my code to fill combo (called in form load event)

Sub FillComboTeachersName()
        SQLDeptTeacher = "SELECT Teachers.teachersname  FROM DeptTeachers INNER JOIN Teachers ON 

Teachers.TeachersID=DeptTeachers.TeachersID INNER JOIN Depts ON Depts.DeptID=DeptTeachers.DeptID WHERE 

Depts.DeptName='" & Me.txtSubjectName.Text & "' "

        CmdDeptTeacher = New SqlCommand(SQLDeptTeacher, Conn)
        DRDeptTeacher = CmdDeptTeacher.ExecuteReader

        While (DRDeptTeacher.Read())

            With Me.cboTeachersName
                .Items.Add(DRDeptTeacher("teachersname"))
            End With

        End While

        CmdDeptTeacher.Dispose()
        DRDeptTeacher.Close()
    End Sub

Your help will be appreciated

[adam_k]

Can you share the code that you load the saved info? If the combo list has the values it should, then there is nothing wrong with this sub.

[tinstaafl]

You couild also set a breakpoint at line 14 and check whether DRDeptTeacher("teachersname") has any data in it.

[gbhs]

This is the code for the form ie ScoresForm.
Teachers of the subject(course) are actually loaded into combo box BUT I want the saved teacher to appear as first i.e to have selected index of 0 or show as selected item in combo box.

Also another slight problem in the SummaryCounts Sub is that if all Sat students have passed, then the txtFailed textbox must show 0. I get but a 1 in this case. I guess my code for doing the counts (from Datagrid dgvScores) is not correct.

Here below is my code:

Imports System
Imports System.Data
Imports System.Data.SqlClient

 Public Conn As New SqlConnection

    'The following will use the Summaries Table to populate the textboxes when user inputs correct Password
    Public CmdSummaries As New SqlCommand
    Public SQLSummaries As String
    Public DSSummaries As New DataSet
    Public DASummaries As New SqlDataAdapter

    'The following will use the Scores Table to populate the datagrid dgvScores based on Password from above Table
    Public CmdScores As New SqlCommand
    Public SQLScores As String
    Public DSScores As New DataSet
    Public DAScores As New SqlDataAdapter

    'The following will use the DeptTeachers Table to populate the Combobox cboTeachersName. Combobox will display only 
    'teachers of the selected SubjectName or DeptName textbox.
    Public CmdDeptTeacher As New SqlCommand
    Public SQLDeptTeacher As String
    Public DSDeptTeacher As New DataSet
    Public DADeptTeacher As New SqlDataAdapter
    Public DRDeptTeacher As SqlDataReader


Public Class ScoresForm

    Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click

        DSSummaries.Clear()
        DSScores.Clear()
        DSDeptTeacher.Clear()
        cboTeachersName.Items.Clear()
        dgvScores.Refresh()

        Conn.ConnectionString = "Server=tobin\sqlexpress;database=gbDbase;Trusted_Connection=True;"
        Try
            Conn.Open()
            MsgBox("connected")

            SQLSummaries = "SELECT SummariesID,summaries.sectionprefix,summaries.Pwd,Subjects.SubjectName,Classes.ClassName,Summaries.SequenceNo,Summaries.Coefficient, Summaries.HrsInPlan ,Summaries.HrsTaught,Summaries.TopicsInPlan, Summaries.TopicsTaught, Summaries.TeachersName, Summaries.Sat, Summaries.Passed, Summaries.Failed,  Summaries.BoysPassed, Summaries.BoysFailed, Summaries.GirlsPassed, Summaries.GirlsFailed, Summaries.ClassAverage FROM Summaries INNER JOIN Subjects ON Subjects.SubjectID=Summaries.SubjectID INNER JOIN Classes ON Classes.ClassID=Summaries.ClassID WHERE summaries.Pwd='" & Me.txtPassword.Text & "' "
            DASummaries = New SqlDataAdapter(SQLSummaries, Conn)
            DASummaries.Fill(DSSummaries, "Summaries")

            If DSSummaries.Tables("summaries").Rows.Count = 0 Then
                MsgBox("No Such Subject Class exists!!!Password must be 7 characters")
                Conn.Close()
                Me.txtPassword.Clear()
                Me.txtPassword.Focus()
            Else

                lblSummariesID.Text = DSSummaries.Tables("summaries").Rows(0).Item("summariesID")
                lblSectionPrefix.Text = DSSummaries.Tables("summaries").Rows(0).Item("sectionprefix")
                txtSubjectName.Text = DSSummaries.Tables("summaries").Rows(0).Item("subjectname")
                txtClassName.Text = DSSummaries.Tables("summaries").Rows(0).Item("classname")
                txtSequenceNo.Text = DSSummaries.Tables("summaries").Rows(0).Item("sequenceNo")
                txtCoeff.Text = DSSummaries.Tables("summaries").Rows(0).Item("coefficient")
                txtHrsInPlan.Text = DSSummaries.Tables("summaries").Rows(0).Item("HrsInPlan").ToString
                txtHrsTaught.Text = DSSummaries.Tables("summaries").Rows(0).Item("HrsTaught").ToString
                txtTopicsInPlan.Text = DSSummaries.Tables("summaries").Rows(0).Item("TopicsInPlan").ToString
                txtTopicsTaught.Text = DSSummaries.Tables("summaries").Rows(0).Item("TopicsTaught").ToString

                cboTeachersName.SelectedItem = DSSummaries.Tables("summaries").Rows(0).Item("teachersname").ToString

        txtSat.Text = DSSummaries.Tables("summaries").Rows(0).Item("Sat").ToString
                txtPassed.Text = DSSummaries.Tables("summaries").Rows(0).Item("Passed").ToString
                txtFailed.Text = DSSummaries.Tables("summaries").Rows(0).Item("Failed").ToString
                txtBoysPassed.Text = DSSummaries.Tables("summaries").Rows(0).Item("BoysPassed").ToString
                txtBoysFailed.Text = DSSummaries.Tables("summaries").Rows(0).Item("BoysFailed").ToString
                txtGirlsPassed.Text = DSSummaries.Tables("summaries").Rows(0).Item("GirlsPassed").ToString
                txtGirlsFailed.Text = DSSummaries.Tables("summaries").Rows(0).Item("Girlsfailed").ToString
                txtClassAverage.Text = DSSummaries.Tables("summaries").Rows(0).Item("ClassAverage").ToString


                'Fill Datagrid with Scores Table

                If Me.txtSequenceNo.Text = 1 Then
                    SQLScores = "SELECT ScoresID,Scores.ClassName,Scores.SubjectName,Scores.RollNo,Scores.StudentNames,Scores.Gender,Scores.Coeff1, Scores.Sequence1, Scores.Sequence2, Scores.Average1, Scores.Total1, Scores.Comment1 FROM Scores  WHERE scores.SubjectName='" & Me.txtSubjectName.Text & " ' and scores.ClassName='" & Me.txtClassName.Text & "' "
                ElseIf Me.txtSequenceNo.Text = 2 Then
                    SQLScores = "SELECT ScoresID,Scores.ClassName,Scores.SubjectName,Scores.RollNo,Scores.StudentNames,Scores.Gender,Scores.Coeff1, Scores.Sequence2, Scores.Sequence1, Scores.Average1, Scores.Total1, Scores.Comment1 FROM Scores  WHERE scores.SubjectName='" & Me.txtSubjectName.Text & " ' and scores.ClassName='" & Me.txtClassName.Text & "' "
                ElseIf Me.txtSequenceNo.Text = 3 Then
                    SQLScores = "SELECT ScoresID,Scores.ClassName,Scores.SubjectName,Scores.RollNo,Scores.StudentNames,Scores.Gender, Scores.Coeff2, Scores.Sequence3, Scores.Sequence4, Scores.Average2, Scores.Total2, Scores.Comment2 FROM Scores WHERE scores.SubjectName='" & Me.txtSubjectName.Text & " ' and scores.ClassName='" & Me.txtClassName.Text & "' "
                ElseIf Me.txtSequenceNo.Text = 4 Then
                    SQLScores = "SELECT ScoresID,Scores.ClassName,Scores.SubjectName,Scores.RollNo,Scores.StudentNames,Scores.Gender, Scores.Coeff2, Scores.Sequence4, Scores.Sequence3, Scores.Average2, Scores.Total2, Scores.Comment2 FROM Scores WHERE scores.SubjectName='" & Me.txtSubjectName.Text & " ' and scores.ClassName='" & Me.txtClassName.Text & "' "
                ElseIf Me.txtSequenceNo.Text = 5 Then
                    SQLScores = "SELECT ScoresID,Scores.ClassName,Scores.SubjectName,Scores.RollNo,Scores.StudentNames,Scores.Gender, Scores.Coeff3, Scores.Sequence5, Scores.Sequence6, Scores.Average3, Scores.Total3, Scores.Comment3 FROM Scores WHERE scores.SubjectName='" & Me.txtSubjectName.Text & " ' and scores.ClassName='" & Me.txtClassName.Text & "' "
                ElseIf Me.txtSequenceNo.Text = 6 Then
                    SQLScores = "SELECT ScoresID,Scores.ClassName,Scores.SubjectName,Scores.RollNo,Scores.StudentNames,Scores.Gender, Scores.Coeff3, Scores.Sequence6, Scores.Sequence5, Scores.Average3, Scores.Total3, Scores.Comment3 FROM Scores WHERE scores.SubjectName='" & Me.txtSubjectName.Text & " ' and scores.ClassName='" & Me.txtClassName.Text & "' "
                End If
                DAScores = New SqlDataAdapter(SQLScores, Conn)
                DAScores.Fill(DSScores, "Scores")
                dgvScores.DataSource = DSScores.Tables("Scores")

                FillComboTeachersName()   'This subroutine is below
                SummaryCounts()
                txtHrsInPlan.Focus()
                txtHrsInPlan.BackColor = Color.LightGreen

                Conn.Close()
                MsgBox("connection closed")


            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

Sub FillComboTeachersName()
        SQLDeptTeacher = "SELECT Teachers.teachersname  FROM DeptTeachers INNER JOIN Teachers ON Teachers.TeachersID=DeptTeachers.TeachersID INNER JOIN Depts ON Depts.DeptID=DeptTeachers.DeptID WHERE Depts.DeptName='" & Me.txtSubjectName.Text & "'  "
        CmdDeptTeacher = New SqlCommand(SQLDeptTeacher, Conn)
        DRDeptTeacher = CmdDeptTeacher.ExecuteReader
        While (DRDeptTeacher.Read())
            With Me.cboTeachersName
                .Items.Add(DRDeptTeacher("teachersname"))
            End With
        End While
        CmdDeptTeacher.Dispose()
        DRDeptTeacher.Close()
    End Sub

 Private Sub dgvScores_Cellenter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvScores.CellEnter

        SummaryCounts()

    End Sub

Private Sub SummaryCounts()
        Dim countSat As Integer = 0
        Dim countPassed As Integer = 0
        Dim countFailed As Integer = 0
        Dim countBoysPassed As Integer = 0
        Dim countBoysFailed As Integer = 0
        Dim countGirlsPassed As Integer = 0
        Dim countGirlsFailed As Integer = 0
        Dim tot As Double = 0
        Dim avg As Double = 0

        For i As Integer = 0 To Me.dgvScores.Rows.Count - 1
            If Not IsDBNull(Me.dgvScores.Rows(i).Cells(7).Value) Then

                If Me.dgvScores.Rows(i).Cells(7).Value >= 0 Then
                    countSat = countSat + 1
                    Me.txtSat.Text = countSat
                End If

                If Me.dgvScores.Rows(i).Cells(7).Value >= 10 Then
                    countPassed = countPassed + 1
                    Me.txtPassed.Text = countPassed
                End If

                If Me.dgvScores.Rows(i).Cells(7).Value < 10 Then
                    countFailed = countFailed + 1
                    Me.txtFailed.Text = countFailed
                    Me.txtFailed.ForeColor = Color.Red
                End If

                If Me.dgvScores.Rows(i).Cells(7).Value >= 10 And Me.dgvScores.Rows(i).Cells(5).Value = "M" Then
                    countBoysPassed = countBoysPassed + 1
                    Me.txtBoysPassed.Text = countBoysPassed
                End If

                If Me.dgvScores.Rows(i).Cells(7).Value < 10 And Me.dgvScores.Rows(i).Cells(5).Value = "M" Then
                    countBoysFailed = countBoysFailed + 1
                    Me.txtBoysFailed.Text = countBoysFailed
                    Me.txtBoysFailed.ForeColor = Color.Red
                End If

                If Me.dgvScores.Rows(i).Cells(7).Value >= 10 And Me.dgvScores.Rows(i).Cells(5).Value = "F" Then
                    countGirlsPassed = countGirlsPassed + 1
                    Me.txtGirlsPassed.Text = countGirlsPassed
                End If

                If Me.dgvScores.Rows(i).Cells(7).Value < 10 And Me.dgvScores.Rows(i).Cells(5).Value = "F" Then
                    countGirlsFailed = countGirlsFailed + 1
                    Me.txtGirlsFailed.Text = countGirlsFailed
                    Me.txtGirlsFailed.ForeColor = Color.Red
                End If

                If Me.dgvScores.Rows(i).Cells(7).Value >= 0 Then
                    tot = tot + Me.dgvScores.Rows(i).Cells(7).Value
                    avg = Math.Round(tot / countSat, 2)
                    Me.txtClassAverage.Text = avg
                    If Me.txtClassAverage.Text < 10 Then
                        Me.txtClassAverage.ForeColor = Color.Red
                    Else
                        Me.txtClassAverage.ForeColor = Color.Blue
                    End If
                End If
            End If

        Next
    End Sub


    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        SummaryCounts()
        Dim CB As New SqlCommandBuilder(DAScores)

        Try

            Conn.Open()
            MsgBox("Connection Open")
            CmdSummaries = New SqlCommand("", Conn)
            With CmdSummaries
                .CommandText = "Update Summaries SET hrsinplan=@hrsinplan,hrstaught=@hrstaught,topicsinplan=@topicsinplan,topicstaught=@topicstaught,teachersName=@teachersname, sat=@sat , passed=@passed, failed=@failed, boyspassed=@boyspassed, boysfailed=@boysfailed, girlspassed=@girlspassed, girlsfailed=@girlsfailed,classaverage=@classaverage WHERE summariesID='" & Me.lblSummariesID.Text & " ' "

                '.Parameters.Add("@summariesid", SqlDbType.Int, 3).Value = Me.lblSummariesID.Text
                .Parameters.Add("@hrsinplan", SqlDbType.Int, 5).Value = Val(Me.txtHrsInPlan.Text)
                .Parameters.Add("@hrstaught", SqlDbType.Int, 5).Value = Val(Me.txtHrsTaught.Text)
                .Parameters.Add("@topicsinplan", SqlDbType.Int, 5).Value = Val(Me.txtTopicsInPlan.Text)
                .Parameters.Add("@topicstaught", SqlDbType.Int, 5).Value = Val(Me.txtTopicsTaught.Text)
                .Parameters.Add("@teachersname", SqlDbType.VarChar, 30).Value = Me.cboTeachersName.SelectedItem
                .Parameters.Add("@sat", SqlDbType.Int, 5).Value = Val(Me.txtSat.Text)
                .Parameters.Add("@passed", SqlDbType.Int, 5).Value = Val(Me.txtPassed.Text)
                .Parameters.Add("@failed", SqlDbType.Int, 5).Value = Val(Me.txtFailed.Text)
                .Parameters.Add("@boyspassed", SqlDbType.Int, 5).Value = Val(Me.txtBoysPassed.Text)
                .Parameters.Add("@boysfailed", SqlDbType.Int, 5).Value = Val(Me.txtBoysFailed.Text)
                .Parameters.Add("@girlspassed", SqlDbType.Int, 5).Value = Val(Me.txtGirlsPassed.Text)
                .Parameters.Add("@girlsfailed", SqlDbType.Int, 5).Value = Val(Me.txtGirlsFailed.Text)
                .Parameters.Add("@classaverage", SqlDbType.Float, 10).Value = Val(Me.txtClassAverage.Text)
                .ExecuteNonQuery()

            End With

            CmdScores = New SqlCommand(SQLScores, Conn)
            DAScores.Update(DSScores.Tables(0))
            DSScores.AcceptChanges()

            MsgBox("Data Saved Successfully")

        Catch

            'Finally

            Conn.Close()
            MsgBox("Connection closed")
        End Try

    End Sub


End Class

[adam_k]

Your FillComboTeachersName() sub will only populate the combobox, you need to select the item with the appropriate name. Since you are using DSSummaries.Tables("summaries").Rows(0) to populate everything else that is stored in summaries, I suggest you use that approach to assign to selectedvalue whatever value you've got saved in summaries.

[jelly04]

hi!
i want to join combobox from form2 with checklist from form5 if some1 select something from combobox example A4 or D4 also iwant it to be selected on checklist from form5 because value from combobox=checklist. in vb6.