Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

calculated cells in datagridview

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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