Hi evoteam21,
Please refer below Sample.
Form1
Private Sub GenerateTasksUser(title As String)
FlowLayoutPanel3.Controls.Clear()
Dim dt As DataTable = New ClassBLL().GetTasksUser(title)
If dt IsNot Nothing Then
If dt.Rows.Count > 0 Then
Dim listItems As UserTasks() = New UserTasks(dt.Rows.Count - 1) {}
For i As Integer = 0 To 1 - 1
For Each row As DataRow In dt.Rows
Dim listItem As New UserTasks()
listItems(i) = listItem
listItems(i).Width = FlowLayoutPanel3.Width - 24
listItems(i).TaskTitle = row("taskstitle").ToString()
listItems(i).TaskSubject = row("tasksubject").ToString()
listItems(i).TaskFrom = row("taskfromname").ToString()
listItems(i).Tag = row("ID").ToString()
listItems(i).CheckBoxText = row("taskstatus").ToString
listItems(i).Status = row("taskstatus").ToString
If listItems(i).CheckBoxText = "Completed" Then
listItems(i).CheckBoxCheck = True
Else
listItems(i).CheckBoxCheck = False
End If
FlowLayoutPanel3.Controls.Add(listItems(i))
Next
Next
End If
End If
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.CenterToScreen()
GenerateTasksUser("")
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim recordsToUpdate = FlowLayoutPanel3.Controls.OfType(Of UserTasks)().
Where(Function(ut) ut.CheckBox1.Checked And ut.Status = "").
Select(Function(x) CStr(x.Tag))
If recordsToUpdate?.Any() Then
'At least one CheckBox in myGroupBox is checked.
'MsgBox("Checkbox Selected.", MsgBoxStyle.OkOnly, "Yeyy!")
For Each recordId In recordsToUpdate
UpdateTasksDB(recordId)
Next
MsgBox("Job Done")
Else
MsgBox("Hey wait first you need to select some tasks checkbox before proceed.", MsgBoxStyle.OkOnly, "Error!")
End If
End Sub
Private Sub TitleKeyUp(sender As Object, e As KeyEventArgs) Handles txtTitle.KeyUp
GenerateTasksUser(txtTitle.Text)
End Sub
UserTask
Public Class UserTasks
Public _taskTitle As String
Public _taskSubject As String
Public _taskFrom As String
Public _CheckBoxText As String
Public _checked As Boolean = True
Public _status As String
Public Property TaskTitle As String
Get
Return _taskTitle
End Get
Set(value As String)
_taskTitle = value
Label1.Text = value
End Set
End Property
Public Property TaskSubject As String
Get
Return _taskSubject
End Get
Set(value As String)
_taskSubject = value
Label2.Text = value
End Set
End Property
Public Property TaskFrom As String
Get
Return _taskFrom
End Get
Set(value As String)
_taskFrom = value
Label3.Text = value
End Set
End Property
Public Property CheckBoxText As String
Get
Return _CheckBoxText
End Get
Set(value As String)
_CheckBoxText = value
'CheckBox1.Checked = value
End Set
End Property
Public Property CheckBoxCheck As Boolean
Get
Return _checked
End Get
Set(value As Boolean)
_checked = value
CheckBox1.Checked = value
End Set
End Property
Public Property Status As String
Get
Return _status
End Get
Set(value As String)
_status = value
End Set
End Property
Private Sub UserTasks_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
End Class
ClassDAL
Public Class ClassDAL
Public Function AddPicsToTable(img As Image, title As Integer)
Using cons As New SQLiteConnection(ServerStatus)
Using cmd As New SQLiteCommand()
Dim ms As New MemoryStream()
cmd.Connection = cons
cmd.CommandText = "UPDATE Profiles SET userPicture = @Image Where ID = @cName"
cmd.Parameters.AddWithValue("@cName", title)
img.Save(ms, img.RawFormat)
cmd.Parameters.AddWithValue("@Image", ms.ToArray())
'cmd.Parameters.AddWithValue("@Image", OleDbType.Binary).Value = arrimage
cons.Open()
cmd.ExecuteNonQuery()
End Using
End Using
'Using conn As New OleDbConnection(ServerStatus)
' Using cmd As New OleDbCommand()
' cmd.Connection = conn
' cmd.CommandText = "UPDATE Profiles SET userPicture = @Image WHERE ID = @cName"
' cmd.Parameters.AddWithValue("@cName", title.Trim())
' Dim ms As New MemoryStream()
' img.Save(ms, img.RawFormat)
' cmd.Parameters.AddWithValue("@Image", ms.ToArray())
' conn.Open()
' cmd.ExecuteNonQuery()
' End Using
'End Using
Return True
End Function
Public Function AddItemsToTable(FID As String, FAccFrom As String, FUserFrom As String, FStatus As String, FAccTo As String, FUserTo As String) As Boolean
Using conn As New SQLiteConnection(ServerStatus)
Using cmd As New SQLiteCommand()
cmd.Connection = conn
cmd.CommandText = "INSERT INTO OrdersAssigned (orderid,orderacc,orderfrom,orderstatus,orderreceiveracc,orderreceiver) VALUES (@cID,@cAccFrom,@cUserFrom,@cStatus,@cAccTo,@cUserTo)"
cmd.Parameters.AddWithValue("@cID", FID)
cmd.Parameters.AddWithValue("@cAccFrom", FAccFrom)
cmd.Parameters.AddWithValue("@cUserFrom", FUserFrom)
cmd.Parameters.AddWithValue("@cStatus", FStatus)
cmd.Parameters.AddWithValue("@cAccTo", FAccTo)
cmd.Parameters.AddWithValue("@cUserTo", FUserTo)
'Dim ms As New MemoryStream()
'img.Save(ms, img.RawFormat)
'cmd.Parameters.AddWithValue("@Image", ms.ToArray())
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
Return True
End Function
Public Function ReadItemsTable() As DataTable
Using cons As New SQLiteConnection(ServerStatus)
Using cmd As New SQLiteCommand()
cmd.Connection = cons
'cmd.CommandText = "SELECT * FROM OrdersAssigned ORDER BY ID ASC"
cmd.CommandText = "SELECT OrdersAssigned.*,
ProfilesFrom.userPosition AS UserPositionFrom,
ProfilesFrom.userPicture AS UserPictureFrom,
ProfilesTo.userPosition AS UserPositionTo,
ProfilesTo.userPicture AS UserPictureTo
FROM
(OrdersAssigned
LEFT OUTER JOIN Profiles AS ProfilesFrom ON OrdersAssigned.orderacc = ProfilesFrom.userAccount)
LEFT OUTER JOIN Profiles AS ProfilesTo ON OrdersAssigned.orderreceiveracc = ProfilesTo.userAccount
ORDER BY
OrdersAssigned.ID ASC;"
cons.Open()
Using sda As New SQLiteDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Public Function FindNewUsers() As DataTable
Using cons As New SQLiteConnection(ServerStatus)
Using cmd As New SQLiteCommand()
cmd.Connection = cons
cmd.CommandText = "SELECT
[ID],
userName,
userPosition,
userPicture
FROM
Profiles
ORDER BY
userRegDate Desc
LIMIT 4;"
cons.Open()
Using sda As New SQLiteDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Public Function FindTasksUser(taskTitle As String) As DataTable
Using cons As New SQLiteConnection(ServerStatus)
Using cmd As New SQLiteCommand()
cmd.Connection = cons
If String.IsNullOrEmpty(taskTitle) Then
cmd.CommandText = "SELECT * FROM Tasks WHERE accountName = @GetUser ORDER BY [ID] DESC;"
cmd.Parameters.AddWithValue("@GetUser", UserUserAcc).ToString()
Else
cmd.CommandText = String.Format("SELECT * FROM Tasks WHERE taskstitle LIKE '%{0}%' ORDER BY [ID] DESC;", taskTitle)
End If
cons.Open()
Using sda As New SQLiteDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
End Class
ClassBLL
Public Class ClassBLL
Public Function SaveItems(FID As String, FAccFrom As String, FUserFrom As String, FStatus As String, FAccTo As String, FUserTo As String) As Boolean
Try
Dim objdal As New ClassDAL()
Return objdal.AddItemsToTable(FID, FAccFrom, FUserFrom, FStatus, FAccTo, FUserTo)
Catch e As Exception
Dim result As DialogResult = MessageBox.Show(e.Message.ToString())
Return False
End Try
End Function
Public Function SavePics(img As Image, title As Integer) As Boolean
Try
Dim objdal As New ClassDAL()
Return objdal.AddPicsToTable(img, title)
Catch e As Exception
Dim result As DialogResult = MessageBox.Show(e.Message.ToString())
Return False
End Try
End Function
Public Function GetItems() As DataTable
Try
Dim objdal As New ClassDAL()
Return objdal.ReadItemsTable()
Catch e As Exception
Dim result As DialogResult = MessageBox.Show(e.Message.ToString())
Return Nothing
End Try
End Function
Public Function FindNewUsersRegistered() As DataTable
Try
Dim objdal As New ClassDAL()
Return objdal.FindNewUsers()
Catch e As Exception
Dim result As DialogResult = MessageBox.Show(e.Message.ToString())
Return Nothing
End Try
End Function
Public Function GetTasksUser(title As String) As DataTable
Try
Dim objdal As New ClassDAL()
Return objdal.FindTasksUser(title)
Catch e As Exception
Dim result As DialogResult = MessageBox.Show(e.Message.ToString())
Return Nothing
End Try
End Function
End Class
Module Settings
Module Settings
Public MyConfigFile As String = ".\Configs.ini"
Public objIniFile As New IniFile(MyConfigFile) 'Read File Strings
Public File As String = objIniFile.GetString("Database", "File", "")
Public Path As String = objIniFile.GetString("Database", "Path", "")
Public Encr As String = objIniFile.GetString("Database", "Encryption", "")
Public Checker As String = objIniFile.GetString("Database", "Status", "")
Public ServerUpdateFolder As String = objIniFile.GetString("Server", "UpdatesFolder", "")
Public ServerNewsFolder As String = objIniFile.GetString("Server", "NewsFolder", "")
Public UserUserAcc As String = objIniFile.GetString("Login", "User", "")
Public ServerUpdateFold = Environment.CurrentDirectory & ServerUpdateFolder
Public ServerNewsFold = Environment.CurrentDirectory & ServerNewsFolder
'Public ServerStatus As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & File & ";")
Public ServerStatus As String = "Data Source=" & Path & File & ";Version=3;"
Public conn As New SQLiteConnection(ServerStatus)
Public cmd As New SQLiteCommand
Public Adapter As New SQLiteDataAdapter
Public Reader As SQLiteDataReader
Public Result As Integer
Public RatingsCounter As String
Public RatingsCheck As Decimal
Public Sub ConnectDB() 'Check for DB Status
If conn IsNot Nothing Then
conn.Close()
End If
Try
conn.Open()
MsgBox("Connected")
Catch ex As Exception
MsgBox("Database Error:[" & ex.Message & "]")
Finally
conn.Close()
End Try
End Sub
Public Sub DeleteTasksDB(idNumber As Integer)
Try
Using conn As New SQLiteConnection(ServerStatus)
Using cmd As New SQLiteCommand()
cmd.Connection = conn
cmd.CommandText = "DELETE FROM Tasks WHERE [ID] = @FindID;"
cmd.Parameters.AddWithValue("@FindID", idNumber)
conn.Open()
Result = cmd.ExecuteNonQuery()
If Result > 0 Then
MessageBox.Show("Deleted Successful", "YCNavi Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Failed to Delete!", "YCNavi Tool", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
End Using
End Using
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Public Sub UpdateTasksDB(idNumber As Integer)
Try
Using conn As New SQLiteConnection(ServerStatus)
Using cmd As New SQLiteCommand()
cmd.Connection = conn
cmd.CommandText = "UPDATE Tasks SET taskstatus = 'Completed' WHERE [ID] = @FindID;"
cmd.Parameters.AddWithValue("@FindID", idNumber)
conn.Open()
Result = cmd.ExecuteNonQuery()
If Result > 0 Then
MessageBox.Show("Updated Successful", "YCNavi Tool", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Failed to Update!", "YCNavi Tool", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End If
End Using
End Using
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Module
Screenshot