It works very well for what I want to do, up until I want to give an
extra effort to make things better.
So what you're saying is, it doesn't work very well for what you *really*
want to do? Maybe it's time to move on?
That's the problem with automagical code generation tools - they try to
account for every possible circumstance, write overly-verbose code that
noone can follow, and make it even worse by appending a proprietary prefix
to the variables. Look at me, MM_MacroMedia made this! And in the end, they
don't do that one thing that the developer had in mind.
What's even worse, is that newbies attempt to do things they don't
understand, and end up trying to fix the thing don't understand by wading
through the incomprehensible code generated by the <not>helpful</not> tool.
From the code you provided, you are doing lots of LIKEs without a wildcard
when a value is entered. So you would end up with something like this...
AND (GenKStock.SingleArtist LIKE 'jo') in your query if someone typed 'jo'
in your search form.
Suggestions -
You should specify the collection in the Request object you are using.
Request.Form("field_name"), Request.QueryString("field_name"), etc....
Your test for "" is unreliable. You should check the length of the
string or...
If Not Request.Form ("title") = "" Then
.....
End If
For concatenation, "&" is the correct operator in VB - "+" can cause
some unexpected esults if the value is number.
Consider doing the Replace in your If staements to make the SQL more
readable in the code.
Also, write a function to do the replacing so you don't have to keep
writing - Replace(rsResults__TY, "'", "''") - over and over.
Bob Barrows's advice to response.write the query to make sure it works in
Access, and Aaron's advice to start simple, are probably the best two pieces
of advice should follow.
Bob Lehmann
shank said:
It was and it wasn't related to the other post. There were 2 issues and if I
could have gotten through either, I probably could have gotten through both.
The reason I don't post everything is it looks like a huge mess of code
coming through the newsgroup. And I just figured it would make things more
confusing. Here is the entire recordset created in dreamweaver. It works
fine as it is here. I know you code heads may not like dreamweaver but it's
a huge crutch for those of us that don't have the knowledge to hand code
things. It works very well for what I want to do, up until I want to give an
extra effort to make things better. For this project I have to use Access
2002. I don't like it, but that's not my choice. I have a music database
that users can search. I get titles and artists from many many sources. They
all choose to spell titles and artists their own way. That's a problem. In
an effort to make it easier on the end users, I want to remove common words
and punctuation that varies. That includes "'", """, ",", "/", " ", "&",
"the", and "and". If I remove those characters from both terms, it creates a
much better chance of relevant search results. I've done this with SQL
stored procedures and it works great. Now I have to do this with SQL in ASP
and I'm having a lot of syntax problems. I appreciate everyone's help!!! I
thought I was trying to make it easier on everyone and I guess I made it
more confusing. Sorry!
I only have to remove those characters from titles and artists fields. The
other fields I have control over the data. OK... now how do I accomplish
this feat?
thanks!
<%
Dim rsResults__T
rsResults__T = "%"
If (Request("title") <> "") Then
rsResults__T = Request("title")
End If
%>
<%
Dim rsResults__A
rsResults__A = "%"
If (Request("artist") <> "") Then
rsResults__A = Request("artist")
End If
%>
<%
Dim rsResults__C
rsResults__C = "%"
If (Request("category") <> "") Then
rsResults__C = Request("category")
End If
%>
<%
Dim rsResults__TY
rsResults__TY = "%"
If (Request("type") <> "") Then
rsResults__TY = Request("type")
End If
%>
<%
Dim rsResults__M
rsResults__M = "%"
If (Request("manuf") <> "") Then
rsResults__M = Request("manuf")
End If
%>
<%
Dim rsResults__SA
rsResults__SA = "%"
If (Request("singleartist") <> "") Then
rsResults__SA = Request("singleartist")
End If
%>
<%
Dim rsResults
Dim rsResults_numRows
Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_GenKAccess_STRING
rsResults.Source = "SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
GenKStock.SoftHard, GenKStock.Category, GenKStock.Type, GenKStock.Label,
GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM GenKStock
INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber WHERE
(GenKTitles.Title LIKE '%' + '" + Replace(rsResults__T, "'", "''") + "' +
'%') AND (GenKTitles.Artist LIKE '%' + '" + Replace(rsResults__A, "'",
"''") + "' + '%') AND (GenKStock.Category LIKE '" +
Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf LIKE '" +
Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE '" +
Replace(rsResults__TY, "'", "''") + "') AND (GenKStock.SingleArtist LIKE
'" + Replace(rsResults__SA, "'", "''") + "')"
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()
rsResults_numRows = 0
%>
