[RESOLVED] records not displaying
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.
Code:
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.
Code:
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
Re: records not displaying
Quote:
Code:
"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.
Re: records not displaying
thanks jm...I'll try to do the combobox thing...hope I can have this figure out especially the saving/updating part.
Re: records not displaying
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.
Code:
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
Re: records not displaying
Follow the CodeBank link in my signature and check out my thread on adding a combo box column to a DataGridView.
Re: records not displaying
Im on the process of following your code submission...
Re: records not displaying
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.
Re: records not displaying
Can you provide a more thorough explanation of what you're doing?
Re: records not displaying
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.
Re: records not displaying
Here's my code now
Code:
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..
Re: records not displaying
I've change my code to INSERT like
Code:
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)
Re: records not displaying
anyone who has idea on this one?
Re: records not displaying
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.
Code:
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
Re: records not displaying
how do i get the values of the other cells of each row?
Re: records not displaying
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.