Go Premium for a chance to win a PS4. Enter to Win


DataGridView Sorting Part 2

Posted on 2006-07-20
Medium Priority
Last Modified: 2008-02-01
Ok you were right Sancler, I did try something too fast.
What actually works is it doesnt stop the sort ... but the data arent updated..
OK so I need your help. I will write again what I want and maybe work that togheter:

1) I got a timer Interval : 6 seconds.
2) OnLoad: fillRooms()

    Public Sub fillRooms()
        dtRooms = oData.selectTable.Tables(0)
        dgRooms.DataSource = dtRooms
        dgRooms.Columns(0).Visible = False
    End Sub

3) On timer_Tick, I need to get again the data from my SQL table (I got a SELECT statement on a class)
4) The new data needs to be "commited" on the DataGrid, but I want to keep the sort if it has been sorted and I want to keep the selectedRow of course.

5) that's all.

Question by:PhilippeRenaud
  • 3
  • 2
LVL 34

Expert Comment

ID: 17150657
Is there any reason why the data has to be placed in a DataSet rather than just in a free-standing DataTable?  For the purpose you have described, I doubt it, but there may be other considerations of which I'm not aware.

Unless there are such other considerations, the steps you should take are

1)  At form level (or some other level at which your various bits of code can "see" it, like at the start of a module) put

    Dim dtRooms As New DataTable

2)  Get rid of this line

        dtRooms = oData.selectTable.Tables(0)

in your fillRooms() Sub.

That should give you a datatable which exists throughout the life of your application and a permanent binding between your datagrid and that datatable.

When your application starts, and at 6 second intervals thereafter, you want to fill, or refill, that datatable.  To do that you will probably use a dataadapter.  It is an SQL database, so you will need an SQLConnection and an SQLDataAdapter.  Those are going to be used frequently, so rather than re-creating them every time you need them, you should declare them once, with a scope that any necessary procedure can see.  So ...

3)  At form level or module level something like

    Dim ConnectionString as String = "<put your details here>"
    Dim con as New SQLConnection(ConnectionString)
    Dim SQLString As String = "SELECT * FROM <put your table name here>"
    Dim da As New SQLDataAdapter(SQLString, con)

The filling and refilling of the datatable is going to be done at the start and every 6 seconds.  You need a procedure to do it.  

4)  Something like this

    Private Sub RefreshData()
    End Sub

5)  You need to fill the datatable when the application first starts: and again when your timer fires.  So in both the Form_Load sub and in your Timer Sub you add this line


Then you come back and tell me - with details as specific as you can make them - what's not working ;-)


Author Comment

ID: 17167100
OK, I try but not the same as what you wrote...Let me explain:

The only reason why I was writting the lign: dtRooms = oData.selectTable.Tables(0)
is because I got a class called: frmMainData.vb  and in it there is all my ConnectionStrings, DataAdapter... look:

    Dim cnn As SqlConnection
    Dim strConnectionString As String = "server=......"
    Dim myConnection As New SqlConnection(strConnectionString)

    Public Function selectTable() As DataSet
        Dim strSql As String
        Dim dataAd As New SqlDataAdapter
        Dim myCommand As SqlCommand
        Dim dsTable As New DataSet

            strSql = " " _
                + " SELECT ....... " _
                + " FROM tables t, status s, category c " _
                + " WHERE(t.Status = s.statusID And t.Category = c.categoryID) "
            myCommand = New SqlCommand(strSql, myConnection)
            dataAd.SelectCommand = myCommand
        Catch ex As Exception
        End Try
        Return dsTable
    End Function

So It returns the DsTable so when i write    dtRooms = oData.selectTable.Tables(0) well I get the "new" data in SQL.

so Why if in my Timer I write :

        dtRooms = oData.selectTable.Tables(0)

and in fillRooms :

        dtRooms = oData.selectTable.Tables(0)
        dgRooms.DataSource = dtRooms

Why it would not work?   Because actually I tried and my datagrid diseapear (like if there was no data to write) Only the header was there. Im just not sure what is different of your code.. If you say   Phil..Phil.. just to what I said, I will replied: Alright Im noob, lets just copy code.. lol, but i wanna learn a little
LVL 34

Accepted Solution

Sancler earned 2000 total points
ID: 17167641
This is the problem I think (EMPHASIS added)

        Dim dsTable As NEW DataSet

Every time you call oData.selectTable you replace the old dataset with a new one.

It's like I said in the other thread (although there I was talking about the DataTABLE rather than the DataSET).  If you destroy it - as you effectively do by replacing it every time with a new one - that is different from keep using the same one but emptying and refilling it every time you want to refresh the data.  If you keep using the same one, and just refreshing the data in it, the binding to the datagrid persists and the sortorder will be maintained.  But if you keep using a new one the binding to the datagrid is broken, the binding has to be remade, and the sortorder is lost.

Is that sufficient of a clue for you to make it work the way you want it to?  I'd have provided code myself, but you do say you "wanna learn a little" so I'll let you have a go at it ;-).  I'll just add another hint.  Remember that, although you can bind a control to a DataTable whether that datatable is freestanding - like I suggested - or in a dataset - as you would prefer it, it is - at the end of the day - the DataTABLE that you are binding to, not the DataSET.  So just making sure the DataSET doesn't get destroyed will not be enough.  

Come back if you need help.


Author Comment

ID: 17167998
it worked... :)

I made some changes. The only thing tho, The focus on the last Selected Row before the Timer tick, well when it ticks it comes back at the first Row. So it will select the first rows and does not keep the last one selected.

I think I got a solution for that but... I know it in my head.

I think I have to get the selectedrow before the tick , keep it in a integer...then after the dtRooms.Clear etc.. I write: ok select the row (integer)

Im just not sure how to SET a focus on a row

Author Comment

ID: 17168174
ok it was  dg.CurrentCell ...

oh well, everything is sweet now hehe

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question