SQL Select Statement: MS Access Database from VB.net

The following is a simple example of using the SELECT statement. The ItemMaster table which it is operating on has the following fields:

Field	Type
PartNumber	AUTOINCREMENT PRIMARY KEY
Description	varchar(255)

I am selecting all fields from the table (SELECT * ...).

• The first field I retrieve will be of type AUTOINCREMENT PRIMARY KEY which is stored as a Long in MS Access so I use reader.GetInt32(0).ToString() to get the first field, at location 0 using the GetInt32. I need to convert it to a string to display it.
• The second field I retrieve will be of type varchar(255) which is already a string so I can directly use the reader function  reader(1)  to get the second field.

Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Console.WriteLine("Connection Open!")
            Dim command As New OleDb.OleDbCommand("SELECT * FROM ItemMaster", cnn)
            Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
            While reader.Read()
                Console.WriteLine("PartNumber: " + reader.GetInt32(0).ToString() + vbTab + "Description " + reader(1))
            End While
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine("Can not open connection " + ex.Message)
        End Try
        Console.ReadLine()
    End Sub
End Module

The following is an example of using the MAX statement to find the maximum part number. A separate function has been created to execute the MAX statement. Note that the reader.Read() does not have to be part of While loop if there is only one value that you anticipate to read. The MaxPartNumber function uses the GetInt32 function and the (0) refers to the first field that is returned.

Imports System.Data.OleDb.OleDbConnection
Module Module1
    Sub Main()
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\CIM_CourseDevelopment\BillOfMaterial.accdb; Persist Security Info=False"
        Dim cnn As New OleDb.OleDbConnection(connStr)
        Try
            cnn.Open()
            Console.WriteLine("The max part number is: " + MaxPartNumber(cnn).ToString)
            cnn.Close()
        Catch ex As Exception
            Console.WriteLine("Can not open connection " + ex.Message)
        End Try
        Console.ReadLine()
    End Sub
 
    Function MaxPartNumber(cnnMax As OleDb.OleDbConnection) As Long
        Dim command As New OleDb.OleDbCommand("SELECT MAX(PartNumber) FROM ItemMaster", cnnMax)
        Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()
        reader.Read()
        Return reader.GetInt32(0)
    End Function
End Module