rko9911
asked on
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-TabChe
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-TabCh
I have it listed in my Dataset in the RoomsTableAdapter as both GetDataByAvRooms(@evtDate)
I am filling the Datagridview thusly:
Me.AvRooms.DataSource = RoomsTableAdapter.GetDataB
It is returning Rooms.Room and Rooms.Seating correctly. I can't seem to return the data in the other columns. Can someone help?
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-TabChe
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-TabCh
I have it listed in my Dataset in the RoomsTableAdapter as both GetDataByAvRooms(@evtDate)
I am filling the Datagridview thusly:
Me.AvRooms.DataSource = RoomsTableAdapter.GetDataB
It is returning Rooms.Room and Rooms.Seating correctly. I can't seem to return the data in the other columns. Can someone help?
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?
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Goran