Click here to Skip to main content
15,917,176 members
Please Sign up or sign in to vote.
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
Updated 12-Sep-23 8:17am
v2
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?

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.
 
Share this answer
 
Comments
Developer6 RDL 12-Sep-23 5:05am    
Thank you for the answer, This works fine for my requirement after a small change in that SQL query, "SELECT * FROM (SELECT TOP 25 * FROM [YourTable] ORDER BY [IndexID] DESC) AS Subquery ORDER BY [IndexID] ASC"
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900