Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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