Solved

Update Access Query Data from VB.Net Form

Posted on 2007-03-29
6
179 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

757 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

16 Experts available now in Live!

Get 1:1 Help Now