0

Suppose you have this table structure

---------------|   |--------------|
|   PERSON     |   |   EMPLOYEE   |
|--------------|   |--------------| 
|Id            |   |Id            |
|Name          |   |RegNo         |
|Address       |   |Departament   |
|Email         |   |Salary        | 
|Telephone     |   |FK_Person     | 
|--------------|   |---------------

Then you have a screen that has controls that you can use to CRUD those tables, but this screen joins those two tables to make it seem that those two tables are only one entity.

Screen representing the related entities

I know I could use two separated datasets one for person and one for employee, add person first and then open the employee dataset for adding in two separate screen events, making the user save person first and then add and save employee. What I'm trying to do is a single screen that I can navigate records, edit, add, delete, update. So I wrote a query for a OleDbDataAdapter like this

SELECT *
FROM Person
INNER JOIN Employee
ON Person.Id = Employee.FK_Person

this way I filled the dataset, but I'm only able to navigate, not persist changes.

Note that this is using Microsoft Access.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Leandro Jacques
  • 413
  • 5
  • 19
  • 3
    Why not have the save button save both tables? Preferably in a transaction. – Steve Wellens Nov 25 '14 at 17:15
  • I'd use a stored procedure on the back end and call it from the .net application. – Dan Bracuk Nov 25 '14 at 17:19
  • How would I do this @SteveWellens? I'm starting with VB.NET, don't know many about DB features in VB. What I'm trying to do is a single screen that I can navigate records, edit, add, delete, update. So I wrote a query for a OleDbDataAdapter like this SELECT * FROM Person INNER JOIN Employee ON Person.Id = Employee.FK_Person this way I filled the dataset, but I'm only able to navigate, not persist changes. – Leandro Jacques Nov 25 '14 at 17:28
  • Thought this way too @DanBracuk, use stored procedures, but there are many criticism in using SPs. Other problem is I'm using Access 2010, not a full featured RDBMS – Leandro Jacques Nov 25 '14 at 17:34
  • @LeandroJacques What criticisms would that be, exactly? An SP allows you to prevent SQ injection, and take advantage of having precompiled code stored on the DB. (Not in Access, though, but that wasn't clear from the original question). – SchmitzIT Nov 25 '14 at 17:38
  • @LeandroJacques - If you don't know how to save to _one_ table, you'd better learn how to do that first. Google is your friend. – Steve Wellens Nov 25 '14 at 17:45
  • @SchmitzIT, to maintain code and to find bugs is harder. Using SPs you can't debug code, or are there SPs debuggers out there? It makes it harder to link the SP to it's purpose, SPs naming convention must be very strict. Or the benefits surpasses those points? – Leandro Jacques Nov 25 '14 at 17:48
  • @SteveWellens, I was able to persist the way I told in the question. I used the automated data bind features reading MSFTs walkthroughs, I was able to create a form that persisted data on the tables, but one at a time, I had to create the parent record first, persist it, then create and persist the child, it's not what I want. All examples that I read googling suggests the way I don't want, so I asked here. – Leandro Jacques Nov 25 '14 at 17:53
  • 1
    @LeandroJacques Proper naming conventions usually help a lot ;-) As for debugging, it's pretty straightforward to script out an SP and check the code by simply executing it in small bits and pieces (I like using a transaction that never gets committed. That way you can check the table (if it touches any tables), and simply `ROLLBACK` after. I'm pretty certain that Visual Studio has some ability to debug things (Not a coder, I spend most my time on the SQL Server itself). – SchmitzIT Nov 25 '14 at 19:01
  • 1
    If you're using Access, why don't you just use the built in features of Access? Just create the query in Access and bind the form to the query. – Bobort Nov 25 '14 at 19:50
  • 1
    Have a look at this post for running updates using inner join: http://stackoverflow.com/questions/3867164/sql-update-query-syntax-with-inner-join – eric_the_animal Nov 25 '14 at 19:53
  • 1
    Why use a query join at all? Access models and maintains related tables by use of a sub form. So the right size text boxes should be a sub form. You based the main table (left side) on person, and then create a sub form based on employee. So each form is based on the table (not some sql join). You then simply drop in the sub form on the right side and you are done - no code, no sql, no joins. All this handstanding of suggestions is just fogging up how Access edits and deals with related table (you use sub forms). – Albert D. Kallal Nov 25 '14 at 22:56
  • @Borbot and Albert D. Kallal problem is that not all computers on the company I work has access installed, so it's not possible the use of access directly, unfortunately. – Leandro Jacques Nov 26 '14 at 10:33

0 Answers0