Solved

Update detached datagrid to SQL DB

Posted on 2007-03-23
4
371 Views
Last Modified: 2010-04-23
Hi,
After populating a datagrid with a button I want to detach it from it's underlying dataset table so changes in the datatable do not show immidiatly on the datagrid.
This part I got done by filling a temp. dataset/table from the DB and refer the datagrid.datasource to the temp datatable.
Now I want to save changes made to the datagrid back to the DB. Since the reference for the dataset/table is missing my update throws an exeption.

How can I reconnect to the database and update rows in the DB with DataRowState.Modified after the datagrid got detached from its filling dataset/table?

Thanks
0
Comment
Question by:kolibree53
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 18785517
By default, ADO.NET works with a disconnected data model.  That is, an app's local datatable having been filled, the connection closes and there is no further communication between the database and the datatable until the app initiates it.  So the behaviour you are seeking is what you should have under ADO.NET without any "special" arrangements like "filling a temp. dataset/table from the DB and refer the datagrid.datasource to the temp datatable".

So can you please say a bit more about your arrangements?  How are you filling the "temp. dataset/table from the DB"?  How does this "temp. dataset/table" differ from any other "dataset/table"?  If the datagrid was originally bound to the temp datatable, how did it become detached from it?

Roger
0
 

Author Comment

by:kolibree53
ID: 18785767
My setup was that way, that I loaded, during App start, whole fixed length DB tables into a Dataset/Datatable and made them available publicly so I could work with them from any Form I wanted to. Whenever I made changes to the datatable I would save them back with a public dataadapter.

The problem with that was, that when I showed data from a datatable in a datagrid and a thread would change the datatable in the background my App would throw an exception because I can't update the GUI from a thread.

So I wanted to "detach" the datagrid from the underlying datatable, so the thread could change the datatable and I would update the grid with a button. This I wanted to use instead of the invoke method.  

In the meantime (long night) I changed my whole approach with reading from the DB and I scrachted the Idea of a public dataset with public datatables. Now I read/write from a DB on every occasion.

The issue I still have is that I fill the datagrid in a sub called by a "Refresh" button.

        cn.Open()
        Dim daTS As New SqlDataAdapter("SELECT * FROM TS ", cn)
        daTS.Fill(ds, "TS")
        cn.Close()
        Dim dvTS As New DataView(ds.Tables("TS"))
        dgTable.DataSource = dvTS

Another Sub called from a "Save" Button should save back the changes made in the datagrid to the SQL DB. Because it's another Sub all the references made in the "Refresh" Sub are expired (what I call temp).
The question is now how can I "attach" the datagrid back to the dataadapter/datatable so the changes are recognized with DataRowState.Modified.

Thanks

0
 
LVL 34

Accepted Solution

by:
Sancler earned 125 total points
ID: 18786463
Put this at the start of your form

   Private daTS As SqlDataAdapter
   Private tblTS As New DataTable("tableTS")

In your form load sub put this

        daTS = New SqlDataAdapter("SELECT * FROM TS ", cn)
        Dim cb As New SqlCommandBuilder(daTS)
        dgTable.DataSource = tblTS

Change your refresh sub to just this

        daTS.Fill(tblTS)

And in your save sub put this

        BindingContext(tblTS).EndCurrentEdit
        daTS.Update(tblTS)

If I've understood your requirements correctly, that should do what you want.  In detail

a)  I can see no reason, in the code you show, for putting the relevant datatable in a dataset.

b)  I can see no reason, in the code you show, for interposing a dataview between the datatable and the datagrid.

c)  the dataadpater and datatable are Private to the Form, but ...

d)  their declaration at Form level means they are available to all procedures within the Form

e)  there is no need expressly to open and close the connection when you are using a dataadapter.  That handles the open and closing of the connection itself.

Roger
0
 

Author Comment

by:kolibree53
ID: 18788996
Looking good. I tried for a while to work with the dataadapter on the form level. But I never got the syntax right.
The line "BindingContext(tblTS).EndCurrentEdit" is a nice bonus I didn't know.

Thanks
 
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

705 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

19 Experts available now in Live!

Get 1:1 Help Now