|
 |
access_asp thread: RE: View Products by Manufacturer and Category
Message #1 by "Lori Bannon" <lori@s...> on Thu, 2 Jan 2003 19:25:41
|
|
> Well, I'd start by ordering your SQL string by the field category:
Select * from whatever order by fldCategory
Then, if you want to apply some special formating, just compare the current
fldCategory field value (inside the loop) with the previous one:
dim temp
with oRs
do while not .eof
if .Fields("fldCategory") <> temp then
' different category...do something
different
else
' still the same category.. just do the
same thing
temp = .Fields("fldCategory")
.movenext
loop
end with
In the first record, it'll compare the fldCategory to an empty string,
applying the new formatting.
The second record, however, will be compared to the first one, thus
enabling
the routine to "know" wheter it is still in the same category or not.
HTH
Paulo Fernandes
Consernet, Lda.
www.consernet.com
Telefone: +xxx xxx xxx xxx
Telemóvel: +xxx xxx xxx xxx
Fax: +xxx xxx xxx xxx
----------------------------
Este e-mail é confidencial. Se você não é o destinatário pretendido deste
e-mail,não deverá usar qualquer informação nele contida. Se recebeu este
e-mail por erro, envie-o para administrador@c..., apagando-o de
seguida. Obrigado.
This email is confidential. If you are not the intended recipient, you must
not disclose or use the information contained in it. If you have received
this mail in error, please forward it to administrador@c... and
delete the document. Thank you.
-----Mensagem original-----
De: Lori Bannon [mailto:lori@s...]
Enviada: segunda-feira, 30 de Dezembro de 2002 22:36
Para: Access ASP
Assunto: [access_asp] View Products by Manufacturer and Category
So far with my little template fun (go here if you want to view the
template: http://www.skyesweb.com/template/index.asp) I have it set up so
that when a user selects by manufacturer the productDisplay.asp page will
only display those records that have that manufacturer name.
Code:
<%
strSQL = "Select * from tblList, tblManu"
strSQL = strSQL & " Where tblList.fldName=tblManu.fldManuName"
If fieldManuName <> "" Then
strSQL = strSQL & " AND tblList.fldName='" & fieldManuName & "'"
End If
'Set connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'Use the execute method of the connection object the insert the record
SET oRs = conn.execute(strSQL)
While Not oRs.EOF
%>
<tr>
<td><% strImagePath = oRs.Fields("fldImagePath")
Response.Write("<img src=" & strImagePath & ">") %></td>
<td class="colorHeader"><% = oRs.Fields("fldName") %></td>
<td class="colorHeader"><% = oRs.Fields("fldDesc") %></td>
<td class="colorHeader"><% = oRs.Fields("fldPrice") %></td>
</tr>
<% oRs.MoveNext
wend%>
<%
conn.close
set conn = nothing
%>
Now I want to get a little more complicated and seperate the different
categories as well on the same productDisplay.asp page:
For Instance:
Manufacturers Name
Amplifiers
Image Name Description Price
Preamplifiers
Image Name Description Price
Speakers
Image Name Description Price
and so on...
I was hoping that a simple:
If oRs.Fields("fldCategory") = "Amplifiers" then
write those records that have that category
would work but no such luck. Maybe i'm going about it the wrong way.
Any suggestions????
thanks ahead of time
Lori
Message #2 by "Lori Bannon" <lori@s...> on Thu, 2 Jan 2003 19:28:25
|
|
Paulo,
I'm not sure what the something is (' different category...do something).
This is what I have so far and it still does not work:
While Not oRs.EOF
if oRs.Fields("fldCategory") = "Amplifiers" then
fieldCategory = oRs.Fields("fldCategory")
else
oRs.Fields("fldCategory") = ""
End If
oRs.MoveNext
wend%>
The error is:
Current Recordset does not support updating. This may be a limitation of
the provider, or of the selected locktype.
I understand the concept of what you're saying but don't know how to
phrase it in the sections that you have commented.
thanks ahead of time.
Lori
> Well, I'd start by ordering your SQL string by the field category:
Select * from whatever order by fldCategory
Then, if you want to apply some special formating, just compare the current
fldCategory field value (inside the loop) with the previous one:
dim temp
with oRs
do while not .eof
if .Fields("fldCategory") <> temp then
' different category...do something
different
else
' still the same category.. just do the
same thing
temp = .Fields("fldCategory")
.movenext
loop
end with
In the first record, it'll compare the fldCategory to an empty string,
applying the new formatting.
The second record, however, will be compared to the first one, thus
enabling
the routine to "know" wheter it is still in the same category or not.
HTH
Paulo Fernandes
Consernet, Lda.
www.consernet.com
Telefone: +xxx xxx xxx xxx
Telemóvel: +xxx xxx xxx xxx
Fax: +xxx xxx xxx xxx
----------------------------
Este e-mail é confidencial. Se você não é o destinatário pretendido deste
e-mail,não deverá usar qualquer informação nele contida. Se recebeu este
e-mail por erro, envie-o para administrador@c..., apagando-o de
seguida. Obrigado.
This email is confidential. If you are not the intended recipient, you must
not disclose or use the information contained in it. If you have received
this mail in error, please forward it to administrador@c... and
delete the document. Thank you.
-----Mensagem original-----
De: Lori Bannon [mailto:lori@s...]
Enviada: segunda-feira, 30 de Dezembro de 2002 22:36
Para: Access ASP
Assunto: [access_asp] View Products by Manufacturer and Category
So far with my little template fun (go here if you want to view the
template: http://www.skyesweb.com/template/index.asp) I have it set up so
that when a user selects by manufacturer the productDisplay.asp page will
only display those records that have that manufacturer name.
Code:
<%
strSQL = "Select * from tblList, tblManu"
strSQL = strSQL & " Where tblList.fldName=tblManu.fldManuName"
If fieldManuName <> "" Then
strSQL = strSQL & " AND tblList.fldName='" & fieldManuName & "'"
End If
'Set connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'Use the execute method of the connection object the insert the record
SET oRs = conn.execute(strSQL)
While Not oRs.EOF
%>
<tr>
<td><% strImagePath = oRs.Fields("fldImagePath")
Response.Write("<img src=" & strImagePath & ">") %></td>
<td class="colorHeader"><% = oRs.Fields("fldName") %></td>
<td class="colorHeader"><% = oRs.Fields("fldDesc") %></td>
<td class="colorHeader"><% = oRs.Fields("fldPrice") %></td>
</tr>
<% oRs.MoveNext
wend%>
<%
conn.close
set conn = nothing
%>
Now I want to get a little more complicated and seperate the different
categories as well on the same productDisplay.asp page:
For Instance:
Manufacturers Name
Amplifiers
Image Name Description Price
Preamplifiers
Image Name Description Price
Speakers
Image Name Description Price
and so on...
I was hoping that a simple:
If oRs.Fields("fldCategory") = "Amplifiers" then
write those records that have that category
would work but no such luck. Maybe i'm going about it the wrong way.
Any suggestions????
thanks ahead of time
Lori
Message #3 by "Paulo Fernandes" <paulofernandes@c...> on Thu, 2 Jan 2003 22:08:12 -0000
|
|
Lori,
Okay... so let's start from the beginning and do it properly:
1 - Your table (tbl_products) probably looks something like this:
-------------------------------------------------------------------
|Id|Manufacturer| Category |Image| Name | Description | Price |
-------------------------------------------------------------------
| 1|Yamaha |Amplifiers|1.gif| ya1 | Cool yamaha amp | 200,00|
| 2|Yamaha |PreAmp |2.gif| pa1 | Whatever | 150,00|
| 3|Sony |Amplifiers|x.gif| sa1w | Cool sony amp | 200,00|
| 4|Sony |PreAmp |y.gif| pae1 | Whatever too | 150,00|
| 5|Yamaha |PreAmp |5.gif| pw1 | Another example | 550,00|
| 6|Yamaha |PreAmp |7.gif| pd1 | Whatever again | 650,00|
-------------------------------------------------------------------
2 - What you want to do is to chose the manufacturer from the combo box,
press the submit button and display every product matching the chosen
manufacturer. With the above table, if you chose Yamaha you would have
the following:
__________________________________________________________________
Amplifiers
__________________________________________________________________
Image Name Description Price
1.gif Ya1 Cool yamaha amp 200,00
__________________________________________________________________
PreAmp
__________________________________________________________________
Image Name Description Price
2.gif pa1 Whatever 150,00
5.gif pw1 Another example 550,00
7.gif pd1 Whatever again 650,00
3 - Now the recordset part:
Selecting only the records from the chosen manufacturer:
strSQL1 = "SELECT * FROM tbl_products where Manufacturer like '" &
request.form("id_questionario") & "' ORDER BY Category"
oRs.Open strSQL1, objConn1, adOpenKeyset, adLockPessimistic, adCmdText
4 - Displaying the records:
dim temp
with oRs
do while not .eof
if .Fields("Category") <> temp then
'first time this Category shows up
response.write "<hr><br>" & .fields("Category") &
"<hr><br>"
response.write "Image "
response.write "Name "
response.write "Description "
response.write "Price "
response.write "<br><br>"
response.write .fields("Image") & " "
response.write .fields("Name") & " "
response.write .fields("Description") &
" "
response.write .fields("Price") & " "
response.write "<br>"
else
'we've seen this category before, no need to build the title
response.write .fields("Image") & " "
response.write .fields("Name") & " "
response.write .fields("Description") &
" "
response.write .fields("Price") & " "
response.write "<br>"
end if
temp = .Fields("Category")
.movenext
loop
end with
What will happen is that, once we've ordered the recordset using the
field Category, we can assume that the category will repeat itself until
ther are no more records corresponding to that category. Thus, all we
have to do is compare the category of the current record with the
category of the previous record. If they are the same we are still
listing a product within the same category, if not then we write the
title of the category and the sub-titles again.
5 - THE END !!! (just kidding!)
I've written this in Outlook, so don't be surprised if you come across
some typing errors. Also, I didn't worry about making it pretty (with
tables and alignments)... I figured that you'd like to do it yourself.
BTW, wouldn't it make more sense to have 2 combos, 1 with the
manufacturer and another one with the categories? This way, if I was
looking for some Yamaha speakers I wouldn't have to go thru the entire
recordset of Yamaha products just to find the speakers I was looking
for... and if you add one "All products" choice to the second combo then
you could list the entire stuff. Well, it was just a thought !!!
HTH
Paulo
-----Original Message-----
From: Lori Bannon [mailto:lori@s...]
Sent: quinta-feira, 2 de Janeiro de 2003 19:28
To: Access ASP
Subject: [access_asp] RE: View Products by Manufacturer and Category
Paulo,
I'm not sure what the something is (' different category...do
something).
This is what I have so far and it still does not work:
While Not oRs.EOF
if oRs.Fields("fldCategory") = "Amplifiers" then
fieldCategory = oRs.Fields("fldCategory")
else
oRs.Fields("fldCategory") = ""
End If
oRs.MoveNext
wend%>
The error is:
Current Recordset does not support updating. This may be a limitation of
the provider, or of the selected locktype.
I understand the concept of what you're saying but don't know how to
phrase it in the sections that you have commented.
thanks ahead of time.
Lori
Message #4 by "Lori Bannon" <lori@s...> on Fri, 3 Jan 2003 17:47:19
|
|
Paulo,
First off, I really appreciate the time you are taking helping me out. I
love trying to figure this out but can use all the help I can get.
What I did was save my original file and started over using your plan.
This is the SQL statement I used:
strSQL = "SELECT * FROM tblList where fldManuName like '" &
Request.form("ManuDrop") & "' ORDER BY fldCategory"
This is the error:
Syntax error
/template/productDisplay.asp, line 29, column 59
strSQL = "SELECT * FROM tblList where fldManuName like '" &
----------------------------------------------------------^
ManuDrop is the name of my Select:
<Select Name="ManuDrop" size="1">
<%
While Not oRs.EOF
%>
<OPTION VALUE="<%= oRs.Fields("fldManuName") %>"><%= oRs.Fields
("fldManuName") %></option>
<%
oRs.MoveNext
wend%>
</Select>
Thats as far as I got...I'll keep fooling around with it and check back to
see what you think.
Also,
I plan on having two combo boxes and a search eventually but figured for
learning purposes I should just try and get this one to work first.
thanks again
Lori
> Lori,
Okay... so let's start from the beginning and do it properly:
1 - Your table (tbl_products) probably looks something like this:
-------------------------------------------------------------------
|Id|Manufacturer| Category |Image| Name | Description | Price |
-------------------------------------------------------------------
| 1|Yamaha |Amplifiers|1.gif| ya1 | Cool yamaha amp | 200,00|
| 2|Yamaha |PreAmp |2.gif| pa1 | Whatever | 150,00|
| 3|Sony |Amplifiers|x.gif| sa1w | Cool sony amp | 200,00|
| 4|Sony |PreAmp |y.gif| pae1 | Whatever too | 150,00|
| 5|Yamaha |PreAmp |5.gif| pw1 | Another example | 550,00|
| 6|Yamaha |PreAmp |7.gif| pd1 | Whatever again | 650,00|
-------------------------------------------------------------------
2 - What you want to do is to chose the manufacturer from the combo box,
press the submit button and display every product matching the chosen
manufacturer. With the above table, if you chose Yamaha you would have
the following:
__________________________________________________________________
Amplifiers
__________________________________________________________________
Image Name Description Price
1.gif Ya1 Cool yamaha amp 200,00
__________________________________________________________________
PreAmp
__________________________________________________________________
Image Name Description Price
2.gif pa1 Whatever 150,00
5.gif pw1 Another example 550,00
7.gif pd1 Whatever again 650,00
3 - Now the recordset part:
Selecting only the records from the chosen manufacturer:
strSQL1 = "SELECT * FROM tbl_products where Manufacturer like '" &
request.form("id_questionario") & "' ORDER BY Category"
oRs.Open strSQL1, objConn1, adOpenKeyset, adLockPessimistic, adCmdText
4 - Displaying the records:
dim temp
with oRs
do while not .eof
if .Fields("Category") <> temp then
'first time this Category shows up
response.write "<hr><br>" & .fields("Category") &
"<hr><br>"
response.write "Image "
response.write "Name "
response.write "Description "
response.write "Price "
response.write "<br><br>"
response.write .fields("Image") & " "
response.write .fields("Name") & " "
response.write .fields("Description") &
" "
response.write .fields("Price") & " "
response.write "<br>"
else
'we've seen this category before, no need to build the title
response.write .fields("Image") & " "
response.write .fields("Name") & " "
response.write .fields("Description") &
" "
response.write .fields("Price") & " "
response.write "<br>"
end if
temp = .Fields("Category")
.movenext
loop
end with
What will happen is that, once we've ordered the recordset using the
field Category, we can assume that the category will repeat itself until
ther are no more records corresponding to that category. Thus, all we
have to do is compare the category of the current record with the
category of the previous record. If they are the same we are still
listing a product within the same category, if not then we write the
title of the category and the sub-titles again.
5 - THE END !!! (just kidding!)
I've written this in Outlook, so don't be surprised if you come across
some typing errors. Also, I didn't worry about making it pretty (with
tables and alignments)... I figured that you'd like to do it yourself.
BTW, wouldn't it make more sense to have 2 combos, 1 with the
manufacturer and another one with the categories? This way, if I was
looking for some Yamaha speakers I wouldn't have to go thru the entire
recordset of Yamaha products just to find the speakers I was looking
for... and if you add one "All products" choice to the second combo then
you could list the entire stuff. Well, it was just a thought !!!
HTH
Paulo
-----Original Message-----
From: Lori Bannon [mailto:lori@s...]
Sent: quinta-feira, 2 de Janeiro de 2003 19:28
To: Access ASP
Subject: [access_asp] RE: View Products by Manufacturer and Category
Paulo,
I'm not sure what the something is (' different category...do
something).
This is what I have so far and it still does not work:
While Not oRs.EOF
if oRs.Fields("fldCategory") = "Amplifiers" then
fieldCategory = oRs.Fields("fldCategory")
else
oRs.Fields("fldCategory") = ""
End If
oRs.MoveNext
wend%>
The error is:
Current Recordset does not support updating. This may be a limitation of
the provider, or of the selected locktype.
I understand the concept of what you're saying but don't know how to
phrase it in the sections that you have commented.
thanks ahead of time.
Lori
Message #5 by "Lori Bannon" <lori@s...> on Fri, 3 Jan 2003 22:48:22
|
|
Paulo,
OK...I've been messing with it most of the afternoon and this is what I
have:
--------------------------------------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html lang="en">
<head>
<title>Template Index Page</title>
<link rel=stylesheet href="stylin.css" type="text/css">
<%
'Declare all local variables
dim conn
dim rs
dim strconn
dim strsql
dim oRs
dim fieldManuName
dim pageTitle
dim fieldCategory
strsql = ""
'set connection string to local variable-I use a DSN-less connection
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath
("data/database.mdb")
'this is to populate the drop down box
strSQL = "SELECT * FROM tblManu"
strSQL = strSQL & " Order By fldManuName"
fieldManuName = Request.Form("ManuDrop")
'Set connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'Use the execute method of the connection object the insert the record
SET oRs = conn.execute(strSQL)
%>
</head>
<!--#include file="include/body.htm"-->
<table width="760" border="0" cellspacing="0" cellpadding="0">
<!--//top navigation where the drop down is located//-->
<tr>
<td colspan="2" nowrap class="textMain"><!--#include
file="include/topNav.asp"--></td>
</tr>
<tr valign="top">
<!--//left navigation include//--><td width="180" nowrap><!--#include
file="include/leftNav.htm"--></td>
<!--//end of left nav //-->
<!--// beginning of middle content //-->
<td>
<table width="500" border="0" cellspacing="5" cellpadding="5"
align="center">
<tr valign="top">
<td width="500" colspan="3" nowrap class="textMain"><img
src="images/main/arrow.gif" width="8" height="9" border="0"
alt=""> You are at: <span class="colorHeader"><%=
fieldManuName %></span></td>
</tr>
<%
'this is to populate the product information HTML table
strSQL = "SELECT * FROM tblList"
strSQL = strSQL & " Order By fldCategory"
'Set connection object
set conn = server.createobject("adodb.connection")
conn.open strconn
'Use the execute method of the connection object the insert the record
SET oRs = conn.execute(strSQL)
If fieldManuName <> "" Then
strSQL = strSQL & " AND fldManufacturer='" & fieldManuName & "'"
End If
While Not oRs.EOF
dim temp
if oRs.Fields("fldCategory") <> temp then
%>
<tr>
<td class="colorHeader"><%= oRs.Fields("fldCategory") %></td>
</tr>
<tr valign="top">
<td class="colorHeader">Image</td>
<td class="colorHeader">Name</td>
<td class="colorHeader">Description</td>
<td class="colorHeader">Price</td>
</tr>
<tr>
<td><% strImagePath = oRs.Fields("fldImagePath")
Response.Write("<img src=" & strImagePath & ">") %></td>
<td class="colorHeader"><%= oRs.Fields("fldName") %></td>
<td class="colorHeader"><%= oRs.Fields("fldDesc") %></td>
<td class="colorHeader"><%= oRs.Fields("fldPrice") %></td>
</tr>
<%
else
%>
<tr>
<td><% strImagePath = oRs.Fields("fldImagePath")
Response.Write("<img src=" & strImagePath & ">") %></td>
<td class="colorHeader"><%= oRs.Fields("fldName") %></td>
<td class="colorHeader"><%= oRs.Fields("fldDesc") %></td>
<td class="colorHeader"><%= oRs.Fields("fldPrice") %></td>
</tr>
<%
end if
temp = oRs.Fields("fldCategory")
oRs.MoveNext
wend%>
<%
conn.close
set conn = nothing
%>
</table>
</td>
</tr>
</td>
</tr>
</table>
</body>
</html>
--------------------------------------------------------------------------
The results are that it seperates the field Categories nicely but it still
gives all of the records. This statement:
If fieldManuName <> "" Then
strSQL = strSQL & " AND fldManufacturer='" & fieldManuName & "'"
End If
Is designed to only display those fldManufacturer records that are equal
to fieldManuName which is coming from the drop down select...its acting
like it isn't reading the fldManufacturer field?
Lori
|
|
|
View Cart
