How to set Datagridview to show latest data on top as entered in SQL table?

Hi guys,

I want to know how to set datagridview to show the latest data on top, in order of how its entered in sql table? At the moment its just showing how it appears in Sql table.

any help will be much appreciated.

Best regards,

Bilal

If your sql table has an indexed auto increment field, use ORDER BY <id field> DESC in your sql statement. Doing that will order the data how you want, when you fetch it from the database.

i am afraid i dont have any ID field in my tables. can we not just pickup the order they come in to the table?

Quote:

---

Originally Posted by bmf073

i am afraid i dont have any ID field in my tables. can we not just pickup the order they come in to the table?

---

With out any ORDER BY clause in the statement, that's how the data will come in. Is there a timeStamp field to order by?

I have got date and time field but unfortunately they are coming as a string. Is there anyway to use these,may be by converting?

Does it currently show the most recent data on the bottom of the table? With SQL Server, if you don't have any kind of index, I'm not sure that there is any guarantee as to how the data will organize internally, as SQL Server is going to do whatever is efficient in the abcense of a key. Most likely that will mean that the data will be in the order that it was entered. If that is the case, then what you are looking for is a way to reverse the order. That can be done with a bit of pain, but would be easier if you had something you could sort on.

In mySQL you can use ORDER BY on a date field. I'm not sure about msSQL though.

edit... What is the datatype of your date field in the server?

Quote:

---

I have got date and time field but unfortunately they are coming as a string. Is there anyway to use these,may be by converting?

---

sigh... yes you could... use the CONVERT or CAST methods to do so.... but I have to ask.... WHY is your datetime field a string.... there's no reason for that. None what so ever.
You wouldn't store your numbers as a string, would you? (I ask that assuming that the answer is "no, of course not" but I also recognize that anything is possible and I have seen some weird shhtuff before)

-tg

I've seen that particular weird stuff done by people who would otherwise have known better. There is one guy I'd kind of like to question on that subject, but he left the organization a decade back.

Quote:

---

Originally Posted by Shaggy Hiker

There is one guy I'd kind of like to question on that subject, but he left the organization a decade back.

---

... probably better that way (that he left) ... for your health ... it horrid having a programmer you want to strangle around ...

Kris

Hello,

If you populate the data using the idea below, I added a auto increment data column that when the DataTable is populated from the database table this field will populate thus giving you a field to sort descending so that when new data is added the new rows float to the top. So for this demo I set a SomeBindingSource.DataSource to LoadCustomers, set SomeBindingSource.Sort = "ID DESC" then do SomeDataGridView.DataSource = SomeBindingSource.

Be aware that I am using OleDb data provider but we can change the data provider to say SQL-Server or IBM-DB2 or MySql and the same will work as the key here is the DataTable and BindingSource. Also consider always when creating a database table to include an auto-increment primary key and optionally have a data/time column in the table is not a bad idea either as this can be useful in this case or if you need to know when a recorded was added and to go farther add a reason field so now we can record the date/time and for the reason, add, edit or delete (so in any SQL we would filter out deleted which may or may not be of any use to you)

Code:

---

Public Function LoadCustomers() As DataTable
    Dim dt As New DataTable
    dt.Columns.Add(New DataColumn With {.AutoIncrement = True, .DataType = GetType(Integer), .ColumnName = "ID"})
    Using cn As New OleDb.OleDbConnection With {.ConnectionString = "Your connection string"}
        Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
            cmd.CommandText =
                <SQL>
                    SELECT CompanyName, ContactName FROM Customer
                </SQL>.Value
            cn.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    Return dt
End Function

---

Simplified setup as per above

Code:

---

Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
    bsData.DataSource = LoadCustomers()
    DataGridView1.DataSource = bsData
    bsData.Sort = "ID DESC"
End Sub

---

The ID field is shown, for a real application we would use the following in place of the above when creating the temp column and set ColumnMapping to hidden
Attachment 120601

In the following screen shot the top row was just added via the TextBox controls below, the logic clears the TextBox controls once the data was added to the database.
Attachment 120599