How to pull integers out of tableAdapter into a variable and how to count Bools to insert into another column

Question

This should be simple I just cannot find out the answers easily. At work I have 9 Aircards. I am responsible for checking them out and keeping track of them. I wanted to make a simple C# application with a Express database. 2 Tables - one is the Usage table ( totalCards cardsCheckedOut Date peopleTurnedAway) one is the Card table (cardName (#1 #2 etc.) checkedOut bool userName datesNeeded) OKAY - TWO THINGS --- HOW do I pull integers such as totalCards cardsCheckedOut from my TableAdapter and plop them into a variable so that I can perform calculations on my form? ** I want to have simple calcs. like CardsAvail% Usage% etc. AND HOW can I FILL the Column cardsCheckedOut in the Usage table by Adding up the total of the checkedOut bool Column in the Card table? These seem like simple things but have not been able to find the answers....help?

John Penn · Answer

SQL Server doesn't have a boolean datatype. The nearest equivalent is the bit datatype where 1 is true and 0 is false. If this is your table DDL: CREATE TABLE Card (cardname varchar(50) cardtype varchar(50) checkedOut bit) You can get the count of each cardtype that's checked out by using a CASE statement within a SUM like this: SELECT cardtype sum(case when checkedout = 1 then 1 else 0 end) as NumberCheckedOut from Card group by cardtype

Jan Montano · Answer

Hi Todd Is the Usage Table just a summary for the Card Table? Does the Usage Table only contain one record? wherein TotalCards = # of Cards in card Table and cardsCheckedout = # of Cards in Card Table which is checked out? If that's the case I think we could just drop the Usage Table and work directly with the card table. Are you familiar with SqlConnection SqlCommand and other Sql Objects for c#? What have you come up with so far?