Switch colums and rows in vb.net datagrid
-
I am working on a program that will access an Access database. I am using VB.net 2003. My program in part will record the landings of commercially caught fish. I have structured the database with species names as the column heading and then the rows would be a record of each delivery. A fisherman could make around 50 deliveries in one year. I have in excess of thirty species in this table as column headings. When I use the DataGrid object, the default is to present my database as it is created with speices names as columns headings. Is there a way to switch the rows and columns when it is being displayed in the program, so the species names become the row headings and the data are shown to the right as a column of data?
-
I am working on a program that will access an Access database. I am using VB.net 2003. My program in part will record the landings of commercially caught fish. I have structured the database with species names as the column heading and then the rows would be a record of each delivery. A fisherman could make around 50 deliveries in one year. I have in excess of thirty species in this table as column headings. When I use the DataGrid object, the default is to present my database as it is created with speices names as columns headings. Is there a way to switch the rows and columns when it is being displayed in the program, so the species names become the row headings and the data are shown to the right as a column of data?
If you manually populate the DataGridView, you can put things anywhere you like. As a side comment, are you sure you really want the species as columns? That sounds like an open invitation for problems down the line! I'd consider using some other record layout, like perhaps a species name and a record of delivery on each record. Better yet, normalize it and have a table with species name and an integer, then store the integer to indicate the species on the delivery record. That way you can add species, change their names, add other characteristics, etc. But you know more about the application than I do, so maybe that wouldn't work. Good luck!
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
-
I am working on a program that will access an Access database. I am using VB.net 2003. My program in part will record the landings of commercially caught fish. I have structured the database with species names as the column heading and then the rows would be a record of each delivery. A fisherman could make around 50 deliveries in one year. I have in excess of thirty species in this table as column headings. When I use the DataGrid object, the default is to present my database as it is created with speices names as columns headings. Is there a way to switch the rows and columns when it is being displayed in the program, so the species names become the row headings and the data are shown to the right as a column of data?
Two things spring to mind initially. One, why not use a real database, SQL Express is free and far superior to Access, and two, usng the species as a column name means every time you need to add a new species you have to alter your table. Put the species in one table and the record of catches in another - probably there ought to be a further table with the fisherman details too. Get a book on database design before going any further, as using your current design is asking for trouble in the future. In answer to your question, you can manipulate the data within your code to do what you want, but if you use SQL Express 2008 it can be done by using the PIVOT command.
Bob Ashfield Consultants Ltd
-
Two things spring to mind initially. One, why not use a real database, SQL Express is free and far superior to Access, and two, usng the species as a column name means every time you need to add a new species you have to alter your table. Put the species in one table and the record of catches in another - probably there ought to be a further table with the fisherman details too. Get a book on database design before going any further, as using your current design is asking for trouble in the future. In answer to your question, you can manipulate the data within your code to do what you want, but if you use SQL Express 2008 it can be done by using the PIVOT command.
Bob Ashfield Consultants Ltd
Ashfield I want to thank you and Walt Fair for the response. You both mentioned SQL Express and I will look into it. I do not do a lot of this sort of work, that is why I am still using vb.net 2003. I used an Access database because that it what I had handy, but I am early enough in this project that I can switch easily. As far as the species names as column headings. I understand your point, but for good or bad, the fishery that this program is to used in is one that is managed by the Federal Governemnt and has a management plan that identifies exactly the species involved, so adding new species will not be an issue.
-
Ashfield I want to thank you and Walt Fair for the response. You both mentioned SQL Express and I will look into it. I do not do a lot of this sort of work, that is why I am still using vb.net 2003. I used an Access database because that it what I had handy, but I am early enough in this project that I can switch easily. As far as the species names as column headings. I understand your point, but for good or bad, the fishery that this program is to used in is one that is managed by the Federal Governemnt and has a management plan that identifies exactly the species involved, so adding new species will not be an issue.
-
Ashfield I want to thank you and Walt Fair for the response. You both mentioned SQL Express and I will look into it. I do not do a lot of this sort of work, that is why I am still using vb.net 2003. I used an Access database because that it what I had handy, but I am early enough in this project that I can switch easily. As far as the species names as column headings. I understand your point, but for good or bad, the fishery that this program is to used in is one that is managed by the Federal Governemnt and has a management plan that identifies exactly the species involved, so adding new species will not be an issue.
Peter Leipzig wrote:
As far as the species names as column headings. I understand your point, but for good or bad, the fishery that this program is to used in is one that is managed by the Federal Governemnt and has a management plan that identifies exactly the species involved, so adding new species will not be an issue.
Even so, you should bear in mind that this is a bad design which, although it may not cause you problems in this instance should really not be used - better to do it right every time rather than taking shortcuts which can soon lead to general sloppiness.
Bob Ashfield Consultants Ltd