Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

APB for ASP!! 2

Status
Not open for further replies.
danblack30

danblack30

MIS
Aug 1, 2003
85
US
What I'm trying do is populate a drop down list with pagelinks that aren't already assigned to a user


I have three tables USERACCESS (userid, username, userpassword, userlevel) , LINKDETAIL (id, userid, pagelinkdetail) , and PAGELINKS (pagelinkid, pagelinks).

USERACCESS userid(1) inner join LINKDETAIL userid(many)
PAGELINK pagelinkid(1) inner join LINKDETAIL pagelinkiddetail(many)

There will be several different userid's, with access to many pagelinks.

I have the recordset not equal to the userid and that works great until I add a user with access to the same link.

For example;
user 1 has access to pages abce
user 2 has access to pages def
the recordset throws out all records that equal user1 but the page "e" is still in the dropdown when user1 refreshes the page cause its also available to user2. Only pages D, and F should be available.


here's the code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/conntesttsrserv.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("UserID")<> "") Then
Recordset1__MMColParam = Request.QueryString("UserID")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conntesttsrserv_STRING
Recordset1.Source = "SELECT useraccess.UserID, useraccess.Username, linkdetail.pagelinkIDdetail, pagelinks.pagelinks FROM useraccess INNER JOIN (pagelinks INNER JOIN linkdetail ON pagelinks.pagelinkID = linkdetail.pagelinkIDdetail) ON useraccess.UserID = linkdetail.userID WHERE useraccess.UserID<>" + Replace(Recordset1__MMColParam, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<table width="100%" height="82" border="0">
<tr>
<td><form name="form1" method="post" action="">
<p>
Username
<%=(Recordset1.Fields.Item("Username").Value)%>
</p>
<p> pagelinks
<select name="pagelinkid" id="pagelinkid">
<%
While (NOT Recordset1.EOF)
%>
<option value="<%=(Recordset1.Fields.Item("pagelinkIDdetail").Value)%>"><%=(Recordset1.Fields.Item("pagelinks").Value)%></option>
<%
Recordset1.MoveNext()
Wend
If (Recordset1.CursorType > 0) Then
Recordset1.MoveFirst
Else
Recordset1.Requery
End If
%>
</select>
</p>
<p>
<input type="submit" name="Submit" value="Add pagelink">
</p>
</form></td>
</tr>
</table>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Is there any way this could work? Recode or redesign the database?
I thought maybe have two recordsets one that equals the pagelinks and another that doesn't equal the userid and doesn't equal anything in the first recordset, but I can't seem to get that to work.

Any help is greatly appreciated.
Thanks,
Dan
 
Sort by date Sort by votes
It seems like ASP is a poor solution to the stated problem... have you tried instead writing a better SQL Query?

A single recordset containing only the desired rows seems a lot better that multiple recordsets combined with script logic for selecting the proper rows.
 
Upvote 0 Downvote
I tried changing the relationships without success. Is there a way to have the query not equal a value in the userid column and at the same time remove any records from the pagelinkiddetail column that equals any values removed by the first half of the query?

Any suggestions?
 
Upvote 0 Downvote
can you post some sample data from your tables and the kind of result data are you looking...then we can come up with the query...

-DNG
 
Upvote 0 Downvote
I have three tables USERACCESS (userid, username, userpassword, userlevel) , LINKDETAIL (id, userid, pagelinkdetail) , and PAGELINKS (pagelinkid, pagelinks).

USERACCESS userid(1) inner join LINKDETAIL userid(many)
PAGELINK pagelinkid(1) inner join LINKDETAIL pagelinkiddetail(many)

Useraccess
UserID Username Userpassword Userlevel
1 Dan Dan 1
3 Jeff Jeff 1

LinkDetail
ID UserID Pagelinkiddetail
1 1 1
2 1 2
3 1 3
5 3 3
6 3 4
7 1 5

Pagelinks
PagelinkdID Pagelinks
1 tsr
2 cerf
3 confer
4 users
5 test

username [text field]
pagelinks [drop down]
[add link submit]

is that what you needed?
 
Upvote 0 Downvote
It looks like your existing SQL already attempts to do what you describe so we'll need more information about the problem with your existing SQL? Does it throw an error? Return 0 records? What?

PS: I also noticed that the 3rd field in the SELECT list is somewhat different from the fields listed at the top of your first post.
[tt]
SELECT
useraccess.UserID,
useraccess.Username,
linkdetail.pagelink[red]ID[/red]detail,
pagelinks.pagelinks

FROM
useraccess
INNER JOIN (pagelinks
INNER JOIN linkdetail
ON pagelinks.pagelinkID = linkdetail.pagelinkIDdetail)
ON useraccess.UserID = linkdetail.userID

WHERE
useraccess.UserID <> " + Replace(Recordset1__MMColParam, "'", "''")
[/tt]
 
Upvote 0 Downvote
I missed the "ID" in the first post, but you got that.

The code works and returns users and confer to the menu.

Ok... When the userid =1 the query removes those lines so the drop down shows the users and confer links. What I need is for the confer link to also be removed since that is already a link the user has been assigned.(reference table linkdetail ID's 3 and 5)


This drop down is supposed to add links the user has not already been assigned, by highlighting a link and hitting the submit button. (insert behavior hasn't been added yet.)

Is that what you were askin for? Or did I just make it worse.

Thanks
Dan
 
Upvote 0 Downvote
Dan,

Since you are working with Access, have you copied your SQL statement to an Access query to ensure that it returns the values you are looking for? Of course, you will need to replace the little piece of code with a real value to test it.

Did you even look at the recommendations I made in your original post? Specifically, you should stop using the "while... wend" statement, use "&" instead of "+" to concatenate, use response.write instead of "<%= %>" in the middle of your HTML, and place all code AFTER the DOCTYPE statement. I understand that you are relying on Dreamweaver, but trusting a WYSIWYG program is not always the best thing when working with ASP. Heck, it is generating code using a depricated method!

If your SQL statement is valid, then this should work:
Code: 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/html4/loose.dtd">[/URL]
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <title>Untitled Document</title>
</head>

<body>
<!--#include file="connections/conntesttsrserv.asp" -->
<%
  Dim Recordset1_MMColParam
  Dim Recordset1
  Dim Recordset1_numRows

  Recordset1_MMColParam = Request.Querystring("UserID")
  If Recordset_MMColParam = "" Then
    RecordSetMMColParam = "1"
  End If

  Set Recordset1 = Server.CreateObject("ADODB.Recordset")
  Recordset1.ActiveConnection = MM_Conntesttserv_STRING
  Recordset1.Source = "SELECT useraccess.UserID, useraccess.Username," & _ 
                      "linkdetail.pagelinkIDdetail, pagelinks.pagelinks" & _
                      "FROM useraccess INNER JOIN (pagelinks INNER JOIN" & _
                      "linkdetail ON pagelinks.pagelinkID =" & _
                      "linkdetail.pagelinkIDdetail) ON useraccess.UserID = " & _
                      "linkdetail.userID  WHERE useraccess.UserID<> " & _
                      Replace(Recordset1__MMColParam, "'", "''") & ";"
  Recordset1.CursorType = 0
  Recordset1.CursorLocation = 2
  Recordset1.LockType = 1
%>

  <table width="100%" height="82"  border="0">
    <tr>
      <td>
        <form name="form1" method="post" action="">
<%
  Response.Write("<p>Username " & Recordset1("Username") & "</p>")
  Response.Write("<p>PageLinks <select name='pagelinkID' id='pagelinkID'>")
  Do While NOT Recordset1.EOF
    Response.Write("<option value='" & Recordset1("pagelinkIDdetail") & _
                   "'>" & Recordset1("pagelinks") & "</option>")
    Recordset1.MoveNext
  Loop
  Response.Write("</select></p>")
  Response.Write("<p><input type='submit' name='submit' value='Add PageLink'>")
  Response.Write("</p>")
%>
        </form>
      </td>
    </tr>
  </table>
</body>
</html>

* Note that the long lines are broken using "& _", but really should be kept together for easier debugging.

-Brian-
I'm not an actor, but I play one on TV.
 
Upvote 0 Downvote
I did check out the code, and thanks by the way, but even rewriting the page as you have it wouldn't solve the main issue of not being able to remove records that meet the userid criteria and also duplicate pagelinkid details that the user is already assigned.

I started programming in notepad and enjoyed it but dreamweaver lets me speed up the process , even though the code is somewhat screwey.(screwy?) I'll write it in hyrogliphics if it makes it work.
 
Upvote 0 Downvote
Your first criteria is that UserID is not the one submitted...

... and the second criteria is that none of the rows will be ones where the "link detail" is the same as a "link detail" for the submitted UserID.

Is that right?

If so you could use a subquery to describe the second criteria:

[tt]
SELECT
useraccess.UserID,
useraccess.Username,
linkdetail.pagelink[RED]ID[/RED]detail,
pagelinks.pagelinks

FROM
useraccess
INNER JOIN (pagelinks
INNER JOIN linkdetail
ON pagelinks.pagelinkID = linkdetail.pagelinkIDdetail)
ON useraccess.UserID = linkdetail.userID

WHERE
useraccess.UserID <> " + Replace(Recordset1__MMColParam, "'", "''") [HIGHLIGHT]AND
linkdetail.pagelinkIDdetail NOT IN (SELECT Distinct pagelink[RED]ID[/RED]detail
FROM linkdetail
WHERE useraccess.UserID <> " + Replace(Recordset1__MMColParam, "'", "''")
)[/HIGHLIGHT]
[/tt]
 
Upvote 0 Downvote
I have this now;

SELECT
useraccess.UserID,
useraccess.Username,
linkdetail.pagelinkIDdetail,
pagelinks.pagelinks

FROM
useraccess
INNER JOIN (pagelinks
INNER JOIN linkdetail
ON pagelinks.pagelinkID = linkdetail.pagelinkIDdetail) ON useraccess.UserID = linkdetail.userID

WHERE useraccess.UserID<>" + Replace(Recordset2__MMColParam, "'", "''") + " AND
linkdetail.pagelinkIDdetail NOT IN (SELECT Distinct pagelinkIDdetail
FROM linkdetail
WHERE useraccess.UserID<>" + Replace(Recordset2__MMColParam, "'", "''") + ") "

there are no errors on the page but there are no records returned either.


Subquery's... I had no Idea that was available. A whole new world of opportunities just opened up. Thanks Sheco!

 
Upvote 0 Downvote
lets break the query...

SELECT pagelinkIDdetail
FROM linkdetail
WHERE useraccess.UserID = " + Replace(Recordset2__MMColParam, "'", "''") + ") "

gives all the links that are already present for the current user...

SELECT pagelinkID from Pagelinks WHERE pagelinkID not In (
SELECT pagelinkIDdetail
FROM linkdetail
WHERE useraccess.UserID = " + Replace(Recordset2__MMColParam, "'", "''") + ") "
)

gives you the list of links that user doesnt have current and if he/she wishes can add...

did that make sense...

-DNG
 
Upvote 0 Downvote
Yes makes perfect sense, thanks for breaking it down for me.
I pulled the info from 1 table instead of 2, hope thats ok.

Here it is.

SELECT pagelinkIDdetail
FROM linkdetail
WHERE pagelinkIDdetail NOT IN
(SELECT linkdetail.pagelinkIDdetail
FROM linkdetail
WHERE linkdetail.userID=" + Replace(Recordset5__MMColParam, "'", "''") + ")"

Works great, much thanks to Sheco and DotNetGnat!!
 
Upvote 0 Downvote
maybe I'm just having a rough week...but how do I put in a vote for Sheco and DotNetGnat?
 
Upvote 0 Downvote
glad it worked for you...just click on the link that says

"thanks Sheco/DotNetGnat for this valuable post"

-DNG
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
847
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
863
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
802
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
2K
GriffMG
GriffMG
Swi
  • Locked
  • Question
JQUERY AJAX POST and how to receive data via Classic ASP
Replies
0
Views
902
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top