Access VS asp query mismatch

[bigdan]

Hi,

I have a MS Access 97 database and i'm trying to do a query with an ASP file (front page 98) for an intranet.

I built my query in access and is working fine and when i copy that one in my asp file i got an error message.

Here's my query statement:

SELECT [Host table].site, [Host table].adresse, [Host table].[Host name]
FROM [Host table]
WHERE ((([Host table].[Host name]) Like ("*" & "piex" & "*&quot));

Here's the error message:

Erreur de base de données : [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

It's working if a replace the "like" by = and i remove the *& but i have to write all the "host name" without error. The thing i want to do is to search a pattern in that field.

The final query that i want is to replace the "piex" with a variable from a html form (user input), example '%%value1%%' and search that pattern in the field "host name".


Any Help will be very appreciate!!!!!!

Dan!!!!!

 

[webspy]

first tell what are the wild chars involved in ur search text.

 

[bigdan]

What do you means by "wild chars" ?
In my mind is the star "*"
Is this what you mean?

Thanks
Dan

 

[webspy]

no in some texts having single codes.forexample "fathe's" name like that.if ur wild char does not support to the query it shows error.for that u replace the single codes with "''" double single codes before u submiting the variable
regards
webspy

 

[bigdan]

I'm not sure i understand your statement.
And what's a wild char?

But i don't have single/doubles quotes in my table/field/value.
The things that i'm trying to do is for example if you have 5 records with that hostname

civictheater
civicarena
hondacivic4door
hondacrv
toyotarav4
gmcorvette

I would like to do a search on "civic" (pattern of text) for example, and the result will be that

civictheater
civicarena
hondacivic4door

SELECT [Host table].site, [Host table].adresse, [Host table].[Host name]
FROM [Host table]
WHERE ((([Host table].[Host name])= '%%value1%%'));

The sql statement above works but to find something i have the type civicarena
and the result is civicarena only!

Thanks

Dan!!!!!

 

[jonscott8]

Try encapsulating the Like cryteria in single quotes:

Like ("'*" & "piex" & "*'&quot

That way the SQL being passed to access looks like:

SELECT field from table where field Like '*piex*'

FWIW, having the wildcard character at the front of the search expression is not optimizable. The db engine will be forced to physically scan every record in the table. This will result in performance degradation on larger systems. Jon Hawkins

 

[bigdan]

Jon,

I tried your answer but without success.

What's the best way to search a string pattern in a field if you said it's not optimize to put wildcard character at the beginning??

note that:
To write my html code/SQL statement a use frontpage98
->insert->database->query wizard

Every time when i put " (double quote) when I look at the HTML code, for a reason that i don't know he put twice and if i put nothing we don't put any "

PLEASE!!!!!HELP!!!!!!!!

Dan

 

[Databaseguy]

SELECT [Host table].site, [Host table].adresse, [Host table].[Host name]
FROM [Host table]
WHERE ((([Host table].[Host name]) Like (" & "*" & YourVar & "*" & &quot))".

It was the quotes that were messin' ya up.

 

[FengShui1998]

BigDan,

See this post. thread215-117668


Fengshui1998

 

[bigdan]

Thank's Fengshui1998

It's working with %

Thanks to every body on this very useful Site


BigDan