parameters with subqueries?

[Anti-Rich]

hi all,

i have the following code

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ds [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
[SIZE=2]sql = [/SIZE][SIZE=2][COLOR=#800000]"SELECT 'Item'=n.Description, "[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]"'Times Picked'= "[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]"("[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]"select count(Qty) from tblWT where ID= '@ID"[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]")"[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]","[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]"'Volume Picked'="[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]"("[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]"select sum(Qty) from tblWT WHERE ID= '@ID'"[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]")"[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#800000]"FROM tblWT w, tblNSN n WHERE n.NSN = '@ID' GROUP BY n.Description ORDER BY 'Times Picked' Asc"[/COLOR][/SIZE]
 
 
[SIZE=2]cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand(sql, cnn)[/SIZE]
[SIZE=2]cmd.Parameters.AddWithValue([/SIZE][SIZE=2][COLOR=#800000]"ID"[/COLOR][/SIZE][SIZE=2], txtID.Text)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sDa [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter(cmd)[/SIZE]
[SIZE=2]sDa.Fill(ds)[/SIZE]
[SIZE=2]dgInfo.DataSource = ds[/SIZE]

for improved readability here is the query i run in SQLME

[SIZE=2][COLOR=#0000ff]select [/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'Item'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2]n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Description[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]'Times Picked'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]select [/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]count[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]Qty[/SIZE][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT [/SIZE][SIZE=2][COLOR=#0000ff]where[/COLOR][/SIZE][SIZE=2] ID[/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'000013388'[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]'Volume Picked'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]select [/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]Qty[/SIZE][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT [/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] ID [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'000013388'[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT w[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] tblID n [/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]ID[/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'000013388' [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]group [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]by[/COLOR][/SIZE][SIZE=2] n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Description [/SIZE][SIZE=2][COLOR=#0000ff]ORDER[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]BY [/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'Times Picked' [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Asc[/COLOR][/SIZE]

i dont understand why it does not return any rows on the database (and i know there are rows matching the query, since when i run the query in sql manager express (with the parameter replaced by a valid id), it works no problem. is it possible that the parameter i defined does not go into the subqueries?

i would greatly appreciate anyone's input

regards
adam

 

[jmcilhinney]

You have put single quotes around your parameters so they are being interpreted as literal text.

 

[Anti-Rich]

hahahaha, wow that was really unbelieveable! sorry about that i dont know why i didnt see that. well... lesson learnt!

cheers ill re run the query and see if i run into any programs

regards
adam

 

[Anti-Rich]

thanks jmc, worked perfectly... sorta

i now have another problem.. but first, the query i am using

[SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff0000]'Item'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2]n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Description[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] 
[/SIZE][SIZE=2][COLOR=#ff0000]'Times Picked'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] 
[/SIZE][SIZE=2][COLOR=#808080](
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff00ff]count[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]Qty[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT
[/SIZE][SIZE=2][COLOR=#808080])
,
[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'Volume Picked'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=
(
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]Qty[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT
[/SIZE][SIZE=2][COLOR=#808080])
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] tblWT w[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] tbLID.n [/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] w[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]ID[/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2]n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]ID [/SIZE][SIZE=2][COLOR=#0000ff]GROUP[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][SIZE=2] n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Description [/SIZE][SIZE=2][COLOR=#0000ff]ORDER[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff0000]'Times Picked'[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Desc[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff][COLOR=black]

[/COLOR][/COLOR][/SIZE]

now the problem im having is for each item that has an ID, i want it to get how many times it has been picked, and how much has been picked. but its returning a count of EVERYTHING and a sum of EVERYTHING, rather than just for that item... shouldn't the "w.Id = n.Id" have eliminated this sort of thing happening? im not very good with more complex queries for sql server.. hell, its probably not even complex i just have no idea...

cheers for your input so far
regards
adam

 

[jmcilhinney]

That's a really odd query. I've never seen SQL syntax like that and it is definitely not standard. The subqueries are not required and are actually what's causing the problem, because you're performing those subqueries before performing any join. I think you'll find that this should work:

SELECT n.Description AS Item, COUNT(w.Qty) AS [Times Picked], SUM(w.Qty) AS [Volume Picked] FROM tblWT w INNER JOIN tblID n ON w.ID = n.ID GROUP BY n.Description ORDER BY [Times Picked] ASC

 

[Anti-Rich]

thankyou so much it worked perfectly! if by the strange syntax you mean the thing like "groupby" etc? it just didnt paste into the post properly and for some reason deletes some spaces... if you are referring to how i did the query itself (with the subqueries)... well like i said im not that great with sql, and although i actually had tried the INNER JOIN clause, i didnt work it properly,

cheers for your input though, much appreciated.

when are subqueries actually appropriate? i had a feeling it was the wrong way to go about it i just didnt know why. do you know of any sites that explain (in detail, preferably) the ins and outs of sql? i think it would be of great benefit for me to learn the more complex stuff now.

cheers again mate
regards
adam

 

[jmcilhinney]

A subquery is appropriate when you need to create a subset of data first, then perform a query on that subset. In your case there is no subset required. You just want every unique Description and the count and sum of each.

Performing a join by specifying multiple tables separated by commas and then using the WHERE clause to link them is considered old style SQL. It is considered standard to explicitly JOIN the tables ON related columns.

By "odd" I meant things like:

'Item' = n.Description

I've never seen that before. It is considered standard to use the AS keyword to specify an alias for a column:

n.Description AS Item

I'd recommend everyone have a copy of "SQL in a Nutshell", although even then they can't give you everything. Some complex cases just take experimentation and experience.

 

[Anti-Rich]

really? thats just the way we were taught... but as i recall our SQL teacher wasnt the brightest spark in the bonfire. it does work though...

yeah ill look into that book, cheers again.

once again your help has been invaluable

regards
adam