Solved

Update Access Query Data from VB.Net Form

Posted on 2007-03-29
6
187 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
[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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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