Question Dataset Designer problems

[jamie123]

I'm using visual studio 2008. I have a datagridview, and I am trying to display data from two tables in this datagridview, one being customer name..which is on one table, the other data is customer financial information, which is in another table..(we'll call these financial and demographics), I thought I could just create a relationship between the two common columns (Customer Number) within the dataset designer, and then in the fillby() statement for the Financial table, add in the two columns (demographics.fname, demographics.lname) that I need, so the select statement would say

SELECT Financial.ID, Financial.Payments, Financial.CustomerNo, demographics.CustomerNo, demographics.fname, demographics.lname FROM Financial, demographics WHERE Financial.CustomerNo = demographics.CustomerNo

I tried that and got no errors so i thought it worked..until i tried to build and it gives me these errors now "Insert' is not a member of FinancialTableAdapter wherever my insert statements are located, which I have not touched. I realized this is because in Advanced options when configuring queries in the dataset designer, all of the three checkboxes were unchecked, so i thought hm..check them again! so I did, and it unchecked themselves again, I then reverted my data back to what it was before this incident...restarted, but no luck, same stuff happens.

So I guess my two questions are:

1. How can I get my insert, update, and delete statements back to be a "member of FinancialTableAdapter"

and

2. How can I successfully extract the data I need from these two tables using relationships in the dataset designer and have them display on my datagridview?

Thanks in advance

 

[cjard]

I'm using visual studio 2008. I have a datagridview, and I am trying to display data from two tables in this datagridview, one being customer name..which is on one table, the other data is customer financial information, which is in another table..(we'll call these financial and demographics), I thought I could just create a relationship between the two common columns (Customer Number) within the dataset designer, and then in the fillby() statement for the Financial table, add in the two columns (demographics.fname, demographics.lname) that I need, so the select statement would say

If you perform a JOIN as the data source for a datatable, the result is typically:

Writable on both sides only where both tables have all the primary key columns as join members
Writable on one side only where that side expresses its primary key columns as unique after the join
Read only where either side does not fully express its primary key


What I mean by this:

TableA: PKCol, DataCol
TableB: PKCol, DataCol

A.PKCol = B.PKCol

This JOIN is key-preserved on both sides. Every key of A and every key of B is a key of A_B. That said, in this situation, the columns should all be in one table!


TableA: PKCol, DataCol
TableB: PK1Col, PK2Col, DataCol

A.PKCol = B.PK1Col

This JOIN is key-preserved only on the B side! Rows from A can repeat for each unique row of B. Only B and columns from B will be updateable. This restriction is imposed by the database, because multiple identical rows of A cannot be updated to each have different values:

A.PKCol,A.DataCol,B.PK1Col,B.PK2Col,B.DataCol

1,DataA_1,1,Z,DataB_1Z
1,DataA_1,1,Y,DataB_1Y


See the repeated datarows from A?

-
If neither key is preserved (i.e. TableA has a PK1Col and Pk2Col and the join is only on PK1Col) :

A.PKCol,A.PK2Col,A.DataCol,B.PK1Col,B.PK2Col,B.DataCol

1,R,DataA_1R,1,Z,DataB_1Z
1,R,DataA_1R,1,Y,DataB_1Y
1,S,DataA_1S,1,Z,DataB_1Z
1,S,DataA_1S,1,Y,DataB_1Y

SELECT Financial.ID, Financial.Payments, Financial.CustomerNo, demographics.CustomerNo, demographics.fname, demographics.lname FROM Financial, demographics WHERE Financial.CustomerNo = demographics.CustomerNo

Only the side of this join that still has all its primary key columns with no repeated rows will be reliably updateable, but because the designer doesnt care to work out which side that is (it could, but it doesnt - it wasnt programmed to) you must write the update queries yourself

Typically what I do here is decide which table I want to be updated, its usually only one. If it is both, use a client side DataRelation and two separate datatables. If only (e.g.) Financial record is to be updated then here's a trick:

Make a TableAdapter, using only the Financial table
Generate all statements
Now edit the SELECTCOMMAND property (DO NOT reconfigure the TA) only, to include the new columns and the new table
VS asks "You have changed the select command. Do you want to update the other update queries to reflect the changes to the new command text?" NO

Now, you can see data from demographics, but only changes to financial will be saved.

-

In oracle, we can also turn the query into a VIEW and write an INSTEAD OF trigger so that an attempt to update the view will update the relevant tables instead.

You can also write stored procedures to update the data in two tables and have those sprocs be the ones to update each table correctly

1. How can I get my insert, update, and delete statements back to be a "member of FinancialTableAdapter"

the name FinancialTableAdapter implies it is for the financial table only
and youre trying to make it a ViewOfFinancialAndDemographicsJoinedTableAdapter

Doesnt work

2. How can I successfully extract the data I need from these two tables using relationships in the dataset designer and have them display on my datagridview?

You cant have them display in one DGV in this way, as it isnt designed to display from multiple sources. If youre set on that idea then you must:

Elect one table to be the parent and one to be the child
In the child table, add all the columns of the parent (by name)
Set each column to have an expression: Parent.[ColumnName]


E.g.
Parent is Financial
Child is Demographics

Parent has a column called FinanceName
Create a column in child called ParentFinanceName (name irrelevant; human semantic only)
Set the expression of the column to be: Parent.[FinanceName]

Now fille the parent rows and the children. Show the children in the grid and the parents will be seen, read only. (By definition, because a parent can have many children, parents are NOT key preserved so they cannot be updated unless youre using stored procedures)


Hope this helps

 

[jamie123]

i'm pretty sure i get what you're saying, and i'm eager to try the parent/child thing to display in the dgv as that sounds like it would solve my problem..but the thing is that to my knowledge i have reversed everything that I did that caused the insert update and delete statements to not work. I have no relationships setup with that TA right now and its' not trying to select columns from other tables, i just have it set the way it previously was before this incident, yet whenever I hit advanced options and try to check all 3 checkboxes that say "automatically create insert update delete statmeents" or whatever, it doesn't let me and after I hit ok it just reverses my changes to the checkboxes. I understand why it wouldn't work the way i was trying it, but shouldn't i be able to insert/update/delete now that I reversed everything?

 

[jamie123]

cjard, actually, is there anyway in visual studio to just revert back to the last successful build and revert to that actual code? Because if I could do that then at least I could go back to where I was and get things working again, because as of now I do not know what exactly to change to get my TA out of read-only mode

 

[cjard]

Afraid not, though you might be able to Undo Undo Undo... if the VS has been open for the entire duration of the change

TO be honest though, i'd jsut delete the TA and DT from the dataset and add a new one, setting the quiery to be "select * from yourtable"
Basically re-do the tableadapter. You can save any custom queries you have added to it, of course, by cutting and pasting them into another (temporary) tableadapter

 

[jamie123]

Yeah, that's what i'm going to end up doing. I'm reinstalling vs as well, I think that there was some corruption going on, as every 5 or so times I'd load the solution I'd get an error saying something along the lines of "package failure possible environment corruption." I'd usually disregard it and reload the solution but I've been noticing some weird problems lately. I'm reinstalling today so i'll let you know how setting up that relationship goes, thanks for all your help

 

[jamie123]

Well I got everything working back to normal again, but when I tried to setup that Parent/Child relationship I had a few problems...I setup the relationship between the two tables under an identical column, that column being Patient No, which is in both tables..I set Demographics as my Parent and Financial as my child, I made a new column, and set the expression to "Parent.[Fname]" (Fname is the name of the column I would like from demographics to be displayed in the datagridview. That worked without errors, when I moved to put the Fname column (which is now in the Financial datatable) into my Fillby() select statement, it told me at the end of the wizard that Fname is not a valid column, i'm guessing because it is not in the Financial table, it allowed me to continue anyway but I thought that I am probably doing something wrong, could you explain to me what I'm not doing right?




Thanks!

 

[cjard]

er. RIght. What bits of advice of mine did you heed? You seem to be going for the longest and most bug-prone solution..

 

[jamie123]

I don't even know, I just started working on it again today (was away for 2 weeks on vacation). What advice should I use lol? I still just want to display columns from another datatable in my datagridview that have the same PK. I do not need to be able to modify them, just view them