dcsimg
Results 1 to 11 of 11

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

    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

  2. #2
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,698

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

    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.
    Last edited by kebo; Nov 5th, 2014 at 08:39 AM.
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

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

    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?

  4. #4
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,698

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

    Quote Originally Posted by bmf073 View Post
    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?
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    29

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

    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?

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,932

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

    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.
    My usual boring signature: Nothing

  7. #7
    Still learning kebo's Avatar
    Join Date
    Apr 2004
    Location
    Gardnerville,nv
    Posts
    3,698

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

    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?
    Process control doesn't give you good quality, it gives you consistent quality.
    Good quality comes from consistently doing the right things.

    Vague general questions have vague general answers.
    A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.

    ______________________________
    Last edited by kebo : Now. Reason: superfluous typo's

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,382

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

    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,932

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

    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.
    My usual boring signature: Nothing

  10. #10
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,337

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

    Quote Originally Posted by Shaggy Hiker View Post
    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

  11. #11
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,450

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

    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
    Name:  AM.png
Views: 613
Size:  8.9 KB

    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.
    Name:  Untitled-1.jpg
Views: 678
Size:  55.8 KB

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width