Help with Recordset Please...
Hi,
I'm using ASP and Access.
Here is my recordset:
<?php
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_SchoolDatabase03_STRING
Recordset1.Source = "SELECT * FROM Clipart_images WHERE PersonalGroup = '" + Replace(Recordset1__MMColParam, "'", "''") + "' ORDER BY ImageTitle ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
?>"WHERE PersonalGroup" filters what the user search for (URL) but i also want to make it show only the users content (MM_Username) but i cant seem to do both. I can filter just Session (MM_Username) or filter by "Personal Group" but not both.
Does anyone no how?
Cheers
chrishirst posted this at 09:11 — 29th May 2005.
He has: 379 posts
Joined: Apr 2005
use the sql AND modifier.
"SELECT * FROM Clipart_images WHERE PersonalGroup = '" & Replace(Recordset1__MMColParam, "'", "''") & "'" & " AND [username column] = '" & Session (MM_Username) & "' ORDER BY ImageTitle ASC"'Chris
Indifference will be the downfall of mankind, but who cares?
Venue Capacity Monitoring
Code Samples
chrishirst posted this at 09:18 — 29th May 2005.
He has: 379 posts
Joined: Apr 2005
BTW with Access try to avoid using the "*" to select columns from the table. Even if you require all the columns use a comma seperated field list instead.
This will increase the speed of the queries. Access is slow (relatively speaking) anyway.
so;
SELECT field1,field2,field3 FROM table WHERE ...'this is even more important if you are only using a few fields, it is pointless and time consuming to return a full recordset when only part of it is needed.
Chris
Indifference will be the downfall of mankind, but who cares?
Venue Capacity Monitoring
Code Samples
dhotchin posted this at 09:30 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
Cheers for that..... I will give it a go
dhotchin posted this at 09:39 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
<?php
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_SchoolDatabase03_STRING
Recordset1.Source = "SELECT * FROM Clipart_images WHERE PersonalGroup = '" & Replace(Recordset1__MMColParam, "'", "''") & "'" & " AND [Owner] = '" & Session (MM_Username) & "' ORDER BY ImageTitle ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
?>
Is the new code that i am using. It is not giving me any error (which is good) BUT, it is not showing any data which IS in the database. for example it should be showing pictures which are in a group, but only the users images. for example they could be more the one user who has a group/folder called "Test Group". Before it was showing everones pictures in the "Test Group", but i only want the users pictures to show.
If that makes sence?
dhotchin posted this at 10:15 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
This part was missing:
<?phpDim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("PersonalGroup") <> "") Then
Recordset1__MMColParam = Request.QueryString("PersonalGroup")
End If
?>
Is it needed? and when it is added i get a error:
Microsoft VBScript compilation error '800a0411'
Name redefined
/ClipArt/Images/PersonalClipArt_GroupList.asp, line 35
Dim Recordset1__MMColParam
----^
Abhishek Reddy posted this at 10:33 — 29th May 2005.
He has: 3,348 posts
Joined: Jul 2001
Been a while since I worked with ASP. Paste or link to your entire code. I'm not sure how your bits of code are arranged. Use [ code ] tags in your post so we can track lines too.
Try printing (Response.Write?) Recordset1.Source. If it prints the SQL string, I think you'll find there's a space missing somewhere. That could cause problems. Also, you don't need the [square brackets] around column names. chrishirst was using that to signify that bit of text needed to be replaced, I think.
dhotchin posted this at 10:44 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../../Connections/SchoolDatabase03.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("PersonalGroup") <> "") Then
Recordset1__MMColParam = Request.QueryString("PersonalGroup")
End If
%>
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers=""
MM_authFailedURL="../../MemberOnly.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
If (true Or CStr(Session("MM_UserAuthorization"))="") Or _
(InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
MM_grantAccess = true
End If
End If
If Not MM_grantAccess Then
MM_qsChar = "?"
If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
MM_referrer = Request.ServerVariables("URL")
if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
Response.Redirect(MM_authFailedURL)
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_SchoolDatabase03_STRING
Recordset1.Source = "SELECT * FROM Clipart_images WHERE PersonalGroup = '" & Replace(Recordset1__MMColParam, "'", "''") & "'" & " AND Owner = '" & Session (MM_Username) & "' ORDER BY ImageTitle ASC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Sounds__MMColParam
Sounds__MMColParam = "1"
If (Request.QueryString("PersonalGroup") <> "") Then
Sounds__MMColParam = Request.QueryString("PersonalGroup")
End If
%>
<%
Dim Sounds
Dim Sounds_numRows
Set Sounds = Server.CreateObject("ADODB.Recordset")
Sounds.ActiveConnection = MM_SchoolDatabase03_STRING
Sounds.Source = "SELECT * FROM Clipart_sounds WHERE PersonalGroup = '" + Replace(Sounds__MMColParam, "'", "''") + "' ORDER BY SoundTitle ASC"
Sounds.CursorType = 0
Sounds.CursorLocation = 2
Sounds.LockType = 1
Sounds.Open()
Sounds_numRows = 0
%>
<%
Dim GroupList__MMColParam
GroupList__MMColParam = "1"
If (Session("mm_username") <> "") Then
GroupList__MMColParam = Session("mm_username")
End If
%>
<%
Dim GroupList
Dim GroupList_numRows
Set GroupList = Server.CreateObject("ADODB.Recordset")
GroupList.ActiveConnection = MM_SchoolDatabase03_STRING
GroupList.Source = "SELECT * FROM PersonalGroups WHERE Username = '" + Replace(GroupList__MMColParam, "'", "''") + "' ORDER BY PersonalGroupTitle ASC"
GroupList.CursorType = 0
GroupList.CursorLocation = 2
GroupList.LockType = 1
GroupList.Open()
GroupList_numRows = 0
%>
<%
Dim SharedList
Dim SharedList_numRows
Set SharedList = Server.CreateObject("ADODB.Recordset")
SharedList.ActiveConnection = MM_SchoolDatabase03_STRING
SharedList.Source = "SELECT * FROM SharedGroups ORDER BY SharedGroupTitle ASC"
SharedList.CursorType = 0
SharedList.CursorLocation = 2
SharedList.LockType = 1
SharedList.Open()
SharedList_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
Dim MM_paramName
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
Dim MM_keepNone
Dim MM_keepURL
Dim MM_keepForm
Dim MM_keepBoth
Dim MM_removeList
Dim MM_item
Dim MM_nextItem
' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then
MM_removeList = MM_removeList & "&" & MM_paramName & "="
End If
MM_keepURL=""
MM_keepForm=""
MM_keepBoth=""
MM_keepNone=""
' add the URL parameters to the MM_keepURL string
For Each MM_item In Request.QueryString
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))
End If
Next
' add the Form variables to the MM_keepForm string
For Each MM_item In Request.Form
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))
End If
Next
' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
If (MM_keepBoth <> "") Then
MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
End If
If (MM_keepURL <> "") Then
MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
End If
If (MM_keepForm <> "") Then
MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
End If
' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>
<html><!-- InstanceBegin template="/Templates/PlainTemplate.dwt.asp" codeOutsideHTMLIsLocked="false" -->
<head>
<!-- InstanceBeginEditable name="doctitle" -->
<title>Schools Intranet</title>
<!-- InstanceEndEditable --><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
-->
</style>
<style type="text/css">
<!--
a:hover {
color: #CC33FF;
}
-->
</style>
<style type="text/css">
<!--
-->
</style>
<meta name="Description" content="Welcome to Spear Webdesign by Darren Hotchin">
<meta name="Keywords" content="spear, spears, webdesign, webdesigning, free hosting, free domain name, video, weddings, web, internet, free,ripon, rippon, north, north yorkshire, yorkshire, Darren, Darren Hotchin, warrengill, andy jepson">
<link href="../../CSS.css" rel="stylesheet" type="text/css">
<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>
<body bgcolor="#CCCCCC">
<table width="730" height="101" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="#FFFFFF">
<tr>
<td width="20" height="20"><img src="../../Images/graycorners/3.jpg" width="20" height="20"></td>
<td bgcolor="#FFFFFF"> </td>
<td width="20"><img src="../../Images/graycorners/4.jpg" width="20" height="20"></td>
</tr>
<tr>
<td height="20" bgcolor="#FFFFFF"> </td>
<td valign="top" bgcolor="#FFFFFF">
<div align="left">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="56" valign="middle" background="../../Images/HeaderLinks01.jpg"><br> <table width="71%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="28%"><div align="center" class="MenuLinks2"><a href="../../Index.asp" class="MenuLinks2">Home</a></div></td>
<td width="21%"><div align="center" class="MenuLinks2"></div></td>
<td width="32%"><div align="center" class="MenuLinks2"></div></td>
<td width="19%"><div align="center" class="MenuLinks2"></div></td>
</tr>
</table>
</td>
</tr>
<tr>
<td valign="middle"><!-- InstanceBeginEditable name="Border" --><!-- InstanceEndEditable --></td>
</tr>
<tr>
<td valign="middle"><!-- InstanceBeginEditable name="MainContent" -->
<table width="100%" border="0">
<tr>
<td colspan="2"><span class="MenuLinks">Your Clip Art Manager </span></td>
</tr>
<tr>
<td width="56%"><span class="AdvertList01">To Save: Right Click,
Save Target As</span></td>
<td width="44%"><div align="right" class="MenuLinks">
<div align="center"><a href="uploadTester.asp" class="MenuLinks">Add
New Image</a> | <a href="../Sounds/uploadTester.asp" class="MenuLinks">Add New Sound</a> </div>
</div></td>
</tr>
<tr>
<td colspan="2"><table width="100%" border="1" align="center">
<tr bordercolor="#D6D38C">
<td width="338"><form action="PersonalClipArt_GroupList.asp" method="get" name="form1" class="MenuLinks">
<p align="center">Your Personal Groups
<select name="PersonalGroup" class="MenuLinks" id="PersonalGroup">
<option value="None">Select a Group</option>
<%
While (NOT GroupList.EOF)
%>
<option value="<%=(GroupList.Fields.Item("PersonalGroupTitle").Value)%>"><%=(GroupList.Fields.Item("PersonalGroupTitle").Value)%></option>
<%
GroupList.MoveNext()
Wend
If (GroupList.CursorType > 0) Then
GroupList.MoveFirst
Else
GroupList.Requery
End If
%>
</select>
<input type="submit" class="MenuLinks" value="Go">
<a href="PersonalClipArt_List.asp" class="MenuLinks">ViewAll</a> </p>
</form></td>
<td width="276"><form action="GroupClipArt_GroupList.asp" method="get" name="form1" class="MenuLinks">
<p align="center">Shared Groups
<select name="SharedGroup" class="MenuLinks" id="SharedGroup">
<option value="None">Select a Group</option>
<%
While (NOT SharedList.EOF)
%>
<option value="<%=(SharedList.Fields.Item("SharedGroupTitle").Value)%>"><%=(SharedList.Fields.Item("SharedGroupTitle").Value)%></option>
<%
SharedList.MoveNext()
Wend
If (SharedList.CursorType > 0) Then
SharedList.MoveFirst
Else
SharedList.Requery
End If
%>
</select>
<input type="submit" class="MenuLinks" value="Go">
</p>
</form></td>
</tr>
</table></td>
</tr>
</table>
<% If Recordset1.EOF And Recordset1.BOF Then %>
<p align="center" class="MenuLinks">They are no images in this group </p>
<% End If ' end Recordset1.EOF And Recordset1.BOF %>
<% If Not Recordset1.EOF Or Not Recordset1.BOF Then %>
<table width="90%" border="0" align="center">
<tr>
<td height="40" valign="bottom" class="MenuLinks">IMAGE Title</td>
<td colspan="4" valign="top"><div align="center"><a href="uploadTester.asp" class="AdvertList01">Add
New Image</a> </div></td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<tr>
<td width="379" class="MainContent01"><A HREF="PersonalClipArt_Results.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ID=" & Recordset1.Fields.Item("ID").Value %>" class="MainContent01"><%=(Recordset1.Fields.Item("ImageTitle").Value)%></A> (<span class="MainContent02"><%=(Recordset1.Fields.Item("PersonalGroup").Value)%></span>)</td>
<td width="56" class="MenuLinks"><div align="center"><A HREF="PersonalClipArt_Results.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ID=" & Recordset1.Fields.Item("ID").Value %>" class="MenuLinks">View</A></div></td>
<td width="50" class="MenuLinks"><div align="center"><A HREF="PersonalClipArt_Amend.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ID=" & Recordset1.Fields.Item("ID").Value %>" class="MenuLinks">Amend</A></div></td>
<td width="43" class="MenuLinks"><div align="center"><A HREF="ClipArt_Delete.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ID=" & Recordset1.Fields.Item("ID").Value %>" class="MenuLinks">Delete</A></div></td>
<td width="83" class="MenuLinks"><div align="center"><A HREF="PersonalClipArt_GroupManager.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ID=" & Recordset1.Fields.Item("ID").Value %>" class="MenuLinks">Add
to Group</A> </div></td>
</tr>
<tr>
<td colspan="5" class="MainContent01"><hr></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
<% If Not Sounds.EOF Or Not Sounds.BOF Then %>
<table width="90%" border="0" align="center">
<tr>
<td width="62%" height="47" valign="bottom" class="MenuLinks">SOUND
Title</td>
<td colspan="4" valign="top" class="MenuLinks"><div align="center"><a href="../Sounds/uploadTester.asp" class="AdvertList01">Add
New Sound</a></div>
<div align="center"></div></td>
</tr>
<tr>
<td class="MainContent01"><a href="../Sounds/UploadedSounds/<%=(Sounds.Fields.Item("URLofSound").Value)%>" class="MainContent01"><%=(Sounds.Fields.Item("SoundTitle").Value)%></a> (<span class="MainContent02"><%=(Sounds.Fields.Item("PersonalGroup").Value)%></span> ) </td>
<td width="7%" class="MenuLinks"><div align="center"><a href="../Sounds/UploadedSounds/<%=(Sounds.Fields.Item("URLofSound").Value)%>" class="MenuLinks">Play</a></div></td>
<td width="9%" class="MenuLinks"><div align="center"><A HREF="../Sounds/Clipart_PersonalSoundAmend.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ID=" & Sounds.Fields.Item("ID").Value %>" class="MenuLinks">Amend</A></div></td>
<td width="9%" class="MenuLinks"><div align="center"><A HREF="../Sounds/Clipart_PersonalSoundDelete.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ID=" & Sounds.Fields.Item("ID").Value %>" class="MenuLinks">Delete</A></div></td>
<td width="13%" class="MenuLinks"><div align="center"><A HREF="../Sounds/ClipArt_PersonalGroupManager.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ID=" & Sounds.Fields.Item("ID").Value %>" class="MenuLinks">Add
to Group</A> </div></td>
</tr>
<tr>
<td colspan="5"><hr></td>
</tr>
</table>
<% End If ' end Not Sounds.EOF Or NOT Sounds.BOF %>
<% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %>
<!-- InstanceEndEditable --></td>
</tr>
</table>
</div></td><td bgcolor="#FFFFFF"> </td>
</tr>
<tr>
<td height="20" valign="bottom" bgcolor="#FFFFFF"><img src="../../Images/graycorners/2.jpg" width="20" height="20"></td>
<td valign="middle" bgcolor="#FFFFFF"><div align="center">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><img src="../../Images/BorderBorder1.jpg" width="703" height="16"></td>
</tr>
</table>
</div></td>
<td valign="bottom" bgcolor="#FFFFFF"><img src="../../Images/graycorners/1.jpg" width="20" height="20"></td>
</tr>
<tr bgcolor="#CCCCCC">
<td height="21" colspan="3"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="64%"><font color="#FFFFFF" size="1" face="Arial, Helvetica, sans-serif"><a href="http://www.spearwebdesign.co.uk" class="Speartext">Designed
by Spear Webdesign</a></font> </td>
<td width="36%"><div align="center" class="Speartext"> | <%= Session("MM_Username") %> |<a href="../../SigningOut.asp" class="Speartext">Sign
Out</a> | <a href="../../Index.asp" class="Speartext">Home</a> |</div></td>
</tr>
</table></td>
</tr>
</table>
</body>
<!-- InstanceEnd --></html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Sounds.Close()
Set Sounds = Nothing
%>
<%
GroupList.Close()
Set GroupList = Nothing
%>
<%
SharedList.Close()
Set SharedList = Nothing
%>
Here is the whole of the page in question. Thanks for you help...
dhotchin posted this at 10:52 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
Content shows when i remove this line
+ "'" + " AND Owner = '" + Session(MM_Username)
But i need this part to only show the users content not everyelses.
Abhishek Reddy posted this at 11:09 — 29th May 2005.
He has: 3,348 posts
Joined: Jul 2001
Print Session(MM_Username) and see if it gives the value you expect. I think it should be Session("MM_Username"), seeing as that's what you use elsewhere in the code.
dhotchin posted this at 11:12 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
I will give it a try, cheers, and thanks again
dhotchin posted this at 11:17 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
If i use the "MM_Username" on its own, it will show all images by the user. If i use the "Personal Groups" it will show all images in that group. but if i use both to only show the users images in that group select it will not show anything.
When using both, it does show the username of the person logged in, but will not show his/her images.
chrishirst posted this at 11:48 — 29th May 2005.
He has: 379 posts
Joined: Apr 2005
In VbScript you should use "&" (ampersand) instead of "+" (plus) for concatenating strings
response.write out the value of [RS].source after the concatenation to see if the SQL string is what you expect as well
Chris
Indifference will be the downfall of mankind, but who cares?
Venue Capacity Monitoring
Code Samples
dhotchin posted this at 15:46 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
SELECT * FROM Clipart_images WHERE PersonalGroup='Recordset1__MMColParam' AND Owner='Darren Hotchin' ORDER BY ImageTitle ASC
This is the message i get. The "PersonalGroup" is not work/showing.
dhotchin posted this at 15:54 — 29th May 2005.
He has: 183 posts
Joined: Nov 2003
GOT IT!!!! Thanks to every who help!
Here is the working scripted
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_SchoolDatabase03_STRING
Recordset1.Source = "SELECT * FROM Clipart_images WHERE PersonalGroup='" & Replace(Request.QueryString("PersonalGroup"),"'","''") & "'" & " AND Owner='"& Session("MM_Username") & "' ORDER BY ImageTitle ASC"
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("PersonalGroup") <> "") Then
Recordset1__MMColParam = Request.QueryString("PersonalGroup")
End If
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
The part in red is the part i changed.
Is this the best way of working with asp?
Again. thanks to all.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.