Author |
Topic |
|
siraj
VP-CART New User
USA
194 Posts |
Posted - February 21 2004 : 11:44:31
|
I have seen lot of users request display by manufacture. Something like category. The mfg field is in the products table need to be to to be populated and upon the selection it will bring the matching records for that particular manufcturer. This one can be used for display by artist if you have insert the artist name on the mfg column of the products table. I have tested in VP-ASP 4.0 to 5.0 works fine.
Open the file shopproductsubs.asp and all the way end before %>, cut and paste.
Sub navigateshowallmfg() dim arrmfgid,mfgid, mfgname,tmpSQL,i,arrmfg,recMFG,inMaxMFG Dim dbc, hassubcategory,mylink,j ShopOpenDatabase dbc '============== Get record count tmpSQL="select count(distinct UPPER(mfg)) as MaxMfg from products" set recMFG=dbc.execute(tmpSQL) inMaxMFG=recMFG("maxMfg") recMFG.close set recMFG=nothing tmpSQL="select distinct UPPER(mfg) as mfg from products" set recMFG=dbc.execute(tmpSQL) redim arrmfg(inMaxMFG) redim arrmfgid(inMaxMFG) i=1 j=0 While Not recMFG.EOF mfgname=recMFG("mfg") arrmfg(j)=mfgname arrmfgid(j)=i j=j+1 i=i+1 recMFG.MoveNext Wend recMFG.Close set recMFG=nothing ShopCloseDatabase catdbc
dim url,gid
if request("mfgid")="" then mfgid=0 else mfgid=cint(request("mfgid")) end if
%> <form name='mfgNavForm'> <select name="menu" onChange = "self.location = document.mfgNavForm.menu[document.mfgNavForm.menu.selectedIndex].value;" style="color: #000000; font-size: 8 pt; font-family: Verdana" size="1"> <option <%if mfgid=0 then%>SELECTED<%end if%> value='shopquery.asp'>Select a manufacture</option> <%
For i = 0 to (inMaxMFG-1) gid=arrmfgid(i) mfgname=arrmfg(i) url="shopquery.asp?mfg=" & server.urlencode(mfgname)
%> <option value='<%=url%>' <%if mfgid=gid then%>SELECTED<%end if%>><%=mfgname%></option> <% next response.write "</select></form>" end sub
Then in your header or whereever you want the dorpdown to show, insert this. <td valign="top"> <%navigateshowallmfg%> </td>
GOOD LUCK. SJ.
[email protected]
Edited by - siraj on February 21 2004 11:47:17 |
|
alla
Starting Member
10 Posts |
Posted - February 26 2004 : 11:06:35
|
I get error Microsoft VBScript runtime error '800a01f4'
Variable is undefined: 'catdbc'
/shopproductsubs.asp, line 667
|
|
|
siraj
VP-CART New User
USA
194 Posts |
Posted - February 26 2004 : 13:17:54
|
alla, just replace dim arrmfgid,mfgid, mfgname,tmpSQL,i,arrmfg,recMFG,inMaxMFG this line by dim arrmfgid,mfgid, mfgname,tmpSQL,i,arrmfg,recMFG,inMaxMFG,catdbc. Or right after Sub navigateshowallmfg() define this variable dim catdbc GOOD LUCK. SJ.
[email protected] |
|
|
rudeone
Starting Member
Australia
9 Posts |
Posted - March 01 2004 : 06:06:07
|
Wondering if you could help, i get this error;
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'count(distinct UPPER(mfg))'.
/mach2/shopproductsubs.asp, line 649
any idea's anyone?? Would love to get this script working!!!
|
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - March 01 2004 : 07:52:57
|
depends what database you're using; try replacing "UPPER" with "UCASE" and/or wrapping brackets inside the distinct value being selected
different databases allow different things when it comes to distincts/counts/group-by etc. For example, some databases might not allow you to do functions like upper/lower to a distinct. often you need to separate out the logic into different bits to make it work properly.
something you could do to get the count in a different way is: select count(*) from ( SELECT distinct(ucase(mfg)) FROM products)
that is, you'd replace: tmpSQL="select count(distinct UPPER(mfg)) as MaxMfg from products"
with: tmpSQL="select count(*) as MaxMfg from (select distinct(ucase(mfg)) FROM products)"
that should work for access databases, but mysql/sqlserver might be more along the lines of:
tmpSQL="select count(*) as MaxMfg from (select distinct(upper(mfg)) FROM products)"
Edited by - devshb on March 01 2004 09:42:27 |
|
|
siraj
VP-CART New User
USA
194 Posts |
Posted - March 01 2004 : 17:42:14
|
If you are using Access, then devshb is right. Try that and let us know. I never tested for Access. Good Luck, SJ.
[email protected] |
|
|
rudeone
Starting Member
Australia
9 Posts |
Posted - March 02 2004 : 00:18:13
|
Very good, that fixed that error, but now I need help with the next problem.
This is the line, the error is with UPPER not being defined;
tmpSQL="select distinct UPPER(mfg) as mfg from products"
So i changed upper to ucase
tmpSQL="select distinct ucase(mfg) as mfg from products"
and the page loaded, but nothing in the drop down box, and the rest of the page did not load. I think it’s the way it’s written, it needs to be formatted (like you did last time), with the logic separated in to little bits.
Thanks in advance!!!!!!!!!! (Im not very advanced with this language and would appreciated any help.!!!)
Edited by - rudeone on March 02 2004 00:19:13 |
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - March 02 2004 : 03:27:27
|
there are 2 sqls; the bit that gets the count, and the bit that gets the descriptions; you need to make sure both have the relevant changes in them, eg:
'============== Get record count tmpSQL="select count(distinct UPPER(mfg)) as MaxMfg from products"
goes to: '============== Get record count tmpSQL="select count(*) as MaxMfg from (select distinct(ucase(mfg)) FROM products)"
AND**:
tmpSQL="select distinct UPPER(mfg) as mfg from products" set recMFG=dbc.execute(tmpSQL)
goes to: tmpSQL="select distinct ucase(mfg) as mfg from products" set recMFG=dbc.execute(tmpSQL)
hope that does the trick, if not, just create another message/reply in this topic.
having looked at siraj's code above, the logic/code looks fine to me, the only changes needed are for different databases, but you'll always have that problem with counts/distincts; no database does/allows exactly the same things for those, so a generic all-database bit of sql for that chunk of code could never be done unless you also included all the vp-asp database switch logic in the chunk of code, and that code was offered for free, so I wouldn't expect that level of detail ! anyway; hope it works; let us all know how you get on!
Edited by - devshb on March 02 2004 03:38:43 |
|
|
rudeone
Starting Member
Australia
9 Posts |
Posted - March 02 2004 : 03:48:02
|
Okay, i changed those lines of code, and the page loads with no errors, but it does not load anything after the <%navigateshowallmfg%>
And there are not brands in the list?!?!?
Any ideas would be most appreciated.
|
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - March 02 2004 : 05:08:18
|
if it's not showing anything after the call to that function, then there might be errors going on that aren't getting displayed.
Firstly, try:
<% navigateshowallmfg() %>
(ie with spaces and brackets, exactly as shown above)
instead of: <%navigateshowallmfg%>
(but that's really just grasping at straws to be honest)
secondly, if you're not already, try loading the page in netscape instead of internet explorer, as netscape has much better error-handling and will show you errors that internet explorer would not, and shows you much more detailed/useful messages. For example, internet explorer will just say "can't load page" (or nothing), but netscape will show the full vbscript error message/line-number etc.
which file is it that you're adding the <% navigateshowallmfg() %> call into ? This is crucial, because if you add it to a file where the parent is only an html and not an asp then embedded asp will cause problems. shopheader, even though it's an html, will get treated like an asp if it's used/included in other asps, but if you include shopheader in, say, index.html then the asp bit will get ignored or produce errors. Basically, if you want all the dynamic stuff to work properly, then there should never be an html file in the user's url address line.
Edited by - devshb on March 02 2004 05:14:14 |
|
|
rudeone
Starting Member
Australia
9 Posts |
Posted - March 02 2004 : 05:16:59
|
Im adding the navigateshowallmfg() to default.asp, and the actual code is in shopproductsubs.asp
I will try loading in netscape, thats a great idea.. i will try and get more information before i post next...
Thanks heaps Rudi
|
|
|
rudeone
Starting Member
Australia
9 Posts |
Posted - March 02 2004 : 15:35:29
|
devshb, i have taken your advice and used netscape, i think your close, because were down to one of the last lines. :)
url="shopquery.asp?mfg=" & server.urlencode(mfgname)
Error Type: Microsoft VBScript compilation (0x800A0409) Unterminated string constant /mach2/shopproductsubs.asp, line 691, column 53 url="shopquery.asp?mfg=" & server.urlencode(mfgname)"
What do you think? or do you need more info about the error?? Thanks..!
(this is what ie and netscape give me now)
|
|
|
devshb
Senior Member
United Kingdom
1904 Posts |
Posted - March 02 2004 : 15:42:36
|
that's just a simple typo i think.....
url="shopquery.asp?mfg=" & server.urlencode(mfgname)"
should be
url="shopquery.asp?mfg=" & server.urlencode(mfgname)
(ie no quote on the end)
|
|
|
rudeone
Starting Member
Australia
9 Posts |
Posted - March 02 2004 : 15:50:28
|
It never ends :(
Error Type: Microsoft VBScript runtime (0x800A000D) Type mismatch: 'URLEncode' /mach2/shopproductsubs.asp, line 691
(still in the same line) Is there a substitute for URLEncode?
|
|
|
rudeone
Starting Member
Australia
9 Posts |
Posted - March 02 2004 : 15:50:55
|
It never ends :(
Error Type: Microsoft VBScript runtime (0x800A000D) Type mismatch: 'URLEncode' /mach2/shopproductsubs.asp, line 691
(still in the same line) Is there a substitute for URLEncode?
|
|
|
rudeone
Starting Member
Australia
9 Posts |
Posted - March 02 2004 : 15:50:55
|
It never ends :(
Error Type: Microsoft VBScript runtime (0x800A000D) Type mismatch: 'URLEncode' /mach2/shopproductsubs.asp, line 691
(still in the same line) Is there a substitute for URLEncode?
|
|
|
|
Topic |
|