griffisblessing
asked on
How do I see if a recordset contains a particular value?
I'm a bit of a quagmire ... I need to find a way to see if a recordset contains a particular value for a field, and I need to be able to do this over and over again. Here's my situation:
On a particular web page (ASP) I'm doing a query on an Access DB to get the field PROPID for a particular USERID. So,
SELECT propId FROM table WHERE userID = x
In the DB table there may be multiple lines for a particular USERID, so the results might look like:
USERID PROPID
123 222
123 333
123 444
Then listed on the web page are all of the possible PROPID's:
222 555
333 666
444 777
What I want to do is make the PROPID displaying on the web page a link IF the current user (USERID) has a record that contains that PROPID. Otherwise, the PROPID on the web page just remains regular text.
Please let me know your thoughts on an answer to this. Thanks.
On a particular web page (ASP) I'm doing a query on an Access DB to get the field PROPID for a particular USERID. So,
SELECT propId FROM table WHERE userID = x
In the DB table there may be multiple lines for a particular USERID, so the results might look like:
USERID PROPID
123 222
123 333
123 444
Then listed on the web page are all of the possible PROPID's:
222 555
333 666
444 777
What I want to do is make the PROPID displaying on the web page a link IF the current user (USERID) has a record that contains that PROPID. Otherwise, the PROPID on the web page just remains regular text.
Please let me know your thoughts on an answer to this. Thanks.
P.S. Let me know if you need an example query and I'll put one together for you.
ASKER
Thanks for the response jcrumble. I'm planning to just put all of the possible PROPIDs statically on the page, then add the HREF tag values if the correct conditions are met. Maybe this isn't possible and I need to rethink my strategy. If you've got something better, then by all mean please share it. For now, I'm thinking of something like this:
<table ...>
<tr>
<td>
<% If conditions are met Then %>
<a href=somepage.asp>PROPID</ a>
<% Else %>
PROPID
<% End If %>
</td>
</tr>
</table>
<table ...>
<tr>
<td>
<% If conditions are met Then %>
<a href=somepage.asp>PROPID</
<% Else %>
PROPID
<% End If %>
</td>
</tr>
</table>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If it were me I would load all your PROPIDs into a "lookup table" in SQL Server. This makes for easier maintenance and a better overall design( plus it makes the same information available for other pages/apps ithout having to maintain each set of information independently in your code). Then use a query something like this one from Northwind (in SQL Server....sorry I don't have Access loaded on this machine):
Select A.ProductID
--, B.ProductID
, case when B.ProductID IS NULL then '0' else B.ProductID end as [Indicator]
from Products A left outer join
(Select Distinct ProductID
from "Order Details"
Where OrderID = '10643') B on
(A.ProductID = B.ProductID)
order by A.ProductID
In your case you would replace the statement
Select Distinct ProductID
from "Order Details"
Where OrderID = '10643' with your query SELECT propId FROM table WHERE userID = x
on the outside query you would hit your new lookup table similar to how I've done it in my query. You could then use the case statement to create an indicator of some kind or simply go with the B.ProductID (B.PROPID in your code) which is commented out in my Example and just check for nulls.
After getting all the information into the result set the ASP code to use should be relatively simple. Pretty much exactly what you have listed in you code. For your conditions you would simply check to see if the values in the result set are NULL (or '0' depending on your approach).
Alternatively you could put the tags directly in your SQL code if you have the information at that time. Something like:
, case when B.ProductID IS NULL then '0' else '<a href=somepage.asp>'+B.Prod uctID+'</a >' end as [Indicator] -- you may have to cast/convert the format but it makes the asp a sinch
With this scenario you just have to display the result set period. the browser should interpret the link and give you the results your looking for. Something else about this design can prove to be nice. If applicable, you could already have the links in your lookup table as a new column so that information is also easier to maintain. If you need dynamic results on the called page based on the user you could either check add a session variable containing the user information to your query on the nect page or build it when you return the query something like:
, case when B.ProductID IS NULL then '0' else '<a href=somepage.asp?UserID=' +C.UserID+ '>'+B.Prod uctID+'</a >' end as [Indicator]
or
, case when B.ProductID IS NULL then '0' else '<a href=somepage.asp?UserID=' +@UserID+' >'+B.Produ ctID+'</a> ' end as [Indicator] -- where @USERID is the passed parameter
Hope this helps and good luck,
Jeff
Select A.ProductID
--, B.ProductID
, case when B.ProductID IS NULL then '0' else B.ProductID end as [Indicator]
from Products A left outer join
(Select Distinct ProductID
from "Order Details"
Where OrderID = '10643') B on
(A.ProductID = B.ProductID)
order by A.ProductID
In your case you would replace the statement
Select Distinct ProductID
from "Order Details"
Where OrderID = '10643' with your query SELECT propId FROM table WHERE userID = x
on the outside query you would hit your new lookup table similar to how I've done it in my query. You could then use the case statement to create an indicator of some kind or simply go with the B.ProductID (B.PROPID in your code) which is commented out in my Example and just check for nulls.
After getting all the information into the result set the ASP code to use should be relatively simple. Pretty much exactly what you have listed in you code. For your conditions you would simply check to see if the values in the result set are NULL (or '0' depending on your approach).
Alternatively you could put the tags directly in your SQL code if you have the information at that time. Something like:
, case when B.ProductID IS NULL then '0' else '<a href=somepage.asp>'+B.Prod
With this scenario you just have to display the result set period. the browser should interpret the link and give you the results your looking for. Something else about this design can prove to be nice. If applicable, you could already have the links in your lookup table as a new column so that information is also easier to maintain. If you need dynamic results on the called page based on the user you could either check add a session variable containing the user information to your query on the nect page or build it when you return the query something like:
, case when B.ProductID IS NULL then '0' else '<a href=somepage.asp?UserID='
or
, case when B.ProductID IS NULL then '0' else '<a href=somepage.asp?UserID='
Hope this helps and good luck,
Jeff
P.S. - Just because I already did it here's the SQL for putting the link directly in your data set with a cast:
Select A.ProductID
--, B.ProductID
, case when B.ProductLink IS NULL then '0'
else '<a href=something.asp>'+B.Pro ductLink+' </a>' end as [Indicator]
from Products A left outer join
(Select Distinct ProductID, cast(ProductID as nvarchar) as ProductLink
from "Order Details"
where OrderID = '10643') B on
(A.ProductID = B.ProductID)
order by A.ProductID
Good Luck.....again,
Jeff
Select A.ProductID
--, B.ProductID
, case when B.ProductLink IS NULL then '0'
else '<a href=something.asp>'+B.Pro
from Products A left outer join
(Select Distinct ProductID, cast(ProductID as nvarchar) as ProductLink
from "Order Details"
where OrderID = '10643') B on
(A.ProductID = B.ProductID)
order by A.ProductID
Good Luck.....again,
Jeff
ASKER
I've accepted the answer from prashantagarw10. That solution was most straight-forward and easiest to implement (in my situation). jcrumble, props go out to you for the thorough SQL explanation. Although I'm not going to use it in this situation, I am going to keep it as a reference tool. Hopefully it can be useful to others who browse this site.
Thanks to all for your input and suggestions.
BTW ... Here's my final code (the relevant pieces at least):
''' Pull Properties for current user
sqlProperties = "SELECT * FROM table WHERE userID = " & Request.Cookies("userID") & "; "
Set rstProperties = Server.CreateObject("ADODB .Recordset ")
rstProperties.Open sqlProperties, cnnCIData, adOpenDynamic ''' Had to open the DB this way so I could scroll through the rowsets
<% rstProperties.MoveFirst
rstProperties.Find "propID = 111"
If Not rstProperties.EOF Then
%>
» <a href=somepage.asp>Prop Name</a><br>
<% Else %>
» <font color=#C0C0C0>Prop Name</font><br>
<% End If %>
Thanks to all for your input and suggestions.
BTW ... Here's my final code (the relevant pieces at least):
''' Pull Properties for current user
sqlProperties = "SELECT * FROM table WHERE userID = " & Request.Cookies("userID") & "; "
Set rstProperties = Server.CreateObject("ADODB
rstProperties.Open sqlProperties, cnnCIData, adOpenDynamic ''' Had to open the DB this way so I could scroll through the rowsets
<% rstProperties.MoveFirst
rstProperties.Find "propID = 111"
If Not rstProperties.EOF Then
%>
» <a href=somepage.asp>Prop Name</a><br>
<% Else %>
» <font color=#C0C0C0>Prop Name</font><br>
<% End If %>
Good Luck,
Jeff