MySql load Listview and run time column names + autosize, checked, color items

[sdowney1]

example using ado.net to load values into a list box
at run time setup column names, autosize columns
select checked
color items a color with bold, then black regular

mouse down select first column to display other text in a rtb
etc...

add Try - Catch as you like, I leave it out at first and watch for exception in the debug window.

	'example of working code
	'https://stackoverflow.com/questions/57584/how-can-i-make-a-listviews-columns-auto-resize-programmatically

	'https://learn.microsoft.com/en-us/dotnet/api/system.windows.forms.listviewitem.checked?view=windowsdesktop-8.0
	'ListView1.View = View.Details
	'ListView1.Columns.Add("stuff")
	'ListView1.Columns.Add("stuff1")
	'ListView1.Columns.Add("stuff2")
	'ListView1.Columns.Add("stuff3")
	'For Xx = 1 To 100
	'Dim Litem As New ListViewItem With {
	'.Text = "Around the Horn"
	'	}
	'Litem.SubItems.Add("Hardy Thomas")
	'Litem.SubItems.Add("(171) 555-7788")
	'Litem.SubItems.Add("(171) 555-6750")
	'ListView1.Items.Add(Litem)
	'Next
	'ListView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent)

	With ListView1
		.Items.Clear()
                .Columns.Clear()
		.View = System.Windows.Forms.View.Details
		.Alignment = System.Windows.Forms.ListViewAlignment.Top
		.LabelEdit = False
		.View = View.Details
		.Columns.Add("ItemID")
		.Columns.Add("Barcode")
		.Columns.Add("Deletion Date")
		.Columns.Add("Title")

		' Display check boxes.
		.CheckBoxes = True
		' Select the item and subitems when selection is made.
		.FullRowSelect = True
		' Display grid lines.
		.GridLines = True
		'goofy sort on first column Id not numerical order
		' Sort the items in the list in ascending order.
		'.Sorting = SortOrder.Ascending
	End With


	'get count(id) from unbookdata
	Dim numberID As Integer = 0
	Dim ConStrUserVar As String = frmlogonConnectstring ' & "Allow User Variables=True;"
	Dim conn As New MySqlConnection(ConStrUserVar)
	Using conn
		conn.Open()
		Dim cmd1 As New MySqlCommand("Select count(Id) as TOPID from unbookdata", conn)
		Using RDR = cmd1.ExecuteReader()
			If RDR.Read() Then numberID = RDR("TOPID")
		End Using

		If numberID = 0 Then
			conn.Close()
			MsgBox("No records available to restore", MsgBoxStyle.Information, "Record Management")
			Me.Close()
			Exit Sub
		End If

		Me.Text = "Record Undelete Program - " & numberID & " records available."
		'load list view with data
		ListView1.BeginUpdate()
		cmd1.CommandText = "Select unID, unDatetime, unTitles, unBarcode From unbookdata"

		Using RDR = cmd1.ExecuteReader()

			Do While RDR.Read()
				Dim LItem As New ListViewItem()
				LItem.Text = RDR("unID").ToString() '0
				LItem.SubItems.Add(RDR("unBarcode").ToString()) '3
				LItem.SubItems.Add(RDR("unDateTime").ToString()) '"yyyy-MM-dd HH:mm:ss")) '1
				LItem.SubItems.Add(RDR("unTitles").ToString()) '2

				ListView1.Items.Add(LItem)
			Loop

		End Using
		ListView1.EndUpdate()
		ListView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent)

		conn.Close()
	End Using

End Sub

Uses couple buttons to select all items in lisview, then unselect all items in list view
with Try Catch

	Private Sub cmdSelect2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdSelect2.Click
		'selects all
		Try
			Xx = 0
			Do Until Xx > ListView1.Items.Count - 1
				ListView1.Items.Item(Xx).Checked = True
				ListView1.Items.Item(Xx).Font = New Font(ListView1.Items.Item(Xx).Font, FontStyle.Bold)
				ListView1.Items.Item(Xx).ForeColor = System.Drawing.Color.Blue
				ListView1.Items.Item(Xx).SubItems.Item(1).ForeColor = System.Drawing.Color.Blue
				ListView1.Items.Item(Xx).SubItems.Item(2).ForeColor = System.Drawing.Color.Blue
				ListView1.Items.Item(Xx).SubItems.Item(3).ForeColor = System.Drawing.Color.Blue
				Xx += 1
			Loop
		Catch ex As Exception
			Debug.WriteLine(ex.ToString())
			Dim msg = ex.ToString()
			MsgBox(msg,  , "Error")
		End Try


	End Sub

	Private Sub cmdUnselect2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdUnselect2.Click
		'unselects all
		Try
			Xx = 0
			Do Until Xx > ListView1.Items.Count - 1
				ListView1.Items.Item(Xx).Checked = False
				ListView1.Items.Item(Xx).Font = New Font(ListView1.Items.Item(Xx).Font, FontStyle.Regular)
				ListView1.Items.Item(Xx).ForeColor = System.Drawing.Color.Black
				ListView1.Items.Item(Xx).SubItems.Item(1).ForeColor = System.Drawing.Color.Black
				ListView1.Items.Item(Xx).SubItems.Item(2).ForeColor = System.Drawing.Color.Black
				ListView1.Items.Item(Xx).SubItems.Item(3).ForeColor = System.Drawing.Color.Black
				Xx += 1
			Loop
		Catch ex As Exception
			Debug.WriteLine(ex.ToString())
			Dim msg = ex.ToString()
			MsgBox(msg,  , "Error")

		End Try

	End Sub

when mouse down on an item row, red line gets the text for the first column to get data to display in a rtb

	Private Sub ListView1_MouseDown(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDown
		'Dim info As ListViewHitTestInfo = ListView1.HitTest(e.X, e.Y)
		'MsgBox(info.Location.ToString())
		'If Not IsNothing(info.SubItem) Then
		'info will contain the information of the clicked listview column. You can then go through it's subitems for more information, if any.
		'Debug.Print(info.SubItem.Text)
		'want only the id out of the clicked on item!!
		'IdNumber = CInt(ListView1.Items.Item(ListView1.FocusedItem.Index).Text)
		'Debug.Print(IdNumber)
		'End If

		Dim ConStrUserVar As String = frmlogonConnectstring ' & "Allow User Variables=True;"
		Dim conn As New MySqlConnection(ConStrUserVar)
		Using conn
			IdNumber = (Val(ListView1.Items.Item(ListView1.FocusedItem.Index).SubItems.Item(0).Text))
			conn.Open()
			Dim cmd1 As New MySqlCommand("select unmarcdata From unbookdata where unId = " & IdNumber, conn)
			Using RDR = cmd1.ExecuteReader()
				If RDR.Read() Then rtxtMarc.Text = RDR("unmarcdata").ToString()
			End Using
			conn.Close()
		End Using
	End Sub

Will add example code for checked items later, as in I check a row, then I click a button to delete that row in table or restore that row to another table