Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fonzie
Starting Member
31 Posts |
 Posted - 2006-09-29 : 11:01:37
|
| In my VB asp page I have this line"SELECT distinct office FROM csulog6 where nsn like" + "'51%' order by office" and I get this errorA field or property with the name 'nsn' was not found on the selected data source.However "SELECT distinct office,nsn FROM csulog6 where nsn like" + "'51%' order by office" does workPS, I am using VFPOLEDB to access a microsoft visual Foxpro table |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-29 : 13:15:38
|
| It sounds like you're trying to read the nsn column value after you execute that query, so the query has to include the nsn column. You said it works when you include it, so if that works, what's the problem? |
 |
|
|
fonzie
Starting Member
31 Posts |
Posted - 2006-09-29 : 14:37:00
|
| Because if I include nsn as well, I no longer get a list of offices unique. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-29 : 17:43:48
|
Well then think about what you're trying to do - you want the nsn in your app, you get an error without the nsn. But, you also want a unique list of offices. Your data obviously doesn't have a unique nsn for each office, so if you ask for office and nsn you get each office with each different nsn. You could get offices with an nsn with this querySELECT office, min(nsn) as nsn FROM csulog6 where nsn like" + "'51%' ordergroup by office BUT realise that now you are only getting one of the nsn's for each office, the others are all being ignored (I chose the minimum one, but you could choose the maximum one by changing min to max). Does that make sense in your app though? |
|
|
|
fonzie
Starting Member
31 Posts |
Posted - 2006-10-03 : 07:43:30
|
| YES. That works and results in what I was after. I added a "group by office" in there as well. Thanks. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-03 : 13:24:31
|
quote:
Originally posted by fonzie
YES. That works and results in what I was after. I added a "group by office" in there as well. Thanks.
Correct, sorry, I meant to say "group by" instead of "order by". |
|
|
|
|
|
|
|
|
simple sql query not working