<Serializable()> Public Class DataLayer
  Private Shared sqlConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\Access Databases\northwind.mdb'"
  Private Shared sqlConn As New OleDb.OleDbConnection(sqlConnString)
  Private Shared sqlCmnd As New OleDb.OleDbCommand
  Private Shared sqlTrans As OleDb.OleDbTransaction

#Region "Northwind Data Classes"
  Public Class Product
    'you define the database fields here
    Public Property ProductID As Integer
    Public Property ProductName As String
    Public Property SupplierID As Integer
    Public Property CategoryID As Integer
    Public Property QuantityPerUnit As String
    Public Property UnitPrice As Decimal
    Public Property UnitsInStock As Integer
    Public Property UnitsOnOrder As Integer
    Public Property ReorderLevel As Integer
    Public Property Discontinued As Boolean

    Public Overrides Function ToString() As String
      'you define the tostring function of your class here - this is what is shown as a default
      'when you add a class object to the likes of a listbox and combobox etc
      'when you do NOT provide get and set values for a property an automatic
      'private variable of <underscore>FieldName is created i.e. _ProductName
      Return _ProductName
    End Function

    'define any data selection functions here using standard SQL syntax
    Public Shared Function GetAllRecords() As List(Of Product)
      Const sqlString As String = "SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " & _
                                  "ReorderLevel, Discontinued FROM Products"
      Dim lstProducts As New List(Of Product)
      Dim Reader As OleDb.OleDbDataReader

      Try
        With sqlCmnd
          .Parameters.Clear()
          .Connection = sqlConn
          .CommandText = sqlString
        End With
        sqlConn.Open()
        Reader = sqlCmnd.ExecuteReader

        If Reader.HasRows Then
          While Reader.Read
            Dim PROD As New Product
            With PROD
              .ProductID = Reader.Item("ProductID")
              'how to deal with null values:-
              .ProductName = IIf(IsDBNull(Reader.Item("ProductName")), String.Empty, Reader.Item("ProductName"))
              .SupplierID = Reader.Item("SupplierID")
              .CategoryID = Reader.Item("CategoryID")
              .QuantityPerUnit = Reader.Item("QuantityPerUnit")
              .UnitPrice = Reader.Item("UnitPrice")
              .UnitsInStock = Reader.Item("UnitsInStock")
              .UnitsOnOrder = Reader.Item("UnitsOnOrder")
              .ReorderLevel = Reader.Item("ReorderLevel")
              .Discontinued = Reader.Item("Discontinued")
            End With
            lstProducts.Add(PROD)
          End While
        End If
        Return lstProducts
      Catch ex As Exception
        Throw ex
        Return Nothing
        Exit Function
      Finally
        If sqlConn.State = ConnectionState.Open Then
          sqlConn.Close()
        End If
      End Try
    End Function

    Public Shared Function InsertRecord(PROD As Product) As Integer
      Const sqlString As String = "INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) " & _
                                  "VALUES (@ProductName,@SupplierID,@CategoryID,@QuantityPerUnit,@UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued)"

      'in SQL Server you can use ";Select SCOPE_IDENTITY();" with ExecuteScalar instead of ExecuteNonQuery to return
      'the New IDNo generated by a primary key where its Identity Increment if set to 1 or more
      'After testing this is not available is Access#

      Dim NewRecordIDNo As Integer

      'we use transaction processing here to detect for errors and rollback if needed
      Try
        sqlConn.Open()
        sqlTrans = sqlConn.BeginTransaction
        With sqlCmnd
          .Connection = sqlConn
          .Transaction = sqlTrans
          .CommandText = sqlString
          .Parameters.Clear()
          .Parameters.AddWithValue("@ProductName", PROD.ProductName)
          .Parameters.AddWithValue("@SupplierID", PROD.SupplierID)
          .Parameters.AddWithValue("@CategoryID", PROD.CategoryID)
          .Parameters.AddWithValue("@QuantityPerUnit", PROD.QuantityPerUnit)
          .Parameters.AddWithValue("@UnitPrice", PROD.UnitPrice)
          .Parameters.AddWithValue("@UnitsInStock", PROD.UnitsInStock)
          .Parameters.AddWithValue("@UnitsOnOrder", PROD.UnitsOnOrder)
          .Parameters.AddWithValue("@ReorderLevel", PROD.ReorderLevel)
          .Parameters.AddWithValue("@Discontinued", PROD.Discontinued)
        End With
        'executenonquery returns 1 doe success and 0 for failure
        NewRecordIDNo = Convert.ToInt32(sqlCmnd.ExecuteNonQuery)
        sqlTrans.Commit()
        Return NewRecordIDNo
      Catch ex As Exception
        sqlTrans.Rollback()
        Throw ex
        Return -1
      Finally
        If sqlConn.State = ConnectionState.Open Then
          sqlConn.Close()
        End If
      End Try
    End Function

    Public Shared Function UpdateRecordByIDNo(PROD As Product) As Integer
      Const sqlString As String = "UPDATE Products SET ProductName = @ProductName, SupplierID = @SupplierID, CategoryID = @CategoryID, QuantityPerUnit = @QuantityPerUnit, UnitPrice = @UnitPrice, UnitsInStock = " & _
                                  "@UnitsInStock, UnitsOnOrder = @UnitsOnOrder, ReorderLevel = @ReorderLevel, Discontinued = @Discontinued WHERE ProductID=@ProductID"

      'be careful here! - Access has a quirk whereby if you do not add the parameters below in the order that they are
      'used in the query then the query WILL run but will NOT do anything - it bombs for some reason????
      'This is an Access quirk and is not replicated in SQL Server

      Dim RowsUpdated As Integer

      Try
        sqlConn.Open()
        sqlTrans = sqlConn.BeginTransaction
        With sqlCmnd
          .Connection = sqlConn
          .Transaction = sqlTrans
          .CommandText = sqlString
          .Parameters.Clear()
          .Parameters.AddWithValue("@ProductName", PROD.ProductName)
          .Parameters.AddWithValue("@SupplierID", PROD.SupplierID)
          .Parameters.AddWithValue("@CategoryID", PROD.CategoryID)
          .Parameters.AddWithValue("@QuantityPerUnit", PROD.QuantityPerUnit)
          .Parameters.AddWithValue("@UnitPrice", PROD.UnitPrice)
          .Parameters.AddWithValue("@UnitsInStock", PROD.UnitsInStock)
          .Parameters.AddWithValue("@UnitsOnOrder", PROD.UnitsOnOrder)
          .Parameters.AddWithValue("@ReorderLevel", PROD.ReorderLevel)
          .Parameters.AddWithValue("@Discontinued", PROD.Discontinued)
          .Parameters.AddWithValue("@ProductID", PROD.ProductID)
        End With
        'executenonquery returns 1 doe success and 0 for failure
        RowsUpdated = sqlCmnd.ExecuteNonQuery
        sqlTrans.Commit()
        Return RowsUpdated
      Catch ex As Exception
        sqlTrans.Rollback()
        Throw ex
        Return -1
      Finally
        If sqlConn.State = ConnectionState.Open Then
          sqlConn.Close()
        End If
      End Try
    End Function

    Public Shared Function DeleteRecordByIDNo(PROD As Product) As Integer
      Const sqlString As String = "DELETE FROM Products WHERE (ProductID = @ProductID)"
      Dim RowsUpdated As Integer

      Try
        sqlConn.Open()
        sqlTrans = sqlConn.BeginTransaction
        With sqlCmnd
          .Connection = sqlConn
          .Transaction = sqlTrans
          .CommandText = sqlString
          .Parameters.Clear()
          .Parameters.AddWithValue("@ProductID", PROD.ProductID)
        End With
        'executenonquery returns 1 doe success and 0 for failure
        RowsUpdated = sqlCmnd.ExecuteNonQuery()
        sqlTrans.Commit()
        Return RowsUpdated
      Catch ex As Exception
        sqlTrans.Rollback()
        Throw ex
        Return -1
      Finally
        If sqlConn.State = ConnectionState.Open Then
          sqlConn.Close()
        End If
      End Try
    End Function
  End Class

  Public Class NextTableEtc
    'add each table in database defined as above
    'add additional function and subroutines to do whatever you want with the data that you need to retrieve
  End Class
#End Region
End Class

    For Each PROD As DataLayer.Product In DataLayer.Product.GetAllRecords
      ListBox1.Items.Add(PROD)
    Next

    If MsgBox("Add record?", vbYesNoCancel) = vbYes Then
      Dim AddRec As New DataLayer.Product
      Dim RecAdded As Integer

      With AddRec
        .ProductName = "My New Product Name"
        .SupplierID = 1
        .CategoryID = 1
        .QuantityPerUnit = "New Quantity"
        .UnitPrice = 9.5
        .UnitsInStock = 0
        .UnitsOnOrder = 20
        .ReorderLevel = 10
        .Discontinued = False
      End With
      Try
        RecAdded = DataLayer.Product.InsertRecord(AddRec)
        If RecAdded > 0 Then
          ListBox1.Items.Add(AddRec)
          MsgBox("Added!")
        End If
      Catch ex As Exception
        MsgBox(ex.Message)
      End Try
    End If

    If ListBox1.SelectedIndices.Count > 0 Then
      If MsgBox("Update record?", vbYesNoCancel) = vbYes Then
        Dim UpdateRec As DataLayer.Product
        Dim RecUpdated As Integer
        Dim UpdateRecIndex As Integer

        UpdateRec = ListBox1.SelectedItem
        UpdateRecIndex = ListBox1.SelectedIndex
        With UpdateRec
          .ProductName = "My New Product Name"
        End With
        Try
          RecUpdated = DataLayer.Product.UpdateRecordByIDNo(UpdateRec)
          If RecUpdated > 0 Then
            ListBox1.Items.Remove(UpdateRec)
            ListBox1.Items.Insert(UpdateRecIndex, UpdateRec)
            MsgBox("Updated!")
          End If
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End If
    End If

    If ListBox1.SelectedIndices.Count > 0 Then
      If MsgBox("Delete record?", vbYesNoCancel) = vbYes Then
        Dim DeleteRec As DataLayer.Product
        Dim RecDeleted As Integer

        DeleteRec = ListBox1.SelectedItem
        Try
          RecDeleted = DataLayer.Product.DeleteRecordByIDNo(DeleteRec)
          If RecDeleted > 0 Then
            ListBox1.Items.Remove(DeleteRec)
            MsgBox("Deleted!")
          End If
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End If
    End If

    
For Each ctrl As Control In Me.Controls
      Select Case ctrl.GetType
        Case GetType(TextBox)
          Dim txtBox As TextBox
          txtBox = CType(ctrl, TextBox)
          txtBox.Enabled = False
        Case GetType(CheckBox)
          Dim chkBox As CheckBox
          chkBox = CType(ctrl, CheckBox)
          chkBox.Enabled = False
        Case GetType(ListBox)
          Dim lstBox As ListBox
          lstBox = CType(ctrl, ListBox)
          lstBox.Enabled = False
        Case GetType(ComboBox)
          Dim cboBox As ComboBox
          cboBox = CType(ctrl, ComboBox)
          cboBox.Enabled = False
      End Select
    Next

Imports System
Imports System.Collections.Generic            REM To work with collections
Imports Microsoft.VisualBasic

Public Class Form1
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        REM Two individuals take turns drawing a unique random name from a hat until all 500 or so 
        REM names/cards are pulled and displayed. Then same array of names is to be regenerated and
        REM randomized for next prizes to be drawn again as unique random names/cards by the same two
        REM individuals (or players).
    End Sub

    Private Sub btnEnd_Click(sender As System.Object, e As System.EventArgs) Handles btnEnd.Click
        REM create a form with three buttons and a richtextbox
        End
    End Sub

    Private Sub RichTextBox1_Click(sender As Object, e As System.EventArgs) Handles RichTextBox1.Click
        'Dim Rcards As New ArrayList()
        Dim deckIdx As New List(Of Integer)
        Dim Rcards As New List(Of String)
        Dim i As Integer = 0
        Dim Count As Integer = 0
        Dim prng As New Random

        Rcards.Add("1 ABE")
        Rcards.Add("2 BARB")
        Rcards.Add("3 BOB")
        Rcards.Add("4 CHARLES")
        Rcards.Add("5 DON")
        Rcards.Add("6 EDWARD")
        Rcards.Add("7 FRAN")
        Rcards.Add("8 GENE")
        Rcards.Add("9 IAN")
        Rcards.Add("10 JILL")

        REM LOAD ARRAY     
        For i = 0 To Count - 1
            Rcards.Add(i)
            MessageBox.Show(Rcards(i))
            REM PROBLEM: The ONLY card THIS displays is "! ABE"  ???????
        Next i

        REM SHUFFLE NAMES - i.e. Rcards
        REM PROBLEM: The following code generates an error that states:
        REM "Value of type 'System.Collections.Generic.List(Of Integer)' cannot be converted to Integer"

        deckIdx = deckIdx.OrderBy(Function(r) prng.Next).ToList
        'deckIdx = Enumerable.Range(0, 9).ToList
        MessageBox.Show(Rcards(i))
        REM PROBLEM: The above code doesn't appear to generate any names
        
        REM Remove the name/card picked
        REM Error received for next line of code is: "Index is out of range"
        REM I remarked this line of code out because the program won't load right the next time if I don't
        'deckIdx.RemoveAt(i)
        REM The following code isn't allowed either with collections
        'deckIdx += 1
        REM The SECOND person now draws a random unique name. Thus a card must be removed each time
        REM there is a name drawn so that the next person has one less name available in the hat.
        REM Remarks: The ONLY value returned is "1 ABE" - There are NO ERRORS to indicate problem ????
    End Sub

    Private Function Rcards(deckIdx As List(Of Integer)) As String
         Rem I haven't looked at this code yet but no problems here (yet!)
        Throw New NotImplementedException
    End Function
End Class