ASP/VB

Report · Jul 03, 2006

Dear All,
well, it used to work. But no longer. Since upgrading to DW8, all hell has
broken loose with the SQL.

This used to search multiple fields. For example, it used to find 'Smith'
(or Smithson) in the name1 field using the variable MMFreet, but no longer.
On the other hand I've changed it so often in the last few hours I no longer
know which way is up.

Also, why does MMFreet have to have quote marks, but only for the second and
third iterations. Any clues?

Craig

SELECT subID, org, LEFT(describe,86) AS intro, name1, addrs, town, cat,
catsub
FROM subs
WHERE ((cat = MMColParam AND catsub = MMSub) OR (org LIKE MMLtr%) OR (org
LIKE %MMFreet% OR name1 LIKE '%MMFreet%' OR town LIKE '%MMFreet%') AND
show=-1)
ORDER BY org

Report · Jul 04, 2006

Quotes around the search value parameter mean it's checking against a text
based value in the database.
This leads me to believe that either your "org" column needs qoutes around
it too, or that you need to use a numeric value instead of the text value
that will be in MMFreet parameter.

Cheers,
Rob
http://robgt.com/ [Tutorials and Extensions]
Firebox stuff: http://robgt.com/firebox
Skype stuff: http://robgt.com/skype
Dell stuff: http://robgt.com/dell
SatNav stuff: http://robgt.com/satnav

Report · Jul 04, 2006

Rob,
I realise that the quote marks represent text. And always have done.

The org column is text, as is MMFreet, name 1, town etc. The issue is
partially that the first instance of %MMFreet% cannot have quotes in order
to work, but the next instances must have quotes in order to work.
Or another way to describe it is that any search for %MMfreet% will ONLY
work when searching in one place, i.e org, but not when searching org and
then name1 and then town, etc.
Whereas it used to work in DWMX just fine.

I'm getting really confused and just a tad annoyed. Why change things when
they're not broke?

Craig

"RobGT" <rob@lighthouseuk.removeme.net> wrote in message
news:e8dpeg$k1a$1@forums.macromedia.com...
> Quotes around the search value parameter mean it's checking against a text
> based value in the database.
> This leads me to believe that either your "org" column needs qoutes around
> it too, or that you need to use a numeric value instead of the text value
> that will be in MMFreet parameter.
>
> Cheers,
> Rob
> http://robgt.com/ [Tutorials and Extensions]
> Firebox stuff: http://robgt.com/firebox
> Skype stuff: http://robgt.com/skype
> Dell stuff: http://robgt.com/dell
> SatNav stuff: http://robgt.com/satnav
>
>

Report · Jul 05, 2006

Something is not making sense to me here.
are you saying that Dreamweaver is removing the quotes that you have added
around that particular parameter in the code?

Or something else?

Also, when you say your search doesn;t work when searching multiple
columns - should it return one or more results based on your search
criteria, or does it actually throw an error, or something else again?

Cheers,
Rob
http://robgt.com/ [Tutorials and Extensions]
Firebox stuff: http://robgt.com/firebox
Skype stuff: http://robgt.com/skype
Dell stuff: http://robgt.com/dell
SatNav stuff: http://robgt.com/satnav

Report · Jul 05, 2006

Rob,
no, DW isn't removing the quotes - I have to add them to make it work
partially, otherwise DW produces an error.

When searching I should get a result from at least one of the columns. For
example, if MMFreet was equal to Vic, I ought to get at least a name from
name1 (Victoria), an organisation like (Victoria and Albert) and possibly
even a town (Vicksburg).

I'm guessing that for some reason it doesn't like the structure of the AND /
OR parts. In the sense that if I only search one column, I get a result, but
if I search multiple columns I get no result.

Which is confusing because I used to get results from MX with the same code.

Any clues desperately appreciated.
Craig

"RobGT" <rob@lighthouseuk.removeme.net> wrote in message
news:e8g4h4$h4m$1@forums.macromedia.com...
> Something is not making sense to me here.
> are you saying that Dreamweaver is removing the quotes that you have added
> around that particular parameter in the code?
>
> Or something else?
>
> Also, when you say your search doesn;t work when searching multiple
> columns - should it return one or more results based on your search
> criteria, or does it actually throw an error, or something else again?
>
> Cheers,
> Rob
> http://robgt.com/ [Tutorials and Extensions]
> Firebox stuff: http://robgt.com/firebox
> Skype stuff: http://robgt.com/skype
> Dell stuff: http://robgt.com/dell
> SatNav stuff: http://robgt.com/satnav
>
>

Report · Jul 05, 2006

The 8.02 significantly changed the recordset code and switched from using
the recordset object alone to using a command object to return a recordset
object.

Check the source DW puts on your page; possibly post here for everybody.

"Craig" <csintheuk@hotmail.com> wrote in message
news:e8gbrd$pim$1@forums.macromedia.com...
> Rob,
> no, DW isn't removing the quotes - I have to add them to make it work
> partially, otherwise DW produces an error.
>
> When searching I should get a result from at least one of the columns. For
> example, if MMFreet was equal to Vic, I ought to get at least a name from
> name1 (Victoria), an organisation like (Victoria and Albert) and possibly
> even a town (Vicksburg).
>
> I'm guessing that for some reason it doesn't like the structure of the AND
> / OR parts. In the sense that if I only search one column, I get a result,
> but if I search multiple columns I get no result.
>
> Which is confusing because I used to get results from MX with the same
> code.
>
> Any clues desperately appreciated.
> Craig
>
>
>
> "RobGT" <rob@lighthouseuk.removeme.net> wrote in message
> news:e8g4h4$h4m$1@forums.macromedia.com...
>> Something is not making sense to me here.
>> are you saying that Dreamweaver is removing the quotes that you have
>> added around that particular parameter in the code?
>>
>> Or something else?
>>
>> Also, when you say your search doesn;t work when searching multiple
>> columns - should it return one or more results based on your search
>> criteria, or does it actually throw an error, or something else again?
>>
>> Cheers,
>> Rob
>> http://robgt.com/ [Tutorials and Extensions]
>> Firebox stuff: http://robgt.com/firebox
>> Skype stuff: http://robgt.com/skype
>> Dell stuff: http://robgt.com/dell
>> SatNav stuff: http://robgt.com/satnav
>>
>>
>
>

Report · Jul 05, 2006

Hi Craig,
Can you post your latest SQL (include the whole recordset) code again here
please - I have lost the original post from my newsreader.

Cheers,
Rob
http://robgt.com/ [Tutorials and Extensions]
Firebox stuff: http://robgt.com/firebox
Skype stuff: http://robgt.com/skype
Dell stuff: http://robgt.com/dell
SatNav stuff: http://robgt.com/satnav

Report · Jul 06, 2006

Hi Rob,
thanks for taking the time, here's the code again.

Craig

SELECT subID, org, LEFT(describe,86) AS intro, name1, addrs, town, cat,
catsub
FROM subs
WHERE ((cat = MMColParam AND catsub = MMSub) OR (org LIKE MMLtr%) OR (org
LIKE %MMFreet% OR name1 LIKE '%MMFreet%' OR town LIKE '%MMFreet%') AND
show=-1)
ORDER BY org

<%
Dim rsDir
Dim rsDir_cmd
Dim rsDir_numRows

Set rsDir_cmd = Server.CreateObject ("ADODB.Command")
rsDir_cmd.ActiveConnection = MM_connNorth_STRING
rsDir_cmd.CommandText = "SELECT subID, org, LEFT(describe,86) AS intro,
name1, addrs, town, cat, catsub FROM subs WHERE ((cat = ? AND catsub = ?) OR
(org LIKE ?) OR (org LIKE ? OR name1 LIKE '%MMFreet%' OR town LIKE
'%MMFreet%') AND show=-1) ORDER BY org"
rsDir_cmd.Prepared = true
rsDir_cmd.Parameters.Append rsDir_cmd.CreateParameter("param1", 5, 1, -1,
rsDir__MMColParam) ' adDouble
rsDir_cmd.Parameters.Append rsDir_cmd.CreateParameter("param2", 5, 1, -1,
rsDir__MMSub) ' adDouble
rsDir_cmd.Parameters.Append rsDir_cmd.CreateParameter("param3", 200, 1, 255,
rsDir__MMLtr + "%") ' adVarChar
rsDir_cmd.Parameters.Append rsDir_cmd.CreateParameter("param4", 200, 1, 255,
"%" + rsDir__MMFreet + "%") ' adVarChar

Set rsDir = rsDir_cmd.Execute
rsDir_numRows = 0
%>

"RobGT" <rob@lighthouseuk.removeme.net> wrote in message
news:e8glgn$8i9$1@forums.macromedia.com...
> Hi Craig,
> Can you post your latest SQL (include the whole recordset) code again here
> please - I have lost the original post from my newsreader.
>
> Cheers,
> Rob
> http://robgt.com/ [Tutorials and Extensions]
> Firebox stuff: http://robgt.com/firebox
> Skype stuff: http://robgt.com/skype
> Dell stuff: http://robgt.com/dell
> SatNav stuff: http://robgt.com/satnav
>
>

Report · Jul 07, 2006

You need to be sure that the way you have braced your SQL statements will
return the results you require.

Your SQL says, find this:
(cat = MMColParam AND catsub = MMSub)

OR
Find this:
(org LIKE MMLtr%) OR (org LIKE %MMFreet% OR name1 LIKE '%MMFreet%' OR town
LIKE '%MMFreet%')

AND find this:
AND show=-1

If you know that the above will return results from your test searches, but
it is not, then you need to look closer at the SQL structure.

You previously mentioned that the org column is a text based column, in
which case, the SQL should look like this:

SELECT subID, org, LEFT(describe,86) AS intro, name1, addrs, town, cat,
catsub
FROM subs
WHERE ((cat = MMColParam AND catsub = MMSub) OR (org LIKE 'MMLtr%') OR (org
LIKE '%MMFreet%' OR name1 LIKE '%MMFreet%' OR town LIKE '%MMFreet%') AND
show=-1)
ORDER BY org

Text columns must be quoted.
If that doesn't work, I can't immediately see any other reason why?

If you've already tried that, then i'm sorry but I'm a bit stumped!
I hope this helps.
Cheers,
Rob
http://robgt.com/ [Tutorials and Extensions]
Firebox stuff: http://robgt.com/firebox
Skype stuff: http://robgt.com/skype
Dell stuff: http://robgt.com/dell
SatNav stuff: http://robgt.com/satnav