Solved

calculated cells in datagridview

Posted on 2008-10-20
5
363 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now