Hi all,

Was getting sick of clicking next 10 for a million times when browsing my musicdatabase, so i decided to build search into it, my database being much more complex then the one i learned to search (just 1 table and mine is 7 tables) i thought i read tutorial, great, had to set up new database, now halfway through tutorial i hit a problem.

I've reached where it should be searching perfectly, it does i must add (original pages) until i use the textfield, even one letter returns a error, all the parameter boxes work perfectly, the sorting works great, but the most important feature gives errors and i have been trying things for hours now and canNOT figure it out.

the tutorial is located here: www.tdsf.co.uk/tdsfdemo/default.htm
(yes, was a UDzone link...)
and the originalsearch pages from the tutorial are here:
dennisvg.homeip.net/TDSFdemosite/originalsearch.asp
(note how entering a letter makes it stop functioning...)
and the project where i was recreating it is here:
dennisvg.homeip.net/Mysite/Music/search.asp
(pressing search just gives errors, no matter what i do.)

I'm pretty much lost, following the tutorial is vague, alot of text and explanation, but explaining the WRONG things, nothing about potential errors and while creating the sql statement as the tutorial describes, everything works UPTO the point where i have to add a WHERE statement, everything keeps screaming at me that it wants parameters, so i give it parameters and still it yells at me !

Can anyone help me out ?
I just want to type Silver in the textfield
and see what cd's have the track Silver Machine, my guess is around 60+ returned items <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

*sighs* just wanted to get this working tonight, but i guess i hit a wall because they use VBScript, but still i'm doing it all like they describe, even built up my database from the ground up with relations set to how they describe them.

My own search without any additional input gives me this:

Compilationerror Microsoft JScript (0x800A03EC)
';' expected
/Mysite/Music/results.asp, line 4, column 3
If Request("selGenre" = "" Then
--^

POST Data:
txtKeywords=&selMatchType=AND&selFormat=-1&selGenre=-1&selSortBy=+ORDER+BY+ArtistName&radDirection=+ASC&Submit=Submit

And the original does this when entering some text:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access-stuurprogramma] Er zijn te weinig parameters. Het verwachte aantal is: 1.
/TDSFdemosite/originalresults.asp, line 44

POST Data:
txtKeywords=Silver&selMatchType=AND&selFormat=-1&selGenre=-1&selSortBy=+ORDER+BY+ArtistName&radDirection=+ASC

Translated that means there's too few parameters, expecting nr is 1...
But look at the post data...How many parameters does it want ? i'm giving it like 5 parameters now.
here's the code the originalresults.asp seems to get stuck on...

&lt;%
set rsQryCombinedFields = Server.CreateObject("ADODB.Recordset" ' The Ultradev genrated code to open the recordset and return the rows that contain a match with the Keywords entered in the textfield
rsQryCombinedFields.ActiveConnection = MM_TDSFdemoOLE_STRING
rsQryCombinedFields.Source = "SELECT DISTINCT AlbumID FROM QryCombinedFields" & WhereKeywordsString ' Here we've added or WhereKeywordsString variable
rsQryCombinedFields.CursorType = 0
rsQryCombinedFields.CursorLocation = 2
rsQryCombinedFields.LockType = 3
rsQryCombinedFields.Open() &lt;---line 44
rsQryCombinedFields_numRows = 0
%&gt;

Any ideas anyone ?

With kind regards,

Dennis van Galen
Webmaster SPG Finance
KPN Telecom Holland NV

Edited by - djvgalen on 08/12/2001 23:15:42

ugh, i found out why my own recreation of it fails...
site defined to use Jscript, so it keeps changing my &lt;% Language=VBScript %&gt; back to Javascript, oh well, was allready working on a visually more pleasing VB version of it anyway, still can't get the VBScript original (in it's own comfy VB site) to accept text, ideas are welcome <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

With kind regards,

Dennis van Galen
Webmaster SPG Finance
KPN Telecom Holland NV

I think it is a little beyond the scope of the tutorial to cover all the potential errors, the list of possible causes is just too long.

In any case it sounds like a problem with the SQL SELECT statement used to create the first recordset <b>rsQryCombinedFields</b>. Use <b>Response.Write</b> to display the variable <b>WhereKeywordsString</b> in order to check the code generated SQL SELECT statement.

You might also find the VBScript documentation very useful, you can dowload it from:
msdn.microsoft.com/scripting/vbscript/download/vbsdoc.exe

You can find lots more useful scripting information and tools at:
msdn.microsoft.com/scripting/default.htm

Regards

Owen.

life is weird !

Thanks for the 97 version database Owen, it searches that perfectly, i renamed my Access queries to match yours and it's the same thing... it searches using the menu's, but errors entering text, switching back to your dbase i can use the textfield, so there must be some weirdo thing in my dbase... i made all original names as you described them in your tutorial, but my old dbase had quite a bit more data, picture locations, references to another dbase, track length, things like that.

I will look into it tomorrow, it's now a bit late and i don't think my head is upto the task of scrolling through all that data, you say it multiplies findings by four at some point, then i would get like 13.000 entries returned, LOL.
Maybe it's the memo comment field ?

I will get it to work.

With kind regards,

Dennis van Galen
Webmaster SPG Finance
KPN Telecom Holland NV

<font face='Arial'>Dennis,

Something has occured to me, within your own database I assume that there is a Query named rsQryCombinedFields. This Query must contain a field (or column) called CombinedFields in which the search will look for text matches.

You mention that your own database contains extra information and relationships. The demo search is not a front end that can be bolted on to any database. The search pages are specific to the demo database and depending on the particular structure of your own database may require a considerable amount of reworking.

For instance are you trying to find matches in the memo Comment field? Has this been concateated with other fields that you wish to search?

Bear in mind that the tutorial is not a solution. It was designed to cover a broad range of scenarios, concatenating database fields and tables etc. in order that people could learn the techniques and adapt them to suit their own particular projects.

Regards

Owen. </font id='Arial'>

IT WORKS !
hurrah, and no i'm not including the memo fields in the search, first i have to figure out how exactly it works and i have to convert my existing detailOverkill pages to VBScript, main concern there is involving those memo fields again, wish there was a option you could turn on so it would treat memo fields as such <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>
Instead i have to manually add a bit of code, oh well, only have to do it once, for all dynamic pages relying on memo fields, once it runs, it runs...

I'm only searching the fields you showed how to search, and i still haven't figured out why my own new Dbase gets the textfield error, i copied 75% of all info over in your Dbase and it still seems to work, so the problem is either with the money values i added or the purchase date i included in my original Dbase.

dennisvg.homeip.net/Mysite/Music/Hawkalbum.asp?OpnameId=86
This page shows what I use this data for...

dennisvg.homeip.net/Mysite/Music/Song_Detail.asp?Id=127
That page is the result you get when you click on the links nexto ALL individual Hawkwind tracks, ALL 114 albums and all 2000(?) tracks.

I was studying your results page a bit more today and think i begin to understand how it works, so in time this searchengine will expand to include my other data, so that someone can enter say "Stonehenge" and they will be buried in information, drawn from memo-fields, album titles, track titles, concerts played, band line-ups, etc...

Oops, that was classified !
Hehe, no use hiding the fact that my HawkZone is going to be the biggest online, talking about lyrics alone I allready beat all sites around and the funny thing is...I typed 95% of it in myself, and a greek friend supplied the other 5%.
I must have been bored the last 10 years, typing in lyrics as i listened to songs, noting down every other significant detail concerning hawkwind that crossed my path...Maybe i'm just a big fan, like i'm told pretty often.

The QryCombinedFields you mention currently has 2 fields, 1 AlbumID and 1 CombinedFields consisting of the ArtistName, AlbumName and Track fields (currently 3437 records in that Query).
Implementing the comment field (additional info) would be possible since it's a set field for every record (Album), but then i would get a album with 20 tracks that would return the same word 20 times, but that's why we added the SELECT DISTINCT into the SQL statement, so that the AlbumID is only returned once, am i correct ?
I think I am, because searching on Hawkwind returns about 116 values (two collections with Hawkwind on them) Anyway, tomorrow it'll return about 130 entries for Hawkwind, because i can finally distinguish between vinyl/cd/vhs/dvd/postcard single (got 8 of those :-P) Oh, I can further built on it, so people can see what my many bon-jovi titles are, cd-single, 1 track promo's, etc...
Should relieve a bit of weight out of those memo fields...

Anyway, thanks for your help Owen, I'm glad i got it to work and if i can adept this to search my other tables too then i will let you and everyone know how i did it.
But for now I'm a happy man <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

With kind regards,

Dennis van Galen
Webmaster SPG Finance
KPN Telecom Holland NV

I talk too much though... <img src=../images/dmxzone/forum/icon_smile_shock.gif border=0 align=middle>

Edited by - djvgalen on 08/14/2001 16:31:37

Dennis,

In the tutorial <b>Chapter 12.0 Food for thought</b> I mentioned the fact that the number of rows produced by a query will be increased when you included one to many relationships. The number of rows in the tabe on the ONE side of the relationship is increased by a factor equivalent to the number of rows on the MANY side on the relationship. So increasing the number of formats in the formats Table will increase the number of rows returned by the Query QryDisplayFields. For example if you have 100 albums in your database all of which are available on 6 formats the resultant query would contain 600 rows.

You also mention that you would like to include a search for Lyrics and Comments. I would suggest that you search these separately. Indeed you might want to think about having the track search separate as well if your database is getting very large as this method will be more efficient in database terms.

Set up the search page with separate text fields for Lyrics and Comments both of which are contained along with a Search Button within their own form with the Action set to two new results pages you will need to create.

For Example:

<img src="www.tdsf.co.uk/tdsfdemo/tutorialimages/SearchPage.gif" border=0>

In the database you would have a table for Lyrics with two columns, one for the AlbumID and another for the Lyrics and a similar table for Comments, both much like the TracListings table in the TDSFdemo database.

Create a two new results pages for the Lyics Search and the Comments Search, these can look identical to the original results page but use different Queries to create the Recordsets.

For the Lyrics results page Create a Query which incorporates AlbumID, ArtistName and AlbumTitle fom the Albums Table, Genre from the Genres Table and Lyrics From the Lyrics Table. Should look like this in SQL view:

<b>SELECT Albums.AlbumID, Albums.ArtistName, Albums.AlbumTitle, Genres.Genre, Lyrics.Lyrics
FROM Genres INNER JOIN (Albums INNER JOIN Lyrics ON Albums.AlbumID = Lyrics.AlbumID) ON Genres.GenreID = Albums.GenreID;</b>

Once you have created the Query add a recordset to the Lyrics results page and name it something like rsQryLyrics and set up the Repeat Region and Show/Hide server behaviours as on the oiginal results page.

Next copy the code from the original search page used to create the variable <b>WhereKeywordsString</b> and place it before the recordset code, note the changes I've put in bold text:

&lt;%
Keywords = Request("<b>txtLyricsSearch</b>"
MatchType = Request("<b>SelMatchType2</b>"

If Keywords &lt;&gt; "" Then
SearchFields = "<b>Lyrics</b>"
Keywords = Replace(Keywords, "'","''"
If (<b>MatchType2</b> &lt;&gt; "EXACT" Then
Keywords = Replace(Keywords, ","," "
Keywords = Replace(Keywords, ":"," "
Keywords = Replace(Keywords, ";"," "
Keywords = Replace(Keywords, "-"," "
Keywords = Replace(Keywords, "_"," "
Keywords = Replace(Keywords, "/"," "
Keywords = Replace(Keywords, "\"," "
WhereKeywordsString = " WHERE " & SearchFields & " LIKE '%"

SearchArray = Split(Keywords," "
For i = 0 to Ubound(SearchArray)
If i &gt; 0 Then
WhereKeywordsString = WhereKeywordsString & " " & MatchType & " " & SearchFields & " LIKE '%" & SearchArray(i) & "%'"
Else
WhereKeywordsString = WhereKeywordsString & SearchArray(i) & "%'"
End If
Next
Else
WhereKeywordsString = " WHERE <b>Lyrics</b> LIKE '%" & Keywords & "%'"
End If
<b>Else
WhereKeywordsString = "WHERE AlbumID &lt;&gt; -1"
End If</b>
%&gt;

Next modify the recordset in code view as follows:

&lt;%
set rsQryLyrics= Server.CreateObject("ADODB.Recordset"
rsQryLyrics.ActiveConnection = MM_TDSFdemoOLE_STRING
rsQryLyrics.Source = <b>"SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre FROM QryLyrics" & WhereKeywordsString</b>
rsQryLyrics.CursorType = 0
rsQryLyrics.CursorLocation = 2
rsQryLyrics.LockType = 3
rsQryLyrics.Open()
rsQryLyrics_numRows = 0
%&gt;

Now you can create a Comments results page in a similar fashion.

Once you have your three results pages you can have all of them link to the same detail page.

Hope this makes sense.


Regards

Owen.

Edited by - oeastwick on 08/15/2001 18:54:13

Edited by - oeastwick on 08/15/2001 18:55:48

Edited by - oeastwick on 08/15/2001 18:57:29

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
In the database you would have a table for Lyrics with two columns, one for the AlbumID and another for the Lyrics and a similar table for Comments, both much like the TracListings table in the TDSFdemo database.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Owen,

Setup lyrics with a AlbumID in it ???

this would bring serious problems with it.
for instance searching my database on Silver Machine today returns...
Records 1 to 10 of 36

So I would get like 36 copies of the same lyric comment field because it is on 36 albums that I own...

Let me explain my Hawksong Database...

There's only 1 table at the moment "Songs", going to expand it to another to house...AlbumID's to crossreference back to my music-collection and in the future (if i ever manage that) a Complete discography of Hawkwind (there's literally 100's if not 1000's recordings around), so people see the Song "High Tech Cities" and see that it was only released on 2 albums, unlike some other songs...
The table 'Songs' consists of fields:

ID, SongTitle, FirstReleased, AlbumId, Lyrics, Writer, Notes, AlphaID, LyricPage

ID is unique value, used to crosslink from my music-collection to here...

SongTitle is the Title text field.

FirstReleased is textfield holding the Album this was first recorded on.

AlbumID is the AlbumID of the album, first recording, in my music-collection Dbase, if any, most are blanc. Not using this function yet, but it'll make the first released field on the page a link.

Lyrics is the big comment field holding the song-texts.

Writer is textfield holding name(s) of persons who wrote the song

Notes is a memofield describing if it was only performed live or known under different names or other facts about it.

AlphaID, holds a Letter for my Alphabetic list, displaying all songs on 1 page with links back to top of page in between them.
see here to see what i mean:
dennisvg.homeip.net/Mysite/Music/Hawk_Songs_Alpha.asp
(people say this can be done much easier then how i set it up, ie using 26 recordsets, grabbing the A's...display them, repeat. Grabbing the B's etc...)

LyricPage has a value yes/no to determine if it is displayed on my lyricpage or on the accoustic/unknown page...
see here to see what i mean:
lyrics: dennisvg.homeip.net/Mysite/Music/LyricsList.asp
nolyrics: dennisvg.homeip.net/Mysite/Music/NoLyricsList.asp

I take it with AlbumID you meant in a seperate table, cause my database would grow VERY back with so many double information (double? most between 15 and 36 times, that is so far, still got some 30 albums incoming and i'm always on the lookout for wannahaves of Hawkwind) combining the two together would become messy and since i don't have lyrics for all my other music (atleast not like I have them for Hawkwind).
The logical step would be to take those AlbumID's in a seperate table combined with the ID of the Hawksongs, it would still be a big list, but that way i would save much space in the Dbase, space i want to use for my, ahum, booklet text table with comment fields, still have a word document floating around with some big pieces of 30+ years Hawkwind history...

Going back to your search, i have been examining the codes and I think what would happen is it would return ALL lyrics for Every track on a single album, am I correct ?
So, searching lyrics for Psy would return all entries on Psy-Power, Psychosonia, Psychosis, Psychedelia lives, Psychedelic Warlords, Psychedelic Warrior, etc and all other tracks of ALL albums these tracks are found on.

Please tell me that I misunderstood something, cause that would return like half my database...
And then it would be MUCH quicker to just scroll through that Alphabetic Song List, which btw returns 457 songs, and the last 6 or so albums i bought still have some ID=NULL entries, so i was lax cause there should be around 470-500 entries to be complete.

Ahum, the comment fields I want to search are found in a few places, in my music-collection Dbase, telling people which concert this or that live cd/video came from, in my HawkSong Dbase, telling people additional info about the songs, also known as this or only performed once, and since some tracks don't link right now, i have to add more versions of same songs anyway cause "Out of the Shadows", for instance, is also recorded as a single live track combining three or more other songs, so these need seperate entries, entries like "Out Of the Shadows / Eons / Night of the Hawks" or "TV Suicide / Back in the Box / Assassins of Allah" or "Earth Calling / Born to Go", i think you get the idea of what my Hawkzone is meant to be.

I will start on these extra searches tomorrow and try out what you suggest, I think I got a good idea of what to do. And I will certainly show you the finished result, but that has to wait till my scanner is fixed, cause alot of noimageyet.jpg references in my database right now <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

I am going to continue on it now, but my alarm goes off in 7 or so hours, so i can't do much today. Atleast I don't own all these on 6 formats, mostly 3...
ummm, yes 3, the Hawkwind first album are different recordings <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Oh, about your food for thought "for the" example, i allready get one returned with a MUCH bigger sql statement, almost 3 times as long as your example.
LOL, at most 20 or 30 albums for the same artist, i have no comments on that one <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>
the amazon example sounds really good to me, since they get more Hawkwind returned then me, but there's places with much more hawks. And some of those scare me, i met a guy through Internet who has 1600 Hawkwind items, imagine keeping track of that, i'm having trouble finding certain tracks with just 136 entries. But the whole purpose for the search was to see how many tracks and where are they, so that might be a future thing, for now it works cool, without a seperate track search.

Just hit a wall, good time to continue tomorrow...have to manually give the lyrics a album ID and, as i mentioned before, there's lyrics to tracks that i don't have yet and i will be busy for a few hours re-arranging all that, but the start is there, i just had two Dbases open next to eachother and got myself a bit confused, clear head tomorrow will probably do wonders.

With kind regards,

Dennis van Galen
Webmaster SPG Finance
KPN Telecom Holland NV

Edited by - djvgalen on 08/15/2001 23:33:16

Edited by - djvgalen on 08/15/2001 23:57:09

Edited by - djvgalen on 08/16/2001 00:22:24

Dennis,

In my previous message I made some assumptions which may have been incorrect. It's difficult to visualise the structure of your Database, coming as I do from an Art and Design background, I like pictures <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>.

Took a look around your site, and it looks like you are going off on quite a tangent, when you first contacted me it appeared that you were setting up something very similar to what I had created for the tutorial.

Can't say we have very similar tastes in music, as you probably guessed from the demo database . You could scrap the Genres table it's all Rock <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>. Seriously though, let me know when you've finished I'd be interested to see what you come up with.


Regards

Owen.

Edited by - oeastwick on 08/16/2001 01:01:11