[RESOLVED] records not displaying

[Simply Me]

Hi!
I am trying to display a record in my datagridview based on the value selected from a combobox. I tried the code below using SQL Management studio and it works perfectly.

SELECT DISTINCT tblSubjects.SubjectName, tblSubjects.YearLevel
                    FROM tblsubjects INNER JOIN 
                    tblSection ON tblSubjects.Yearlevel=tblsection.yearlevel 
                    WHERE dbo.tblSubjects.YearLevel = 'LC7'

but once I do it in vb.net I dont get any display in my datagridview.

Here's what I want to achieve....my combobox contains list of class section names, I have also two databindings for my yearlevel and dept on a textboxes.

I want to just display in my datagridview all the subjects belonging to a certain yearlevel in the first column and an empty 2nd column so that user can type/assign teachers who teaches the subjects displayed and then click on a save button to update/save all the records to another table name as teachersload.

I am supposed to display the subjects and beside it is a combobox in datagridview displaying all the teachers name from teachersfile table so that i get to choose from the list but then its been a big problem for me to do it. Hope someone can help me out here....

Thanks in advance...

BTW..here is what my code for now. No saving yet.

Imports System.Data.SqlClient
Public Class frmLoading
    Private cnn As New SqlConnection(My.Settings.MyConnectionString)

    Private Sub frmLoading_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If cnn.State = ConnectionState.Closed Then cnn.Open()

        'DISPLAY YEAR LEVEL IN CBOYEARLEVEL
        Using cmdSectionName As New SqlCommand("SELECT SectionName, YearLevel, Dept " & _
                    "FROM tblSection " & _
                    "WHERE Dept LIKE 'HS%' " & _
                    "ORDER BY YearLevel ASC", cnn)
            Using reader As SqlDataReader = cmdSectionName.ExecuteReader()
                Dim Table As New DataTable
                Table.Load(reader)
                cboSectionName.DataSource = Table
                cboSectionName.DisplayMember = "SectionName"
                txtYearLevel.DataBindings.Add("text", Table, "YearLevel")
                txtDept.DataBindings.Add("Text", Table, "Dept")
            End Using
        End Using


        Using command As New SqlCommand("SELECT DISTINCT tblSubjects.SubjectName, tblSubjects.YearLevel " & _
                    "FROM tblsubjects INNER JOIN " & _
                    "tblSection ON tblSubjects.Yearlevel=tblsection.yearlevel " & _
                    "WHERE tblSubjects.YearLevel = '@Year' ", cnn)

            With command.Parameters
                .AddWithValue("@Year", Me.txtYearLevel.Text.Trim)
            End With

            Using reader1 As SqlDataReader = command.ExecuteReader()
                Dim table1 As New DataTable
                table1.Load(reader1)

                dtgLoad.DataSource = table1
            End Using
        End Using
        'End Using
    End Sub

End Class

[jmcilhinney]

"WHERE tblSubjects.YearLevel = '@Year' "

Single quotes in SQL code are ONLY for literal strings. You are literally searching for the text '@Year'. You are using a parameter so get rid of the single quotes.

[Simply Me]

thanks jm...I'll try to do the combobox thing...hope I can have this figure out especially the saving/updating part.

[Simply Me]

I added a combobox in my Datagridview and added this code and call it on formload but then the record just displayed right next the combobox column and the subject names are not displayed anymore.

Private Function GetTeachersName() As DataTable
        If cnn.State = ConnectionState.Closed Then cnn.Open()

        Using cmdTeachers As New SqlCommand("SELECT Teacher FROM tblTeacherFile ", cnn)

            Using TeachReader As SqlDataReader = cmdTeachers.ExecuteReader()
                Dim TeachTable As New DataTable

                TeachTable.Load(TeachReader)
                TeachReader.Close()
                dtgLoad.DataSource = TeachTable


                Me.Column1.ValueMember = "Teacher"
                Me.Column1.DataSource = TeachTable
                Return TeachTable
            End Using
        End Using
        cnn.Close()

    End Function

[jmcilhinney]

Follow the CodeBank link in my signature and check out my thread on adding a combo box column to a DataGridView.

[Simply Me]

Im on the process of following your code submission...

[Simply Me]

got it working now...What's left is how do I get the selected value of the combobox and other columns of the selected value and then save it in another table.

for example there are 8 rows and each of the rows I assign a teacher..How do I loop through the datagridview and then update/save to my table?

anything for me to start with?

thanks again.

[jmcilhinney]

Can you provide a more thorough explanation of what you're doing?

[Simply Me]

I am actually assigning teachers loading..say I have 3 section for grade 5 and there are 5 subjects in grade 5 so all the subjects will display in the datagridview. The combox i added in datagridview contains the names of teachers. i'll just select the teacher from the combobox for each of the subjects of grade 5 and I will do it every section.

I hope my explanation is clear. Thanks.

[Simply Me]

Here's my code now

    Private cnn As New SqlConnection(My.Settings.MyConnectionString)

    Private Sub frmLoading_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If cnn.State = ConnectionState.Closed Then cnn.Open()

        'DISPLAY YEAR LEVEL IN CBOYEARLEVEL
        Using cmdSection As New SqlCommand("SELECT SectionName, YearLevel, Dept " & _
                    "FROM tblSection " & _
                    "WHERE Dept LIKE 'HS%' " & _
                    "ORDER BY YearLevel ASC", cnn)

            Using SecReader As SqlDataReader = cmdSection.ExecuteReader()
                Dim SecTable As New DataTable

                SecTable.Load(SecReader)
                SecReader.Close()

                cboSectionName.DataSource = SecTable
                cboSectionName.DisplayMember = "SectionName"
                txtYearLevel.DataBindings.Add("text", SecTable, "YearLevel")
                txtDept.DataBindings.Add("Text", SecTable, "Dept")
            End Using

        End Using

        Me.BindingSource1.DataSource = Me.GetTeachers()
        Me.Column1.DisplayMember = "Teacher"
        Me.Column1.ValueMember = "TeacherID"
        Me.Column1.DataSource = Me.BindingSource1

        Me.BindingSource2.DataSource = Me.GetSubjects()
        Me.dtgLoad.DataSource = Me.BindingSource2

        'Displaying drop down list on single mouse click
        dtgLoad.EditMode = DataGridViewEditMode.EditOnEnter
    End Sub

    Private Function GetTeachers() As DataTable
        Using cmdTeachers As New SqlCommand("SELECT TeacherID, Teacher, Dept " & _
                "FROM tblTeacherFile " & _
                "WHERE Dept = 'HS' ", cnn)

            Using TeachReader As SqlDataReader = cmdTeachers.ExecuteReader()
                Dim TeachTable As New DataTable

                TeachTable.Load(TeachReader)
                TeachReader.Close()

                Return TeachTable
            End Using
        End Using
    End Function

    Private Function GetSubjects() As DataTable
        Using cmdSubject As New SqlCommand("SELECT DISTINCT tblSubjects.SubjectName, " & _
                        "tblSubjects.SubjectID, tblSubjects.YearLevel, tblSubjects.dept " & _
                    "FROM tblsubjects INNER JOIN " & _
                    "tblSection ON tblSubjects.Yearlevel=tblsection.Yearlevel " & _
                    "WHERE tblSubjects.YearLevel = @year " & _
                        "AND tblSubjects.Dept = @Dept ", cnn)
            With cmdSubject.Parameters
                .AddWithValue("@Year", Me.txtYearLevel.Text.Trim)
                .AddWithValue("@Dept", Me.txtDept.Text.Trim)
            End With

            Using SubjReader As SqlDataReader = cmdSubject.ExecuteReader()
                Dim SubjTable As New DataTable

                SubjTable.Load(SubjReader)
                SubjReader.Close()

                Return SubjTable
            End Using
        End Using
    End Function

    Private Sub txtYearLevel_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtYearLevel.TextChanged
        Me.BindingSource1.DataSource = Me.GetTeachers()
        Me.Column1.DisplayMember = "Teacher"
        Me.Column1.ValueMember = "TeacherID"
        Me.Column1.DataSource = Me.BindingSource1

        Me.BindingSource2.DataSource = Me.GetSubjects()
        Me.dtgLoad.DataSource = Me.BindingSource2
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Using command As New SqlCommand("UPDATE tblTeachersLoad SET Teacher = @Teacher ", cnn)
            With command.Parameters
                .AddWithValue("@Teacher", Me.dtgLoad("Column1", 0).Value)
                '.AddWithValue("@Subject", Me.Column1.Selected)
            End With
            command.ExecuteNonQuery()
        End Using
    End Sub

    Private Sub txtDept_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtDept.TextChanged
        Me.BindingSource1.DataSource = Me.GetTeachers()
        Me.Column1.DisplayMember = "Teacher"
        Me.Column1.ValueMember = "TeacherID"
        Me.Column1.DataSource = Me.BindingSource1

        Me.BindingSource2.DataSource = Me.GetSubjects()
        Me.dtgLoad.DataSource = Me.BindingSource2
    End Sub

I just tried to save the teacher's name to see if Im doing it correct but when I click save button nothing is happening...table does is updated and no error message appearing..

[Simply Me]

I've change my code to INSERT like

  Using command As New SqlCommand("INSERT INTO tblteachersload (Teacher) VALUES (@Teacher)", cnn)
            With command.Parameters
                .AddWithValue("@Teacher", Me.dtgLoad("Column1", 0).Value)
                '.AddWithValue("@Subject", Me.Column1.Selected)
            End With
            command.ExecuteNonQuery()
        End Using

I can see record inserted aready but the TeacherID is inserted in teacher column of tblteachersload not the name of the teacher.

here are my fields in tblteachersload
TeacherID varchar(10)
Teacher nvarchar(50)
SectionID varchar(10)
SubjectName nvarchar(20)
YearLevel nvarchar(20)
SubjectID varchar(10)

[Simply Me]

anyone who has idea on this one?

[Simply Me]

i run the program and just selected a teacher from the combobox and then click the save button with the following code in it and 70 records were inserted in my table with the name of the teacher i chose.

        Dim x As Integer
        Dim y As Integer

        y = Me.BindingContext(dtgLoad.Columns).Count - 1
        Me.BindingContext(dtgLoad.Columns).Position = 0

        For x = 0 To y
            For i As Integer = 0 To dtgLoad.Rows.Count - 1
                Using command As New SqlCommand("INSERT INTO tblteachersload (Teacher ,SubjectName) " & _
                            "VALUES (@Teacher, @Subject)", cnn)
                    With command.Parameters
                        .AddWithValue("@Teacher", Me.dtgLoad("Column1", 0).Value)
                        .AddWithValue("@Subject", Me.dtgLoad("Column1", 0).Value)
                    End With
                    command.ExecuteNonQuery()
                End Using
            Next
        Next

[Simply Me]

how do i get the values of the other cells of each row?

[Simply Me]

i guess this thread is already resolved when it comes to displaying records...I will be opening other thread for saving the records to another table.

thanks for the help jm.