Solved

Update Access Query Data from VB.Net Form

Posted on 2007-03-29
6
181 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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