Thank you for the responses. Here is what I have so far. My form loads and I have a checkedboxitems (as well as a listview for test purposes) which displays the employees. I then have a combobox for selecting the certification to use. And lastly, I have two datetimepickers which will be used to select the date the certification was taken and the date it will expire (usually a 1 or 2 year certification).
Code:
Public Class frmAddCert Dim dbProvider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
Dim TheDatabase As String = "\Certifications.accdb;Persist Security Info=True"
Dim MyDocumentsFolder As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
Dim FullDatabasePath As String = MyDocumentsFolder & TheDatabase
Dim strConnection As String = dbProvider & FullDatabasePath
'Dim strConnection As String = dbProvider & dbSource
Dim con As New OleDb.OleDbConnection(strConnection)
'string to load All Employees
Dim strAllEmployees As String = "SELECT DISTINCT OFFICERS.ID, OFFICERS.LName + ', ' + OFFICERS.FName + ' - ' + OFFICERS.DSN as FullName from OFFICERS" 'WHERE EXISTS (SELECT * FROM INTERNALCERTDATE WHERE OFFICERS.ID= INTERNALCERTDATE.FKOfficer)" ' ORDER BY LName"
Dim strAllCerts As String = "SELECT DISTINCT INTERNALCERT.ID, INTERNALCERT.INTERNALCOURSE + ' - ' + INTERNALCERT.EXPIRATION as Certifications from INTERNALCERT"
'Insert string to add new records to multiple employees
Dim strInsertCert As String = "INSERT INTO INTERNALCERTDATE"
Private Sub frmAddCert_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim dt1 As New DataTable
Dim adapter As New OleDb.OleDbDataAdapter
Dim command As New OleDb.OleDbCommand
con.Open()
command.Connection = con
command.CommandText = strAllEmployees
adapter.SelectCommand = command
adapter.Fill(dt1)
CheckedListBox1.DisplayMember = "FullName"
CheckedListBox1.ValueMember = "FullName"
If dt1.Rows.Count > 0 Then
For i As Integer = 0 To dt1.Rows.Count - 1
CheckedListBox1.Items.Add(CStr(dt1.Rows(i).Item(0).ToString & " | " & dt1.Rows(i).Item(1)), False)
Next
End If
Dim dt2 As New DataTable
Dim adapter1 As New OleDb.OleDbDataAdapter
Dim command1 As New OleDb.OleDbCommand
command1.Connection = con
command1.CommandText = strAllCerts
adapter1.SelectCommand = command1
adapter1.Fill(dt2)
cboCertifications.DisplayMember = "Certifications"
cboCertifications.ValueMember = "Certifications"
If dt2.Rows.Count > 0 Then
For i As Integer = 0 To dt2.Rows.Count - 1
cboCertifications.Items.Add(CStr(dt2.Rows(i).Item(0)))
Next
End If
Dim dt3 As New DataTable
Dim adapter2 As New OleDb.OleDbDataAdapter
Dim command2 As New OleDb.OleDbCommand
command2.Connection = con
command2.CommandText = strAllEmployees
adapter2.SelectCommand = command2
adapter2.Fill(dt3)
Dim j As Integer
If dt3.Rows.Count > 0 Then
For i As Integer = 0 To dt3.Rows.Count - 1
'For Each row In dt3.Rows
ListView1.Items.Add(CStr(dt3.Rows(i).Item(1).ToString))
'Next
Next
End If
con.close()
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim dtInsert As New DataTable
Dim daInsert As New OleDb.OleDbDataAdapter
Dim command As New OleDb.OleDbCommand
con.Open()
command.Connection = con
command.CommandText = strAllEmployees
daInsert.SelectCommand = command
daInsert.Fill(dtInsert)
'gets # of checkbox items (not checked items)
Dim selectedIndex As Integer = CheckedListBox1.Items.Count - 1
MsgBox(selectedIndex)
If (selectedIndex <> -1) Then
'loops through all checkboxes (not just checked ones)
For i As Integer = 0 To CheckedListBox1.Items.Count - 1
'MsgBox(i)
Dim chkstate As CheckState
'determines if checkbox is checked (1 is checked, 0 is not checked
chkstate = CheckedListBox1.GetItemCheckState(i)
'MsgBox(chkstate)
If (chkstate = CheckState.Checked) Then
End If
Next
End If
con.close()
End Sub
End Class
I know this is not a vb.net forum but I felt providing the code may assist in my question.
So I am first checking the state of the checkboxes in the CheckedListBox1. As I iterate through them, I then need to check which value is selected for the combobox (cboCertification) and finally I need to get the values from both datetimepickers. I think I can get all that.
My question is once I have all of that, what is my INSERT INTO statement? The connection to the db will be open at the beginning of the btnSave_click event and will remain open through the entire iteration. I believe I will have multiple embedded For Loops and then in the final one, I will input my values into the INSERT statement and write to the database. Any help with the INSERT statement would be greatly appreciated. Thank you.
Windows 7 32bit 
Access 2010 32bit
Reply With Quote
