Solved

DataGridView Sorting Part 2

Posted on 2006-07-20
5
288 Views
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.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)
        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.


0
Comment
Question by:PhilippeRenaud
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Sancler
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()
        dtRooms.Clear()
        da.Fill(dtRooms)
    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

    RefreshData()

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

Roger
0
 
LVL 1

Author Comment

by:PhilippeRenaud
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

        myConnection.Open()
        Try
            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
            Console.WriteLine(ex.ToString)
        End Try
        dataAd.Fill(dsTable)
        myConnection.Close()
        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.Clear()
        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
0
 
LVL 34

Accepted Solution

by:
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.

Roger
0
 
LVL 1

Author Comment

by:PhilippeRenaud
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
0
 
LVL 1

Author Comment

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

oh well, everything is sweet now hehe
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now