The New TableAdapterManager in Visual Studio 2008

Geek Lectures - Things geeks should know about » Blog Archive » The New TableAdapterManager in Visual Studio 2008

Tue, 08 Jan 2008 07:44:24 GMT

PingBack from http://geeklectures.info/2008/01/07/the-new-tableadaptermanager-in-visual-studio-2008/

re: The New TableAdapterManager in Visual Studio 2008

Jason Stangroome — Wed, 09 Jan 2008 11:24:49 GMT

We saw the new TableAdapterManager and thought it would be great. Unfortunately, due to a logic bug in the designer code, it fails to work with already open connections and therefore cannot perform an transactional UpdateAll across multiple DataSets without involving MSDTC. For the time being, we have a PowerShell script to fix the bad code everytime the designer file gets regenerated.

http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=311689

- Jason

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Wed, 09 Jan 2008 19:58:43 GMT

Hi Jason,

While I think your scenario should be supported in the designer, I'd be careful updating too many rows in a transaction when they really don't need to be. Even in my example above it's not really necessary for me to have the transaction wrapped around the entire batch. The risk of concurrency errors will increase in a multi-user system the more rows you're trying to update, and a rollback of all the data may not be desired if a single row cannot be updated. For instance, in the example above if the update worked but the insert failed, I probably would still want the update to succeed because the orders are independent of eachother. In other types of scenarios, however, you'd want the entire set to rollback so what I'm doing would make sense.

BTW, for now you can still roll your own manually like we did in VS 2005 if you do want to support multiple datasets.

Cheers,

-B

re: The New TableAdapterManager in Visual Studio 2008

Alex Simkin — Fri, 11 Jan 2008 19:15:57 GMT

I noticed that TableAdapterManager explicitly creates and commits transaction. How to make it participate in the ambient (TransactionScope controlled) transaction?

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Fri, 11 Jan 2008 19:51:20 GMT

Hi Alex,

Just like Jason mentions, the generated code doesn't allow use of a shared connection thus not allowing for the this scenario with the TableAdapterManager. It will be fixed in VS2008 SP1. For now you can do it the same as you did in VS 2005. Here's the code for that http://blogs.msdn.com/bethmassi/archive/2007/07/11/tableadapters-and-transactions.aspx

Cheers,

-B

re: The New TableAdapterManager in Visual Studio 2008

Sabino — Sat, 19 Jan 2008 12:14:46 GMT

Dear Beth, i'm a beginner in .NET developing and i'm writing a database management software like northwind. I like a lot the new data components you demo in "how do i" video series and the new enhancement such as TableAdapterManager and LINQ. I have only a problem and i would ask you the better solution. For example: if i load a datasset from database using tableadapter's fill method to use that data in a form and the same data are loaded and modified in another form or externally by another user of the database, how can i refresh the data loaded in the first dataset without refill the dataset?

Can you help me please?

Many thanks,

Sabino

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Mon, 21 Jan 2008 21:10:39 GMT

Hi Sabino,

There's a couple ways to do it. You can use the Merge method on the dataset to merge new records into a dataset you already have filled or you can set the .ClearBeforeFill property on the TableAdapter to False so that when you call the Fill method, it will merge the records returned from the query instead of clearing the DataSet first.

http://msdn2.microsoft.com/en-us/library/bz9tthwx(VS.80).aspx

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Serge — Fri, 25 Jan 2008 00:13:40 GMT

Hi Beth,

I have a problem with the code you suggested above. I have a project which is using an access (mdb) database. First of all, can I use the code above for this ?

Second when I try to use the tablemanager.updateall I always get an error saying there has to be a relation the parent table.

I've tried setting the cascade rule on the database relation level, on the dataset level (as you suggested), on both but I always get an error.

What am I doing wrong and what kind relation do you have to set and where ?

Do I have to set other properties (like incrementseed etc.) ?

I followed your code very thoroughly but still no succes.

Thank you for your reply

Serge

re: The New TableAdapterManager in Visual Studio 2008

May East — Sat, 26 Jan 2008 18:52:45 GMT

in regards to the bug in VS2008 that Jason found in the designer code - Does this effect only people who want to use TransactionScope controlled transactions ?

if i would like to use regular ado.net sql transactions, am i affected by this issue ?

re: The New TableAdapterManager in Visual Studio 2008

Brian in England — Thu, 31 Jan 2008 15:55:36 GMT

Hi beth,

I have just entered the code for the N-Tier DataSets by following the video for the “How Do I” series.

I have found a significant problem after the first save on the DataGridView form. When I try to save a second time I am getting an exception in the DataManager class in the DAL project.

In the line saved = (manager.UpdateAll(products) > 0) (orders in your code) it reports a ‘Concurrency violation’. I think you need to add new code as it thinks to record I have is out of sinc with the one at the server.

I have added a timestamp column to my table as I asumed it was a good way to keep track of changes on the table. I see there is no code at all to handel concurrent access to records so the user can choose to over write or drop and update the record without getting all the records again and reseting the whole DataGridView.

Maybe its time for another video building on the first solution!

If you have answer the problem somewhere can you direct me to it?

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Thu, 31 Jan 2008 18:17:22 GMT

Hi Serge,

Regarding Access, you need to add code to the DataAdapter's RowUpdated event in order to retrieve the @@IDENTITY after a row is inserted. This is because Access doesn't support multiple statements in one command so the generated Insert code on you TableAdapter cannot include the retrieval of the primary key. I'll put it on the list to work on a sample for Access.

-B

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Thu, 31 Jan 2008 18:19:35 GMT

Hi May East,

Yes this will affect any type of transaction you want to perfrom accross *DataSets*. SQL-Transactions are automatic accross DataTables inside a single DataSet. In order to perform a transaction accross DataSets you will need to write the same code you did in VS 2005 for now. This will be fixed in SP1 of VS 2008.

Cheers,

-B

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Thu, 31 Jan 2008 18:39:50 GMT

Hi Brian,

I'm working on getting the CodeGallery project rolled out for this sample, I'm hoping it will be up this week. If it isn't, I'll put it up here. Feel free to use the contact form to email me and I'll send you the finished code.

Couple things to check, you need to make sure you pass the DataSet ByRef on the Save and that you merge the records properly on the client afterwards (as shown in the video). The TableAdapters also need to be set up properly so that they can retrieve the Identity keys on inserts. This should happen automatically if you have an identity PK in the database. By default, the concurrency check does so by checking all the original values against current values in the columns in the WHERE clause of the UpdateCommand, but if you set up a timestamp it will just use that (I recommend that approach if you don't need the finer concurrency check because this performs better.)

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Brian in England — Thu, 31 Jan 2008 20:54:24 GMT

Well thanks for getting back to me as you must be very busy. I will do some checks and get back to you if I don’t get it sorted. Maybe I missed something in the video.

On another subject. I have a problem with the ‘smallmoney’ column type in the SQL server table. It always shows 4 past the decimal like ‘0.0000’ and the same format is appearing on the client forms. Can I change this on the server or on the BindingSource or where?

p.s. I see you play guitar. I help out with a band called ‘Kamagra’

You can see them here. http://www.systonsocial.co.uk/

Thanks for your time

Brian

re: The New TableAdapterManager in Visual Studio 2008

Brian in England — Thu, 31 Jan 2008 21:48:25 GMT

You were right! I missed out…

Me.ProductsDataSet.Merge(changes)

Me.ProductsDataSet.AcceptChanges()

After the save and I also added....

If changes.HasErrors Then

MsgBox("Data on the form is invalid and can't be saved", MsgBoxStyle.Exclamation)

Exit Sub

End If

...before the save to stop invalid data being sent.

re: The New TableAdapterManager in Visual Studio 2008

Brian in England — Fri, 01 Feb 2008 15:38:01 GMT

Help..

In the N-Tier DataSets solution I have just added a new Query to the dataset in the DAL project. I have added the code in the DataManager.vb file of the same project and also updated IService.vb and Service.vb. When I returned to my form in the Client project I can’t find the new method in the proxy.

With this code…

Dim proxy As New xxxxService.ServiceClient

Me.ProductsDataSet.Merge(proxy.newmethod)

Is there something else I need to update to get it?

Sorry to be a pain but others must be getting the same problem.

Brian

re: The New TableAdapterManager in Visual Studio 2008

Brian in England — Fri, 01 Feb 2008 16:17:25 GMT

Sorted..

I needed to update the Reference.vb under the Reference.svcmap in the Client Service References.

Is there in easy way to update this automatically?

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Fri, 01 Feb 2008 19:14:12 GMT

Hi Brian,

When you update the service, you need to also update the proxy code on the client. This code is generated for you. Just expand the Service References on the client project in the Solution Explorer, select the service reference and select "Update Service Reference".

-B

re: The New TableAdapterManager in Visual Studio 2008

Brian in England — Sat, 02 Feb 2008 18:00:45 GMT

I have a ComboBox problem today.

It is bound to one table and the value member is the primay key but the problem comes from the fact the display member is a decimal (fdRate) not a sting and doesn’t like matching up with the user input. Is there a way of converting the display member (fdRate) to as string so that it can match with the users typing then set the value member (fdKeyID). You must have come across this problem before when not working with string values. When converting I need to see it as “#.##” formating.

Brian

re: The New TableAdapterManager in Visual Studio 2008

Serge — Wed, 13 Feb 2008 11:35:31 GMT

Hi Beth,

Regarding the access database @@IDENTITY problem, I think I found a(nother) simple solution by setting the IncrementSeed and IncrementStep to a value on the dataset.

Than I don't get the constraint problems and everything works fine.

Is this solution also valid or is it not the way to do it ?

Regards

Serge

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Wed, 13 Feb 2008 19:37:37 GMT

Hi Serge,

You're solution will not work in a multi-user application if you allow re-editing of the inserted rows. If you do not handle retrieving the identity generated by the database then you will need to refresh the client dataset (clear the dataset then call Fill again) so you can get back the key.

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Serge — Fri, 15 Feb 2008 13:47:08 GMT

Hi Beth,

I thought that would be a problem. Fortunately the project I'm working on is single user (for the moment).

I have another project which is multi-user, so I think it will be "bye bye" Access and "welcome" Sql.

Serge

re: The New TableAdapterManager in Visual Studio 2008

Frans — Sun, 02 Mar 2008 05:40:51 GMT

Hi Beth,

I've generate Typed Dataset using Dataset Designer Wizard in Visual Studio 2005...

Let's say I have 2 Table in the dataset, [CustomerGroups] and [Customers] with 1 to many relation (CustomerGroups 1->MANY Customers).

When I want to show the [CustomerGroups]."Name" in [Customers] Data Table so I configure the Query became like this :

"select t1.*, t2.Name as CustomerGroupsName from Customers t1, CustomerGroups t2 where t1.CustomerGroupsID = t2.id"

After I saved this query the data table is changed with added 1 column (CustomerGroupsName), but the main problem is Te First Generated Insert, Update, And Delete Command is gone and in the InsertCommand, UpdateCommand and DeleteCommand Property is set to (none)

How can I solve this problem? Because I need to work with Joined Query and need to update it and in the other side I know that command builder can't generate multiple table.

Is there any Property in TableAdapter like "MainTable" Property so I can set the main table I want to update? Or The New TableAdapterManager can solve This Problem?

Please Help... Thanks

Frans

re: The New TableAdapterManager in Visual Studio 2008

Brandon — Thu, 13 Mar 2008 20:10:04 GMT

I have a form that creates and edits Invoices. Everything works when I edit an Invoice using the tableadaptermanager.UpdateAll method. However, when I create an Invoice, I get a concurrency violation. I set up a trace in SQL Profiler and found that if I edit an Invoice by adding a new line item and editing another, an InsertCommand is executed on my InvoiceDetail table and then an UpdateCommand is executed on my InvoiceDetail table. Again everything works. When I create an Invoice with one or more line items, an InsertCommand is correctly executed on my Invoice table, but then an UpdateCommand is INCORRECTLY executed on my InvoiceDetail table. I checked the rowstate for each InvoiceDetail record and it is correctly set as Added and so why is an UpdateCommand executing on the InvoiceDetail table (instead of an InsertCommand) and causing the save to fail? Any ideas? I'll be happy to provide more info if needed. Please help. Thanks.

re: The New TableAdapterManager in Visual Studio 2008

Brandon — Fri, 14 Mar 2008 01:58:25 GMT

I got it working after some trial and error. I had the Accept/Reject Rule set to Cascade. Changing it back to None fixed my problem.

re: The New TableAdapterManager in Visual Studio 2008

Pete — Fri, 11 Apr 2008 20:49:37 GMT

Beth,

I'm running into a problem with the TableAdapterManager. I get the following error when I try to update a record. "Update requires a valid UpdateCommand when passes DataRow collecting with modified rows." I have used the configuration wizard in the DataSet designer to allow it to build the Select, Insert, Update, Delete statements for each of the tableadapters in the DataSet. Although I have all of the settings set the same for each adapter, it doesn't not create the Update and Delete statements for many of the TableAdapters. Can you tell me what I'm doing wrong?

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Mon, 21 Apr 2008 22:14:47 GMT

Hi Frans,

You will need to write the queries yourself if you have an entity that is a join of multiple tables. I would suggest writing them as stored procedures in the database and then specifying those in the command properties of the TableAdapters.

Here's the documentation on TableAdapter configuration that also explains how to connect them to new or existing stored procedures:

http://msdn2.microsoft.com/en-us/library/dex7k4dw.aspx

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Mon, 21 Apr 2008 22:16:47 GMT

Hi Pete,

The designer can't create update statements for DataTables that originate from more than one table in your database, is that what is happening in your situation? If so, you need to either specify the statement to execute or write a stored procedure to handle that case.

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Dmitry — Tue, 22 Jul 2008 11:37:57 GMT

1. table in database have constrain on some fields

2. Open form with datagrid and delete row

3. Enter new row with same data

4. Try to update

5. Exception!!! Becouse it need the first row be deleted from database first then insert command

How can i workaround this problem?

re: The New TableAdapterManager in Visual Studio 2008

Oscar Mandiola — Sat, 16 Aug 2008 17:51:20 GMT

Hi Beth, i have just installed vb2008 sp1 and i can't see the "Hierarchical Update" in the dataset properties (in the existing versions); the solution is properly set to "net framework 3.5", I created a new empty dataset and this property does not appears.

re: The New TableAdapterManager in Visual Studio 2008

Oscar Mandiola — Mon, 18 Aug 2008 16:50:26 GMT

Hi Beth, I fixed the problem, thanks anyways.

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Sat, 23 Aug 2008 00:14:16 GMT

Hi Dmitry,

You can't use the TableAdapterManager to automatically do that. Instead just call the TableAdapter.Update method passing it just the deleted rows first then you can update the rest.

Me.MyTableAdapter.Update(Me.MyDataSet.MyTable.Select("", "", DataViewRowState.Deleted))

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Joe — Thu, 11 Sep 2008 00:58:01 GMT

in regards to Serge's post in January of 2008, I'm having similar issues trying to get the FK for a child table with the end result being -- cant add records for a child table. Interestingly, I cant even manually add the primary key / foreign key in the child table and let it update that way. Any ideas?

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Thu, 11 Sep 2008 01:13:37 GMT

Hi Joe,

If you aren't using SQL Server (or more specifically a database that can run batch SQL statements like Access) then you will have to add a handler to the RowUpdated event and retrieve the key maunally. If you look at the attached sample you will see that the Insert statements on the TableAdapters are actually batch statements that return the keys. I'm not an Access expert but here's an article that may help you handle this situation if it applies to you:

http://msdn.microsoft.com/en-us/library/ms971502.aspx#manidcrisis_topic2

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Joe — Thu, 18 Sep 2008 01:39:44 GMT

This all makes sense, however once you get the PrimaryKey -- do you have to manually code the entering of the the PK as the Foreign Key in the child tables AND handle adding new records? It wont be bad if the child table was represented as a datagridview -- but if they were all textboxes, it would be pretty tedious if you have many child objects you need to check on.

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Thu, 18 Sep 2008 01:59:40 GMT

Hi Joe,

If you set up the DataSet relations and the related BindingSources on the controls properly this will happen automatically for you. See this video:

http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Joe — Thu, 18 Sep 2008 07:11:11 GMT

Even if your child table is represented as text boxes instead of a datagridview? I can get it to work for the latter without any modification... Just not as a detail view. Fantastic videos by the way. Nice, Clean and organized.

JOe

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Thu, 18 Sep 2008 20:38:24 GMT

Hi Joe,

Yes that should work as long as you set up the relations and bindingsources correctly as shown in that video. You also need to make sure you call EndEdit on the BindingSources to push the changes into the DataSet before calling UpdateAll on the TableAdapter. Perhaps that is the problem?

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Marco — Sat, 18 Oct 2008 21:08:27 GMT

Hi Beth,

I saw your video-series...it's great!

I have a question: in my previous application I use a MS Acces databse with password. Because of the "identity-problem" of the access database, now I'm using an mdf database and I want to set its password. How do I set the password?

Another question: I don't understand what is the SQL Server version installed by VB 2008 (component of the VS 2008 iso image)...I have only two directories: Microsoft SQL Server and Microsoft SQL Server Compact Edition.....what version is it?

Thanks

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Tue, 21 Oct 2008 20:04:21 GMT

Hi Marco,

I believe SQL Server 2005 Express is installed with Visual Studio. You probably want to install SQL Server Management Studio though. It's easier to add users and manage security from here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

Here's a set of tutorials on how to use it:

http://msdn.microsoft.com/en-us/library/bb934498.aspx

For SQL Server 2008 Express with SSMS see: http://www.microsoft.com/downloads/details.aspx?FamilyId=7522A683-4CB2-454E-B908-E805E9BD4E28&displaylang=en

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Albert Rugel — Sat, 06 Dec 2008 03:28:59 GMT

Are there any microsoft whitepapers that explain how to create a multi-user database applications with vb.net?

re: The New TableAdapterManager in Visual Studio 2008

Mani — Tue, 09 Dec 2008 08:58:36 GMT

Hi Beth,

Thanks for the video tutorials on VB. They are the wonderful practical demo tutorials , I have ever seen.

I have been following the video on how to update related tables in VB 2008. As mentioned in the video I have modified the relationships between a parent and child table to Cascaed for Update and Delete rule.But here I am facing a new problem.(I have the parnet table added to the form as 'Details' and related child table entries under datagridview.)

The problem : Whenever I delete a child row from datagridview, the entire Parent table corresponding to that and all other childs entries get deleted!. Why does this happen?

Here what I am trying to do is, I created a SProc, for delete, which sets a bDeleted column to True in the table. I have changed the datatableadater property for delete command to this SProc and set the parameter properly. Also in the Delete rule (under edit relationship - datatables) I tried setting it to None. But this time it throws an error saying "Cannot delete this row because constraints are enforced on relation FK_Tests_Test_GroupTests, and deleting this row will strand child rows." . Could you please guide me on this.

thanks

S.Mani

re: The New TableAdapterManager in Visual Studio 2008

Roberto — Thu, 11 Dec 2008 07:55:07 GMT

Hi Beth, I'm just starting to work with DataSet, DataBinding, DataSource and TableAdapter on VB2008.

Now, after a lot of trying and error tests, I have already a connection to a MS Access base using a DataSet, TableAdapter, DataGridView and a DataBinding.

Now I have a barely idea about how its work, but I want to know exactly what is the purpose of a DataBinding, DataSource and Dataset.

All the information I can find is about how to use it, but did not explain what is it purpose.

So I get mess and I don't know if I must to make the queries to the DataSet, to the DataSource, to the DataGridView or DataBinding.

Thanks in advance, Roberto.

(Excuse me if my English is no to good, I'm trying to get a better one).

re: The New TableAdapterManager in Visual Studio 2008

S.Mani — Thu, 11 Dec 2008 08:41:39 GMT

Hi Roberto,

I believe, it would be really helpful to you to go through the video tutorials given by Beth. Please find the link given below.

http://msdn.microsoft.com/en-us/vbasic/bb466226.aspx#formsoverdata

Beth,

I managed to solve the previous problem, by removing the "enforce constraint" for the dataset. and by changing the binding source of the binding navigator between child and parent datatables on datagridview enter and leave events.

I am not sure if there is a better way to do it. if yes , please suggest.

thanks,

S.Mani

re: The New TableAdapterManager in Visual Studio 2008

Tomas Ekenman — Sun, 21 Dec 2008 18:17:41 GMT

Hello Beth,

Thanks for all the great videos!

Regarding the "new" Table Adapter Manager, I can't get the example to work with the SQL Compact version. Perhaps it is not ment to be used with it or am I missing out on something?

Merry Christmas

Tomas Ekenman

re: The New TableAdapterManager in Visual Studio 2008

Albert Rugel — Thu, 01 Jan 2009 18:00:55 GMT

Hello Beth,

When you use clickonce to deploy a database program, where are the database and application files installed?

Albert

re: The New TableAdapterManager in Visual Studio 2008

Albert Rugel — Sun, 04 Jan 2009 18:26:43 GMT

Hello Beth,

I have written a database program and now I need to write a utility program to backup and restore the database. How would I do this in visual basic. What white papers can I read to help me get started? A few code snippets would be great..

Thank You

Albert

re: The New TableAdapterManager in Visual Studio 2008

David — Tue, 13 Jan 2009 05:37:33 GMT

Hi Beth,

Does the TableAdapterManager have an issue with cascading Foreign Keys on a Datatable with a self join?

I'm getting the error "ForeignKeyConstraint FK_ABC_DEF requires the child key value (xxxx) to exist in the Parent Table."

The self join relation has the Update Rule set to Cascade...

Regards

David

re: The New TableAdapterManager in Visual Studio 2008

David — Tue, 13 Jan 2009 10:44:40 GMT

Hi Beth, I resolved that issue! The error was unrelated to the Self-join relation - the tableadapter manager updates the FK of the child records perfectly, Brilliant feature!

Regards

David

re: The New TableAdapterManager in Visual Studio 2008

Lalit Kumar Soni — Sat, 07 Mar 2009 17:07:13 GMT

Hi Beth massi I found everwhere The hirachical prpperty of the Table adabter manager but it could not be see any where plz give the metode by which i can update the property Set it to True for the Insert update Delete Metode .

Plz Help me you can post replay on my mail id lalit_1984@rediffmail.com

thanks

re: The New TableAdapterManager in Visual Studio 2008

Alexei — Tue, 14 Apr 2009 01:22:22 GMT

Hi Beth, thanks for all this great videos

I have a question about the tableadaptermanager

I need to merge the results of the saved dataset in the client side, but i wouldnt like to use the logic you used in your video (merge the returned dataset, delete added rows and then acceptchanges)

In VS2005, i used to set acceptchangesduringupdate=false for the tableadapter, but now with the tableadaptermanager, it doesnt work, even if i set it like this:

Dim taMngr As New DataSet1TableAdapters.TableAdapterManager

Dim ta As New DataSet1TableAdapters.CitiesTableAdapter

ta.Adapter.AcceptChangesDuringUpdate = False

taMngr.CiudadesTableAdapter = ta

taMngr.UpdateAll(changes)

Any ideas of how t prevent the tableadaptermanager from calling accept changes on the saved data so i can merge it back in the client without getting duplicated rows??

Thanks in advance

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Wed, 22 Apr 2009 19:15:12 GMT

Hi Alexei,

The technique I showed is necessary with n-tier datasets that have database-assigned primary keys because there's no way to know what the key will be on the client. If you do a straight merge without deleting the added rows first then you will end up with duplicate rows. You can create a more generic method that also checks and merges any errors in the tables as well. Call this after your middle-tier dataset comes back from an attempted save regardless if it returns true or false:

Private Sub MergeChanges(ByVal dsSource As DataSet, ByVal dsTarget As DataSet)

Dim tblTarget As DataTable

Dim i As Integer

With dsTarget

For i = 0 To .Tables.Count - 1

tblTarget = .Tables(i)

'Remove added rows on the client first so we don't end up with duplicates

Dim addedrows = (From row In tblTarget _

Where row.RowState = DataRowState.Added).ToArray()

For Each row In addedrows

tblTarget.Rows.Remove(row)

'Watch your constraints here, this will fail if the dataset constraints are invalid

.Merge(dsSource)

For i = 0 To .Tables.Count - 1

tblTarget = .Tables(i)

' This will merge errors, new keys and any other middle-tier changes

If dsSource.Tables.Contains(tblTarget.TableName) Then

'Only AcceptChanges on the client's tables if the middle-tier returned no changes.

If dsSource.Tables(tblTarget.TableName).GetChanges Is Nothing Then

tblTarget.AcceptChanges()

End If

End With

End Sub

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Mohammad — Wed, 13 May 2009 17:18:21 GMT

Hi Beth,

You spoke about RowUpdated event several times to resolve problem with Access databases, but I could not found that event in controls that designer generated for my form (xxxTableAdapter). So I noticed that event handler belongs to DataAdapter instead of TableAdapter class. So why should I do now? Change all TableAdapters with DataAdapter?

I also suggest you to add a field for email adresses in comments, so one can be notified automatically when others reply to his/her comment.

Thanx

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Fri, 15 May 2009 03:52:44 GMT

Hi Mohammad,

Please see this post: http://blogs.msdn.com/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx

Also, you can subscribe to the comment RSS in the comment notification area above.

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Angelika — Thu, 30 Jul 2009 01:41:19 GMT

I am having the same problem as Frans(above), except that I am using a local Access database. My TableAdapter uses a join and displays fields from two tables. I am able to follow your solution for updating the table (stored procedures) using an sqlserver database, but can't get it to work for my Access database. All the store procedure & parameter options are not activated.

Please help!

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Thu, 30 Jul 2009 01:56:37 GMT

Hi Angelika,

Access doesn't support stored procedures so these options are not available for this database. However you may be able to gleen a solution from these two posts I have done on Access and DataSets:

http://blogs.msdn.com/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx

http://blogs.msdn.com/bethmassi/archive/2009/07/22/filtering-child-datatables-using-tableadapters.aspx

You can write an Update statement that updates only the main table and ignores the joined fields but if you need to update both tables you will need to have two separate TableAdapters against Access. Is there any chance you can use SQL Express edition, it's free.

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Angelika — Thu, 30 Jul 2009 03:23:36 GMT

Hello Beth,

Thanks so much for your help. I finally got it to work. I do indeed only need to update the main table.

The solution was fairly simple.

1) Create a TableAdapter for the main table only and copy the TableAdapter UPDATE procedure (stored in the TableAdapter properties window)

2) Configure the old SELECT query in the “TableAdapter Configuration Wizard Query Builder” to include fields from both tables (main table needs to contain a primary key field; tables were already joined in Access)

3) Paste the old procedure into the now blank TableAdapter UPDATE procedure

4) After creating a DataGridView, I was able to display fields from both tables and update the main table.

Thanks again.

re: The New TableAdapterManager in Visual Studio 2008

Sharf — Mon, 03 Aug 2009 15:37:29 GMT

Hi Beth,

I have a winform named 'BillForm' with bounded textboxes (CustomerIDTextBox, BillNoTextBox and TotalTextBox - its datasource is typed dataset 'BillDataset'). I want to update Balance column in CustomerDataTable(it is also typed dataset) and its primarykey column CustomerID with equivalent CustomerIDTextBox & TotalTextBox value in 'BillForm'.

CustomerDataTable.Balance +=CDec(TotalTextBox.Text)

I'm not load or fill CustomerDataset in 'BillForm' formload event. Can you please provide me a sample program?

re: The New TableAdapterManager in Visual Studio 2008

Sharf — Mon, 03 Aug 2009 15:45:10 GMT

I tried this code, the datatable dt is updated but my database not update.

Private Sub BillingBindingNavigatorSaveItem_Click _

(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles BillingBindingNavigatorSaveItem.Click

Dim da As New CustomerDataSetTableAdapters.CustomerTableAdapter

Dim dt As New CustomerDataSet.CustomerDataTable

da.FillByCustomerID(dt, CInt(CustomerIDTextBox.Text))

dt.Rows(0)("Balance") += CDec (NetTotalTextBox.Text)

dt.AcceptChanges()

da.Update(dt)

End Sub

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Mon, 03 Aug 2009 19:20:58 GMT

Hi Sharf,

Don't call AcceptChanges until AFTER you successfully call Update. When you call AcceptChanges it marks all the rows in the DataSet with "no change" so if you call Update afterwards there will be no changes to send to the backend database.

Take a look at this topic in the library for more information: http://msdn.microsoft.com/en-us/library/ss7fbaez.aspx

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Sharf — Mon, 03 Aug 2009 20:22:20 GMT

Great! Thanks for your time and thanks for the link...

re: The New TableAdapterManager in Visual Studio 2008

Declan — Wed, 16 Sep 2009 19:14:35 GMT

Hi Beth,

Great tutorial. In my form I only need to create a master \ detail record (no need to load existing entries). Can you give me an idea of how to go about this?

Like in the tutorial it should allow for multiple detail records to be added but as a detail view of the DataGridView as there will be quite a number of detail columns and its better to lay them out individually.

Any help much appreciated.

Declan

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Wed, 16 Sep 2009 19:48:23 GMT

Hi Declan,

When you drag the related order details from the Data Sources window just select "Details" instead of DataGrid. The toolbar above the gid in that example works against the OrderDetailBindingSource so it will allow you to navigate throwgh the detail rows no matter if the details are displayed in a grid or layed out individually.

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Declan — Thu, 17 Sep 2009 10:25:29 GMT

Sorry the issue is more to do with the saving of the record. I was able to layout the data with a binding navigator for the detail as you described.

When the form is opened the ( + ) symbol to add detail entries is enabled. When the specific data is entered and the save icon is clicked on the parent navigator, the primary key (identity seed) is not being saved on child record. I have followed the tutorial to ensure the EndEdit() is called on each binding source as well as the AddingNew event on the child binding source.

re: The New TableAdapterManager in Visual Studio 2008

Declan (in Ireland) — Thu, 17 Sep 2009 13:25:31 GMT

I managed to solve the issue...now onto validating the data on save :-)

Thanks for the help and keep up the excellent work

re: The New TableAdapterManager in Visual Studio 2008

Kostas — Wed, 04 Nov 2009 15:10:22 GMT

Hi Beth

have a problem with my datasase.In my form i have three related tables.The problem is that i can't update all three tables but only the first two with hierarchical update ( I used the article from Microsoft MSDN site).

All datagrids are drag-and-drop from data-source all the code is auto-generated. I use VS 2008 and Northwind db.

Thanks

re: The New TableAdapterManager in Visual Studio 2008

Beth Massi — Wed, 04 Nov 2009 22:52:36 GMT

Hi Kostas,

You need to make sure you set up both relations between the three tables as a Foreign Key and Constraint and set Update and Delete rules to cascade. Then you need to make sure you call EndEdit on the parent BindingSources as you navigate to the children.

Take a look at this video and make sure to repeat the process for your third table:

http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

HTH,

-B

re: The New TableAdapterManager in Visual Studio 2008

Kostas — Fri, 06 Nov 2009 09:11:04 GMT

Hi Beth

I followed your instructions from the video. I set up the commands for the third table , set the relations to cascade but nothing. I put three ENDEDITS . more help would be apreciated

thanks