Solved

calculated cells in datagridview

Posted on 2008-10-20
5
368 Views
Last Modified: 2008-10-27
I am fairly new to VB.NET and need some help.  I am trying to have a form with a datagridview showing available tables in a room by summing the tables used in my Groups table and subtracting the sum from my master Rooms table.  (I can do this quite easily in MSAccess, so I am sure it can be done in VBasic).

Samples of my Tables on a SQL server

Rooms
Room     Tables    Seating     (The Seating is how many to a table)
__________________________
Room1      10        8
Room2      20        8
Room3      25        4


Groups
Group        Event Date     Room   Tables
______________________________________
ABC Co.      7/4/2008       Room1    4
XYZ Inc.      7/4/2008       Room1    3
LMNO LLC   7/4/2008       Room2    5

The SQL Statement looks like this and has no problems.

SELECT  Rooms.Room,
      ISNULL(Rooms.Tables - TableCheck.UsedTables, Rooms.Tables) AS AvTables,
      Rooms.Seating,
                     ISNULL(Rooms.Tables-TabCheck.UsedTables, Rooms.Tables)*Rooms.Seating AS AvSeating
FROM    (SELECT Room, SUM(Tables) AS UsedTables
        FROM [Groups]
        WHERE ([Event Date] = @evtDate) AND (Cancelled = 0)
        GROUP BY Room) AS TabCheck
RIGHT OUTER JOIN Rooms ON TabCheck.Room = Rooms.Room
WHERE (ISNULL(Rooms.Tables-TabCheck.UsedTables, Rooms.Tables) > 0)


I have it listed in my Dataset in the RoomsTableAdapter as both GetDataByAvRooms(@evtDate) and also FillByAvRooms(@evtDate).  The name of the datagrid is AvRooms.

I am filling the Datagridview thusly:
Me.AvRooms.DataSource = RoomsTableAdapter.GetDataByAvRooms(evtDate)

It is returning Rooms.Room and Rooms.Seating correctly.  I can't seem to return the data in the other columns.  Can someone help?
0
Comment
Question by:rko9911
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Priest04
ID: 22765586
Can you show how would a resulting table of data look like?

Goran
0
 
LVL 18

Expert Comment

by:Priest04
ID: 22765595
Also, would you like to query for the calculated data from database, or you would like to calculate it based on the data from dataset?
0
 

Author Comment

by:rko9911
ID: 22767766
Priest04,

The resulting calculated table should look like the following for @evtDate of 7/4/2008 from the sample tables:

Room     Available     Seating     Available
               Tables                           Seating
___________________________________
Room1          3               8                24
Room2        15               4                60
Room3        25               4               100

Currently, my datagridview is showing the Correct Rooms and the seating, but leaving the calculated fields of AvailableTables and AvailableSeating blank.  I only want to be able to view the resulting table.  

I would rather not caluclate data from the resulting dataset but I will if that is my only choice.


0
 
LVL 18

Assisted Solution

by:Priest04
Priest04 earned 75 total points
ID: 22768783
I woudl change the query to this (of the head)

SELECT  Rooms.Room, Rooms.Tables - ISNULL(SUM(TableCheck.UsedTables), 0) AS AvTables, Rooms.Seating, Rooms.Tables - ISNULL(SUM(TableCheck.UsedTables), 0) * Rooms.Seating AS AvSeating
FROM Rooms LEFT JOIN TableCheck ON Rooms.Room = TableCheck.Room
WHERE WHERE ([Event Date] = @evtDate) AND (Cancelled = 0) AND (Rooms.Tables - ISNULL(SUM(TableCheck.UsedTables), 0)>0)
GROUP BY Rooms.Room, Rooms.Tables, Rooms.Seating
0
 

Accepted Solution

by:
rko9911 earned 0 total points
ID: 22778757
Prienst04,

The problem was not in the SQL statement.  I had the datagridview bound to the Rooms table, so it would only bring rows from the room table that were used in the SQL statement (I mentioned I was new to this!).  Unbinding the datagridview fixed that.  But it also brought unwanted columns (which could be hidden) and put them in a bizarre order.  Rearranging the SQL statement did nothing to rearrange the columns.  But this sure helped:

http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridviewcolumn.displayindex.aspx

Although I solved the problem on my own, you were the only person willing to help me, so I am granting you some points.  Thank you!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 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