How do I append a row to SQL results?

I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:

SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

So the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql.

So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.

You need to do this kind of thing at the front end.

|||

You can do this with a UNION. Something like this:

"SELECT 0, 'Select...'
UNION
SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

The number of fields in the first SELECT has to match the second, so adjust that as needed. And I've put in line breaks here to make it clearer what is happening, but it is all one string in what I assume is VB.NET. Adjust as needed for your code.

Also, you might need to use UNION ALL instead of just UNION, depending on your data.

Put the 'Select...' in the field location where the data used to display the item in the dropdown list.

Make sense?

Don

|||

bbaxter:

I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:

SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

So the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql.

So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.

Try this:

1-- Get The Result Set2SELECT Col1, Col2, Col33From MyTable45UNION-- to append67SELECT'MyRowValueForCol1','MyRowValueForCol2','MyRowValueForCol3'-- columns value of the row you want to append to the result set8FROM MyRowTable-- if you are geeting this row from table OR you can ignor this line if the values are hard coded9

Good luck.

|||

You can have the resultset modified using one of the above techniques, that should work.

You can also turn on append databound items and turn off viewstate (Viewstate will prevent the multiple additions as well as cut down the size of your page and the response you get back).

Or you can just add this type of code:

Sub ddlMyDropdown_databound(sender as object,e as object) handles ddlMydropdown.databound

ctype(sender,dropdown).items.insert(0,new listitem("Select...",""))

end sub

sql

How could I don this?

Here is my query, it works fine. Now, I would like to
execute this query for every item on the itemprice.item
column. I could create another Select query that would
retun all items in the item column of the itemprice table,
but I don't know how to make the first query (the one
below) to go thru every item on that list. Can someone
give some help? TIA
SELECT column1 AS Products, SUM(Total) AS Total FROM
(SELECT t1.column1, t1.column3*t2.column5 AS Total
FROM products AS t1 INNER JOIN itemprice AS t2
ON t1.productid=t2.productid
WHERE t2.item='00152') AS table
GROUP BY column1Try,
SELECT
t1.column1,
t2.item,
sum(t1.column3 * t2.column5) AS Total
FROM
products AS t1
INNER JOIN
itemprice AS t2
ON t1.productid=t2.productid
group by
t1.column1,
t2.item
go
AMB
"JLong" wrote:

> Here is my query, it works fine. Now, I would like to
> execute this query for every item on the itemprice.item
> column. I could create another Select query that would
> retun all items in the item column of the itemprice table,
> but I don't know how to make the first query (the one
> below) to go thru every item on that list. Can someone
> give some help? TIA
> SELECT column1 AS Products, SUM(Total) AS Total FROM
> (SELECT t1.column1, t1.column3*t2.column5 AS Total
> FROM products AS t1 INNER JOIN itemprice AS t2
> ON t1.productid=t2.productid
> WHERE t2.item='00152') AS table
> GROUP BY column1
>|||Thanks for your suggestion. I noticed that the only thing
I had to do was to remove the condition, so the query it
executed for every item not just the one on the condition.

>--Original Message--
>Try,
>SELECT
> t1.column1,
> t2.item,
> sum(t1.column3 * t2.column5) AS Total
>FROM
> products AS t1
> INNER JOIN
> itemprice AS t2
> ON t1.productid=t2.productid
>group by
> t1.column1,
> t2.item
>go
>
>AMB
>
>"JLong" wrote:
>
table,
>.
>sql

how can sqldataobject error be traped

My database has two tables that are joined by a relation (Item category and the item). My relation will not permit the deletion of item categories if there are item referencing it.

How can that error be trapped to then show a message such as. Cannot delete item category that has item associated with it?

This is a asp.net web form with VB

this is the error I get

Server Error in '/TWGSalesInfo' Application.

---

The DELETE statement conflicted with the REFERENCE constraint "FK_PurchaseVendors_PurchaseItems". The conflict occurred in database "CustSegmentation", table "dbo.PurchaseVendors", column 'VendorPurchaseItemID'.
The statement has been terminated.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_PurchaseVendors_PurchaseItems". The conflict occurred in database "CustSegmentation", table "dbo.PurchaseVendors", column 'VendorPurchaseItemID'.
The statement has been terminated.

Source Error:

Line 1904: }Line 1905: try {Line 1906: int returnValue = this.Adapter.DeleteCommand.ExecuteNonQuery();Line 1907: return returnValue;Line 1908: }

Thanks

Dj

Simply add a try/catch block

try

{

int returnValue = this.Adapter.DeleteCommand.ExecuteNonQuery();

return returnValue

}

catch(SqlException)

{

/// throw your custom exception here

throw new MyCustomDeleteException("Unable to delete record...");

}

and in the upper level catch only exceptions of your custom type

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.