How to retrieve latest 25 data from a SQL server table in VB.NET

Question

1.00/5 (1 vote)

See more:

I am trying to retrieve the latest 25 data from the SQL Table in VB.NET, but I get the top 25 data. I want the latest 25 data in ascending manner only. Can you help me?

What I have tried:

VB

Dim TableNameTcp As String = Label138.Text

VB

Dim query As String = "SELECT TOP 25 * FROM [" + TableNameTcp + "] _
ORDER BY [IndexID] ASC"

VB

Dim dt1 As New DataTable()
If Not con.State = ConnectionState.Open Then
     DBConnect()
End If
cmd = con.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = query 
cmd.ExecuteNonQuery()
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt1)
DataGridView8.DataSource = dt1

VB

Public Sub DBConnect()
        Try
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
	        con.ConnectionString = "Data Source=Server_Name; _
            Initial Catalog=Database; User Id=User_Name; _
            Password=Password; MultipleActiveResultSets=True"
	        If con.State = ConnectionState.Open Then
     		    con.Close()
	        End If
	        con.Open()
	        If con.State = ConnectionState.Open Then
     		     MsgBox("open")
	        End If
	    Catch ex As Exception
     		MessageBox.Show(ex.Message)
	    End Try
End Sub

Posted 11-Sep-23 20:04pm

Developer6 RDL

Updated 12-Sep-23 8:17am

Deeksha Shenoy

v2

Add a Solution

Comments

Richard MacCutchan 12-Sep-23 4:02am

Your SELECT statement is requesting the TOP 25 rows, so that is what is returned. What exactly is wrong with the result?

2 solutions

Solution 2

To add to what Richard has - rightly - said, I'd strongly recommend that you don't rely on any ID ordering but instead add a timestamp to each row which you can automatically add (via a default value) when you INSERT your row and optionally change when you UPDATE it.

That way you get a lot better flexibility and it's less likely that anyone will faff with the ordering by accident when they make changes later. It also makes your SQL much more obvious when you read it!

A very good idea is to use a UTC timestamp rather than a localized one as it means things work seamlessly even with clock changes and multiple timezones.

Posted 12-Sep-23 8:43am

OriginalGriff

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)

Richard Deeming · Accepted Answer · 2023-09-11T22:26:00

If you want the last 25 rows, then you have to sort them in descending order and select the top 25 rows.

If you want those rows to be sorted in ascending order, you can either sort them in your code after you've retrieved them, or use a nested query:

SQL

SELECT * FROM (SELECT TOP 25 * FROM [YourTable] ORDER BY [IndexID] DESC) ORDER BY [IndexID] ASC

NB1: Avoid using SELECT * FROM ...; instead, explicitly list the columns you want to return. This will ensure that the columns are returned in the expected order, and you don't end up returning too much data if you add more columns to the table.

NB2: If your TableNameTcp variable can be influenced in any way by the user, then you have introduced a SQL Injection[^] vulnerability to your code. If you can't use a hard-coded table name, then you need to verify that the supplied table name is one of the existing table or view names.