calculated cells in datagridview

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?
rko9911Asked:
Who is Participating?
 
rko9911Connect With a Mentor Author Commented:
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
 
Priest04Commented:
Can you show how would a resulting table of data look like?

Goran
0
 
Priest04Commented:
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
 
rko9911Author Commented:
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
 
Priest04Connect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.