I have a problem these codes when i pressed the add(button) it select all Course_code and Course_name to listview but the Students_fname and Students_Lastname were disappeared.
My Database ACCESS
Using relation table
tblStudents
Student_ID |PK
Student_Fname |Short Text
Student_Lastname |Short Text
Course_ID |FK
tblCourse
Course_ID |PK
Course_code |Short Text
Course_name |Short Text
What I have tried:
Public Class frmStudents
Dim sqlcode As String
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jim Clinton Amarga\Desktop\Class Records\Class Records\bin\Debug\dbClassRecords.accdb"
End Class
Sub loaddata()
ListView1.Items.Clear()
Try
sqlcode = "SELECT tblStudents.Student_Fname, tblStudents.Student_Lastname from tblStudents "
Dim sqlcmd As New OleDb.OleDbCommand(sqlcode)
sqlcmd.Connection = New OleDb.OleDbConnection(connstring)
sqlcmd.Connection.Open()
sqlcmd.ExecuteNonQuery()
Dim da As New OleDb.OleDbDataAdapter(sqlcmd)
Dim ds As New DataSet
da.Fill(ds, "tblStudents")
Dim itemcollection(100) As String
For r = 0 To ds.Tables("tblStudents").Rows.Count - 1
For c = 0 To ds.Tables("tblStudents").Columns.Count - 1
itemcollection(c) = ds.Tables("tblStudents").Rows(r)(c).ToString
Next
Dim LVI As New ListViewItem(itemcollection)
ListView1.Items.Add(LVI)
Next
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
Try
sqlcode = "SELECT tblCourses.Course_code, tblCourses.Course_name from tblCourses "
Dim sqlcmd As New OleDb.OleDbCommand(sqlcode)
sqlcmd.Connection = New OleDb.OleDbConnection(connstring)
sqlcmd.Connection.Open()
sqlcmd.ExecuteNonQuery()
Dim da As New OleDb.OleDbDataAdapter(sqlcmd)
Dim ds As New DataSet
da.Fill(ds, "tblCourses")
Dim itemcollection(100) As String
For r = 0 To ds.Tables("tblCourses").Rows.Count - 1
For c = 0 To ds.Tables("tblCourses").Columns.Count - 1
itemcollection(c) = ds.Tables("tblCourses").Rows(r)(c).ToString
Next
Dim LVI As New ListViewItem(itemcollection)
ListView1.Items.Add(LVI)
Next
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub btnStudADD_Click(sender As Object, e As EventArgs) Handles btnStudADD.Click
Try
sqlcode = "INSERT INTO tblStudents (Student_Fname,Student_Lastname) values('" & txtFname.Text & "','" & txtLname.Text & "')"
sqlcode = "INSERT INTO tblCourses (Course_code,Course_name) values('" & txtSC1.Text & "','" & txtSC2.Text & "')"
Dim sqlcmd As New OleDb.OleDbCommand(sqlcode)
sqlcmd.Connection = New OleDb.OleDbConnection(connstring)
sqlcmd.Connection.Open()
sqlcmd.ExecuteNonQuery()
MsgBox("ADD SUCCESSFULLY")
Call loaddata()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub btnStudADD_Click(sender As Object, e As EventArgs) Handles btnStudADD.Click
Try
sqlcode = "INSERT INTO tblStudents (Student_Fname,Student_Lastname) values('" & txtFname.Text & "','" & txtLname.Text & "')"
sqlcode = "INSERT INTO tblCourses (Course_code,Course_name) values('" & txtSC1.Text & "','" & txtSC2.Text & "')"
Dim sqlcmd As New OleDb.OleDbCommand(sqlcode)
sqlcmd.Connection = New OleDb.OleDbConnection(connstring)
sqlcmd.Connection.Open()
sqlcmd.ExecuteNonQuery()
MsgBox("ADD SUCCESSFULLY")
Call loaddata()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub