Solved

Update detached datagrid to SQL DB

Posted on 2007-03-23
4
380 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
[X]
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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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