DataGridView Sorting Part 2

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.

Who is Participating?
SanclerConnect With a Mentor Commented:
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.

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 ;-)

PhilippeRenaudAuthor Commented:
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
PhilippeRenaudAuthor Commented:
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
PhilippeRenaudAuthor Commented:
ok it was  dg.CurrentCell ...

oh well, everything is sweet now hehe
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.