Click here to Skip to main content
14,865,447 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Let me start off by saying I'm not a programmer at all. I'm trying to learn some basic stuff though.

I'm using VS and SQL Server. I've setup my datasources. The basics of doing a "normal" datagridview isn't an issue for me because it's rather intuitive and simple. In this instance I'm totally lost.

I have two database tables, one row in each. I need to create a Datagridview with 5 columns and specified table column values as rows in the datagridview.

Database Table One Columns Example Info:
ID, Desc1, Desc2, Desc3, Desc4, Desc5, Other columns....

Database Table Two Columns Example Info:
ID, Tax1, Tax2, Tax3, Tax4, Tax5, Start1, Start2, Start3, Start4, Start5, End1, End2, End3, End4, End5, OtherColumns....

Tables are joined.

Columns in Datagridview:
Number - This would be autonumber
Description - Row info would come from Database Table One column values Desc1 through Desc5. One per row.
Tax - Row info would come from Database Table Two column values Tax1 through Tax5
Start - Row info would come from Database Table Two column values Start1 through Start5
End - Row info would come from Database Table Two column values End1 through End5

How I want the values displayed in the Datagridview:

NO, DESCRIPTION, TAX, START, END
1, Desc1, Tax1, Start1, End1
2, Desc2, Tax2, Start2, End2
3, Desc3, Tax3, Start3, End3
4, Desc4, Tax4, Start4, End4
5, Desc5, Tax5, Start5, End5

Unfortunately the table layouts cannot be changed as it links to other master tables. For example changing the columns to rows in the database table.

What I have tried:

I honestly don't know what the best way would be to achieve this as the data in the Datagridview for the columns should still be able to be changed and updated.

Any guidance in the right direction would be appreciated.
Posted
Updated 24-Feb-21 6:19am
v2
Comments
[no name] 24-Feb-21 10:22am
   
Showing data from different tables is called 'join'. Of course to join data from different tables one need a relation between the data of the different tables. Here a nice intoduction about joining data:
Visual Representation of SQL Joins[^]
Logan1980 24-Feb-21 10:36am
   
100% I have no issue with that part of it. The join works perfectly because of the ID column. Thus one of the reasons the layout of the table cannot be changed. My biggest issue is with getting the specified database column values into rows and my specified columns in the datagridview since there's only one row in each table but multiple columns.
[no name] 24-Feb-21 11:04am
   
Your update of the question makes it more clear. Seems to go into the direction 'pivot' of a result, not my field :-) but be patient there are some SQL specialists around here ;)
Logan1980 24-Feb-21 11:16am
   
I thought it might. :D I have made use of pivot/unpivot before with something else I played around with so I have an idea of how to accomplish that. However, with that, I'm a bit further puzzled because the data still has to be able to be updated. I no idea how to get around that with unpivot.

1 solution

The database design was blown in the first place: repeating groups.

And you say they're "joined"; that still results in "repeating groups". So you should "pivot" the tables (to "normalize" them); then join them; resulting in the data set (table) you require.

And unless you go back and normalize the original tables, it will be a mess to try and "update" them also.

Short story, start over and normalize (eliminate repeating groups) in the original tables. Anything else you do, just compounds the problem.
   
Comments
Logan1980 24-Feb-21 12:31pm
   
Thanks Gerry. It confuses me as well WHY the tables would be like this. The layout design isn't specifically my idea. It seems quite a number of tables function the same way. May I ask what you mean by "repeating groups"?
Gerry Schmitz 24-Feb-21 14:18pm
   
You have multiple taxes (i.e. "tax types").

Logically, you have a tax type, and an amount; not simply "some tax fields".

These amounts are dependent on the "ID". "Normalizing" them (3rd normal form) results in a table of ID, TaxType, Amount.

A UI would show a parent-relationship between whomever "ID" represents, and the "types of taxes" with amounts that they have.

The advantages are it's easier to query and the "ease" with which one can add / delete / update taxes. The tradeoff with more storage is not worth considering.

If "start / end" is a "dependent" property of of Id + tax type, add it to that table; otherwise add it to a "master" tables of "tax types" (and rate, desc, etc.)

(sorry for the typing error revisions: no "preview")
Logan1980 24-Feb-21 16:02pm
   
Thanks for the explanation Gerry. Yeah. I'm just redoing these tables as I'm going along now. Ultimately I'll have more tables but at least I can get the information out and updated/inserted/deleted correctly.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900