Find last value in accesses database

Question

1.00/5 (1 vote)

See more:

i have tabel1 in accesses database and i need to read Field1 from it
but i didont need to read all Fiedld1 lines
i need to read only last line number on this filed

Field1 contain numbers like
001
002
003
004

and i need to read only last number witch in this case is 004 as example
how can i do it in dr.Read ???

Dim Cmd As New OleDbCommand 'cmd as command to use
        Cmd.Connection = conn 'open connection to database
        Cmd.CommandText = "select * from Table1
        conn.Open() 'open connection
        dr = Cmd.ExecuteReader 'start to read from database
        While dr.Read

            Textbox1.text = dr("Field1").ToString()

        End While
        dr.Close()
        conn.Close()

What I have tried:

<pre>Dim Cmd As New OleDbCommand 'cmd as command to use
        Cmd.Connection = conn 'open connection to database
        Cmd.CommandText = "select * from Table1
        conn.Open() 'open connection
        dr = Cmd.ExecuteReader 'start to read from database
        While dr.Read

            Textbox1.text = dr("Field1").ToString()

        End While
        dr.Close()
        conn.Close()

Posted 9-Jun-20 3:27am

katkot_rewsh

Updated 9-Jun-20 9:03am

Add a Solution

Comments

[no name] 9-Jun-20 9:34am

Think about/try something like SELECT MAX(Field1) FROM Table1
Keep in mind, it is usually very delicate for fields of the type CHAR (x)

Maciej Los 9-Jun-20 9:54am

That doesn't mean the last value ;)

[no name] 9-Jun-20 10:08am

Basically you are right, but I think/speculate it is, what OP is looking for ;)

Richard MacCutchan 9-Jun-20 9:47am

What result do you see when you run that code?

Maciej Los 9-Jun-20 9:58am

There's no "last value"! If datatable object has got primary key, indexes and foreign keys, a 004 value - which is not related to primary key - have NOT to be the last one.
You should provide table schema and more details about the way you're creating such of value.

katkot_rewsh 9-Jun-20 10:13am

These numbers added to database using a function from VB
This value starts with 00 then I increment 1 to be 01 and so on

But each time I need to look at this filed in database and know the last number witch I don’t know before because each time it’s increment

So any suggestion to know the last number usually in that filed ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2020-06-09T03:52:00

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.

VB

Dim Cmd As New OleDbCommand
	Cmd.Connection = conn
	Cmd.CommandText = "SELECT Max(Field1) FROM Table1"
	conn.Open()

'	dr = Cmd.ExecuteReader 'start to read from database
'	While dr.Read
'		Textbox1.text = dr("Field1").ToString()
'	End While
'	dr.Close()

	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

VB

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[^]

katkot_rewsh · Accepted Answer · 2020-06-09T09:04:00

Solution 2

I found the solution

Cmd.CommandText = "select MAX (ID) from Drums where DrumType = '" & ComboBox7.SelectedItem & "' and Flange = " & YY & "" 'select data from table
        conn.Open() 'open connection

        Label54.Text = Cmd.ExecuteScalar

        dr.Close()
        conn.Close()

Posted 9-Jun-20 9:04am

katkot_rewsh

Comments

MadMyche 9-Jun-20 15:45pm

! You just created a SQL Injection Vulnerability !

I will update my answer with the correct way to do this