I have a ListView that has values from a database. I used two MS Access OLEDB Statements to produce the data I have. So I used two While Loops. The second OLEDB Statement is with reference to the first. How do I insert all the contents of my ListView to a DataGridView?
Here is my code for the two While loops that is used to call out the OLEDB Statements:
Dim connectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\STSlog.mdb;Jet OLEDB:Database Password=password;"
Dim conn As New OleDbConnection(connectionstring)
Dim command, command2, command3 As New OleDbCommand
Dim commstring, commstring2, commstring3 As String
Dim searchstring As String
commstring = "SELECT DISTINCT empname FROM data WHERE ProjectCode = '" & PrjctCmbBox.SelectedItem.ToString & "' ORDER BY empname ASC"
command = New OleDbCommand(commstring, conn)
commstring3 = "SELECT SUM([Regular]) AS sRegular, SUM(OT) AS sOT FROM data WHERE ProjectCode = '" & PrjctCmbBox.SelectedItem.ToString & "' "
command3 = New OleDbCommand(commstring3, conn)
MainLView.Clear()
MainLView.GridLines = True
MainLView.FullRowSelect = True
MainLView.View = View.Details
MainLView.MultiSelect = True
MainLView.Columns.Add("Employee Name", 290)
MainLView.Columns.Add("Total Regular", 200)
MainLView.Columns.Add("Total Overtime", 200)
MainLView.Columns.Add("Total Hours", 200)
conn.Open()
Dim reader As OleDbDataReader = command.ExecuteReader()
Dim RegSum, OTSum, Total As Decimal
While reader.Read
searchstring = reader("empname")
commstring2 = "SELECT SUM([Regular]) AS sReg, SUM(OT) AS sOT FROM data WHERE empname = '" & searchstring & "' AND ProjectCode = '" & PrjctCmbBox.SelectedItem.ToString & "' "
command2 = New OleDbCommand(commstring2, conn)
Dim reader2 As OleDbDataReader = command2.ExecuteReader()
While reader2.Read
RegSum = (reader2("sReg"))
OTSum = (reader2("sOT"))
Total = Format((RegSum + OTSum), "0.0")
With MainLView.Items.Add(reader("empname"))
.subitems.add(reader2("sReg"))
.subitems.add(reader2("sOT"))
.subitems.add(Total)
End With
End While
End While
I added columns first to my DataGridView then inserted the data from the database using DataGridView Rows.
Dim connectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\STSlog.mdb;Jet OLEDB:Database Password=password;"
Dim conn As New OleDbConnection(connectionstring)
Dim command, command2, command3 As New OleDbCommand
Dim commstring, commstring2, commstring3 As String
Dim searchstring As String
commstring = "SELECT DISTINCT empname FROM data WHERE ProjectCode = '" & PrjctCmbBox.SelectedItem.ToString & "' ORDER BY empname ASC"
command = New OleDbCommand(commstring, conn)
commstring3 = "SELECT SUM([Regular]) AS sRegular, SUM(OT) AS sOT FROM data WHERE ProjectCode = '" & PrjctCmbBox.SelectedItem.ToString & "' "
command3 = New OleDbCommand(commstring3, conn)
MainLView.Clear()
MainLView.GridLines = True
MainLView.FullRowSelect = True
MainLView.View = View.Details
MainLView.MultiSelect = True
MainLView.Columns.Add("Employee Name", 290)
MainLView.Columns.Add("Total Regular", 200)
MainLView.Columns.Add("Total Overtime", 200)
MainLView.Columns.Add("Total Hours", 200)
Dim col1 As New DataGridViewTextBoxColumn
Dim col2 As New DataGridViewTextBoxColumn
Dim col3 As New DataGridViewTextBoxColumn
Dim col4 As New DataGridViewTextBoxColumn
col1.HeaderText = "Employee Name"
col2.HeaderText = "Total Regular"
col3.HeaderText = "Total Overtime"
col4.HeaderText = "Total Hours"
ReportDGV.Columns.Add(col1)
ReportDGV.Columns.Add(col2)
ReportDGV.Columns.Add(col3)
ReportDGV.Columns.Add(col4)
conn.Open()
Dim reader As OleDbDataReader = command.ExecuteReader()
Dim RegSum, OTSum, Total As Decimal
While reader.Read
searchstring = reader("empname")
commstring2 = "SELECT SUM([Regular]) AS sReg, SUM(OT) AS sOT FROM data WHERE empname = '" & searchstring & "' AND ProjectCode = '" & PrjctCmbBox.SelectedItem.ToString & "' "
command2 = New OleDbCommand(commstring2, conn)
Dim reader2 As OleDbDataReader = command2.ExecuteReader()
While reader2.Read
RegSum = (reader2("sReg"))
OTSum = (reader2("sOT"))
Total = Format((RegSum + OTSum), "0.0")
With MainLView.Items.Add(reader("empname"))
.subitems.add(reader2("sReg"))
.subitems.add(reader2("sOT"))
.subitems.add(Total)
Dim row As String() = New String() {reader("empname"), reader2("sReg"), reader2("sOT"), Total}
ReportDGV.Rows.Add(row)
End With
End While
End While