MS Access, just like SQL Server, has a
MAX
[
^] function which will return the Maximum value found within a field.
As the updated query will only return a single value, you can then use the
ExecuteScalar()
method and skip using the DataReader.
Dim Cmd As New OleDbCommand
Cmd.Connection = conn
Cmd.CommandText = "SELECT Max(Field1) FROM Table1"
conn.Open()
Textbox1.text = Cmd.ExecuteScalar().ToString()
conn.Close()
Cmd.Dispose()
References:
Min, Max functions (Microsoft Access SQL) | Microsoft Docs[
^]
OleDbCommand.ExecuteScalar Method (System.Data.OleDb) | Microsoft Docs[
^]
Update
To avoid SQL Injection, never ever should a query be created from concatenating commands and variables. The proper way to add variables to a command is by using the
Parameter
[
^] collection of the command object.
Here is an updated snippet for you with your variables added in
Cmd.CommandText = "SELECT Max(ID) FROM Drums WHERE DrumType = @DrumType AND Flange = @Flange"
Cmd.Parameters.AddWithValue("@DrumType", ComboBox7.SelectedItem)
Cmd.Parameters.AddWithValue("@Flange", YY)
conn.Open()
Added Reference:
SqlParameterCollection Class (System.Data.SqlClient) | Microsoft Docs[
^]