DataGridView Sorting Part 2

Posted on 2006-07-20
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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server insert 13 38
Sending receiving text messages in 15 49 Progress Bar - Maximum Value too large 2 16
How to duplicate form in Visual Studio 2015 2 62
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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

820 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