How can you stop and cycle through a column(s) of data?

Ok, at this point I have the reader reading the tables data in a loop while it's not empty. During the gathering of each row of data, I was wondering if it was possible to do a next row once I've reached a certain column. The main users table has just the one user, but it's relationship table has a couple family members. I was hoping someone could show me how to make it so that the one user and all his related family members will print out to a label.
while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();

if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();
}
If I grab the Relation table data again, it's not cycled to the next relative. I was hoping that it would, but it's not. So I'm wondering if there was something that could be added to the second set.
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString();

Thank you in advance.I tried this:

while (reader.Read())
{
string usr = reader["UserName"].ToString();
usr = usr.TrimEnd();
string pss = reader["Password"].ToString();
pss = pss.TrimEnd();
//take the read data from the reader and display it for testing purposes
if (usrNmeLbl.Text == usr)
{
if (psswrdLbl.Text == pss)
{
//read the column from the reader and cast it to String as some may contain null values
usrNmeLbl.Text = reader["FirstName"].ToString() + " ";
psswrdLbl.Text = reader["LastName"].ToString() + "<br />";
psswrdLbl.Text += "Place of Birth: " + reader["BirthPlace"].ToString() + "<br />";
psswrdLbl.Text += "<img src=" + reader["Photo"].ToString() + " />" + "<br />";
Label4.Text = "Your relatives: " + "<br />";
while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString() + "<br />";
}
}

But for some reason the relationships print out multiple times rather than just until there are no more relatives in the database.|||You will most likely get a usable response by posting this question in one of the .NET forums.|||

Arnie Rowland wrote:

You will most likely get a usable response by posting this question in one of the .NET forums.

Ok, this is where I am really confused. Why does the reading of data loop all over again from the beginning? I would think that when it read the last row that then it would just exit out of the loop. Just like what's happening with the first loop with while(reader.Read()).

while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString() + reader["RelativeLN"].ToString() + "<br />";
}

Ok, thank you. I will try.

How can you sort query filenames within a ssmssqlproj project file?

Subject says it all. There appears to be no way, which is ridiculous. Surely I am missing something that someone can easily point out. Thanks, Vic.

Just edit the ssmssqlproj file.

The file for my project (SQL Main) is located in "My Documents\SQL Server Management Studio\Projects\SQL Main\SQL Main\SQL Main.ssmssqlproj". Its just an xml file. Change the following line

<LogicalFolder Name="Queries" Type="0" Sorted="true">

to

<LogicalFolder Name="Queries" Type="0" Sorted="false">

It will revert back to true so you need to repeat this if you make changes. THere is probably a better way

|||I believe as of now only way is to edit hte XML in this case, as reported on the Connect site too.

How Can I Use a SubQuery to Sum a Column "Quantity" where subqry.item=mainqry.it

More to the point, how do I get the results to show one row per Item? I thought if I got all the rows evaluate the same, I would be able to use DISTINCT to return one row per item.

Actually, I thought if I did a RIGHT JOIN that for each instance (record) of an item in my ItemInventory table (in which all rows are unique) with my PurchaseOrderLine table (in which there can be multiple instances of an item) that I would get one lie, with the quantities on PO's that are open and received summed. Instead, I get multiple lines that all appear to have correct data but that are duplicates.

Here is my current query

SELECT ItemInventory.Name AS [Product Number],
ItemInventory.SalesDesc AS Description,
ItemInventory.PrefVendorRefFullName AS Vendor,
ItemInventory.QuantityOnHand AS Available,
ItemInventory.ReorderPoint AS Minimum,
(ItemInventory.QuantityOnHand-ItemInventory.ReorderPoint) AS Under,

(SELECT SUM(PurchaseOrderLine.PurchaseOrderLineQuantity)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS [On Order],

(SELECT SUM
(PurchaseOrderLine.PurchaseOrderLineReceivedQuanti ty)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS Recvd

PurchaseOrderLine.TxnDate AS [PO Date],
ItemInventory.PurchaseCost AS Price

FROM PurchaseOrderLine RIGHT JOIN ItemInventory ON PurchaseOrderLine.PurchaseOrderLineItemRefListID=I temInventory.ListID;

Here are my results:

"Large Box","Large Packing Box",,455,100,355,,,2
"Medium Box","Medium cardboard packing box",,55,100,-45,,,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Small Box","Small cardboard packing box",,55,100,-45,,,1
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Will Excel Delete This Item",,,100,,,,,0

If I use DISTINCT, like I thought I could, MS ACCESS blows up on me. I don't know if it is my SQL or MS ACCESS (or if my data source has issues).

If I can verify my SQL is valid, then I'll know if I need to look elsewhere for a resolution to my problem.

TIA

Danielyou need to develop a query like this in two steps

the first step is to ensure that the join is working correctly, and the second step is to add GROUP BY and aggregate functions like SUM (i think your subqueries in the SELECT clause are wrong, but let's do that later after we fix the join)

for your first step, you need to understand that your RIGHT JOIN will return each ItemInventory with or without matching rows from PurchaseOrderLine

in other words, all products

and for any product that appears on any PurchaseOrderLine, you will get a row for each such occurrence, and that product will appear in the results multiple times

is that what you want? if not, we need to fix the join before you start thinking about SUMs|||I need to look at every item in the InventoryItem table.

For those items where there are purchase orders in the PurchaseOrderLine table, I need to sum the total number on all open purchase orders, minus the number already received against those open purchase orders (they receive partial fills on orders).

In the end, I need somethig that looks like this:

Name Description Vendor #Available Reorder_Point Max_On_Hand #Under Reorder_Point #On_Order #To_Reorder Cost_Per_Unit Cost_to_Reorder Total_Cost_to_Reorder

#Under = Max_On_Hand - #Avaialble (if #Available < Max_On_Hand)

We reorder an item if #Under < 0

#To_Reorder = #Under - #On_Order (we never want the total headed for the shelves to be > Max_On_Hand|||select II.Name AS [Product Number]
, II.SalesDesc AS Description
, II.PrefVendorRefFullName AS Vendor
, II.QuantityOnHand AS Available
, II.ReorderPoint AS Minimum
, (II.QuantityOnHand
-II.ReorderPoint) AS Under
, SUM(POL.PurchaseOrderLineQuantity)
AS [On Order]
, SUM(POL.PurchaseOrderLineReceivedQuantity)
AS Recvd
, MAX(POL.TxnDate) AS [PO Date]
, II.PurchaseCost AS Price
from ItemInventory as II
left outer
join PurchaseOrderLine as POL
ON POL.PurchaseOrderLineItemRefListID
= II.ListID
group
by II.Name
, II.SalesDesc
, II.PrefVendorRefFullName
, II.QuantityOnHand
, II.ReorderPoint
, II.PurchaseCost|||I will try this.

I may ask what makes it tick, but I think I get it ... I will play with it before I ask again tho.