Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

calculated cells in datagridview

Posted on 2008-10-20
5
Medium Priority
?
371 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
[X]
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
  • 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 300 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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

718 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