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
Room Tables Seating (The Seating is how many to a table)
Room1 10 8
Room2 20 8
Room3 25 4
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.
ISNULL(Rooms.Tables - TableCheck.UsedTables, Rooms.Tables) AS AvTables,
ISNULL(Rooms.Tables-TabCheck.UsedTables, Rooms.Tables)*Rooms.Seating AS AvSeating
FROM (SELECT Room, SUM(Tables) AS UsedTables
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?