Solved

Update Access Query Data from VB.Net Form

Posted on 2007-03-29
6
185 Views
Last Modified: 2010-04-23
I'm using VB.Net and an access database.  I have a query already created inside access that is linked to a dataset.  I used the vb.net data wizard to create a new form.  I now have a form called DataForm1 that shows all the fields inside my access data query.  Everything works fine except I'm wondering if there is a way to get the update button to work.  In Access if you change a value in the query the data in the table changes as well.  I would like to mimic that on my vb.net form.  Any ideas on if this is possible?
0
Comment
Question by:brettawv
  • 3
  • 3
6 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 18822382
It will depend on the nature of the Access query.  In Access itself, some select queries are updateable and some are not.  For example, queries which join tables and/or use any of the aggregate functions are not.  So far as I know, if the select query was updateable in Access itself, it should be updateable from a VB.NET application: otherwise not.

Roger
0
 

Author Comment

by:brettawv
ID: 18823234
It is updateable.  The problem seems to be when it creates a dataset.  The program no longer looks at the actual table or query it seems to be looking at the xml file that is created for the dataset.  Is there a way to set it up so that it doesn't look at the xml duplicate.  I want it to write directly to database file.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18824121
I'm assuming, from what you've said, that this is VB.NET 2003.  Is that correct?  If it is, there is no reason, from what you've said so far, why the update shouldn't work.  The Data Form wizard should have set it all up to handle that in the first place.

In particular, I don't understand at the moment what you mean by "The program no longer looks at the actual table or query it seems to be looking at the xml file that is created for the dataset."  The xml file is just the definition of the dataset.  It doesn't normally include any data, nor is it updated in any way by any changes to the data.

Are you getting any error messages?  In particular, does it say anything about Update requiring a valid Update (or Delete) command?

Roger
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:brettawv
ID: 18824457
The error I get says "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."  I read in another post somewhere that when you run the app it creates a duplicate of your data in the bin folder.  I checked the bin folder and saw a file with an extension .pdb.  I deleted it and when I ran the app again the .pdb file reappeared so I assumed this was a copy being created of the actual data being loaded.  I may be all wrong on this issue but that was the assumption I was making.  I'm new to using the data form wizard to create my data connections.  I've always created my connections through my own code in the past.  Any help or guidance is greatly appreciated.
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 18824978
I don't think that the problem has anything to do with the Data Form wizard.  It looks like your Access query doesn't have (or your App doesn't recognise) a primary key.  Update commands - as you'll know from coding your own - are always in the format UPDATE thisTable SET thisField = thisValue ... WHERE IDField = thisID.  The last bit is essential so that the specified changes are made to the right record.  Similarly with a Delete command.  If a wizard cannot identify what field (or combination of fields) uniquely identifies each record in any set of data it is referred to, it simply does not produce the necessary Update and Delete commands.

That's my analysis of the problem.  Does it make sense in the light of what's included in your query?  And, if so, is there enough information there for you to solve it?

If not, come back with some details of what the uniquely identifying field/s should be and I'll see if I can help you to "roll your own" commands to fill the gaps left by the wizard.

Roger
0
 

Author Comment

by:brettawv
ID: 19042412
Thanks for your help!
0

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 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