Problem with SqlDataSource using sub-query and date as parameters

I am creating a search page for master detail tables. The search criteria is mainly on the header table. However, there is also one criteria which is in detail table, let said product number.

In my SqlDataSource, I setup the SQL like this.

select fieldA, fieldB, ..., fieldZ from masterTable where (1 = 1)

Then, the additional search criteria is appended to the SqlDataSource select command once the user click the search button. If user wants to search product number, the following will be appended

and exists (select 1 from detailTable where pid = masterTable.id and productNo = @.productNo)

The problem is when I provides both the sub-query criteria and 2 date fields criteria. The page will raise an timeout exception. I don't have any clue on this as I can copy the SQL and run it inside the SQL Server Management Studio. The result come up in a second.

Any suggestion on tackling this problem? Thanks!

hi,

U can avoid this by diffarent options...By above information I can explain like this

1 use UNION

2 Use Primary Key in your every Subquery Query followed by the Search.

3 Use Joins with valid Key Elements.etc

or send the required result columnes and the Table Design

bye

murthy

|||

Hi Murthy,

1. use UNION

I don't know how should I use UNION in master-detail structure. Please give more detail.

2. The primary key is already used in the where clause of the subquery. The column pid means the primary key ID in master table.

3. Use Join

The use of join is not desirable. I have to group the records back together afterward. I just want to search the master table but use detail record as criteria. If I join them without group, the master record will repeat themselves in the result.

Actually, I'm strange about the performance difference by using ADO.NET and SQL Server management studio.

|||

Hi,

OK

Do this Use "#' table with one primary key ID as the Column column in all the condictions

and finally join all the Table with the key Elements..

Ex:

select <Key>,<Search column 1 > into #TableA from <Master table> where <Condition>

select <Key>,<Search column 2 > into #TableB from <Master table> where <Condition>

.....

....

and Finally

select <Search Column1>,<Search Column2>.<Search Column3>,... from #TableA, #TableB, #TableC...where #TableA.Key=#TableB.Key,#TableA.Key=#TableC.key etc

drop all temp table

your result is ready now...

|||

This approach seems making the simple request into a complex one.

Problem with query

I have two tables. One for invoice detail and one for inventory per warehouse. Let's say the one for invoice detail has the following data:

PRODUCT | AMT_SOLD
shirt | 3
pant | 1
shirt | 4
pant | 5

and the inventory per warehouse has the following data:

PRODUCT | WAREHOUSE | QUANTITY
shirt | A1 | 50
shirt | A3 | 30
shirt | A4 | 10
pant | A2 | 25
pant | A3 | 10

I'm trying to make a query that will show me the name of the product, the sum of the quantity sold (from the invoice table) and the sum of the quantity in all of the warehouses, like this:

PRODUCT | SUM1 | SUM2
shirt | 7 | 90
pant | 6 | 35

If I do:
SELECT a.product, SUM(a.amt_sold) as SUM1, SUM(b.quantity) as SUM2
FROM invoice a, inventory b
WHERE a.product = b.product
GROUP BY a.product

I get:

PRODUCT | SUM1 | SUM2
shirt | 21 | 180
pant | 12 | 70

which is obviously not right

any ideas?this..

with a subquery..

select T.product,T.sum1, sum(quantity) as sum2 from
(select product, sum(amt_sold) as sum1 from invoice group by product) as T
inner join inventory as B
on T.product = B.product
group by T.product,T.sum1

try this =)
Ale.