Advanced SQL commands

[PeterMac]

SELECT * FROM people WHERE last_name like 'mac*';

Why does this work in Access but not in SQL passed to access from ASP code ?

 

[link9]

Is that an actual response.write of your sql statement? Or is it what you **think** is being passed?

If not, then response.write your sql statement (which will give you what is actually being passed) and see if it is more obvious --

My suspicion is that your syntax for getting the single ticks into the statement is flawed --

You might also want to post here your code that creates your SQL statement --

 

[PeterMac]

Here is the code and yes it is a response write result that shows this on the top of my web page... it's complicated for sure, but I am trying to build a "fuzzy" seach on the last name, so that they can use the like command as well as the exact equals on the WHERE clause...

strQuery = "SELECT * FROM people WHERE "

if strPrefix = "ON" OR strSuffix = "ON" then
strStar = "*"
strOperator = " like "
else
strOperator = "="
strStar = ""
End if

if strLastName <> &quot;&quot; then
if strPrefix = &quot;ON&quot; then
if strSuffix = &quot;ON&quot; then
QueryAdd1 = &quot;last_name &quot; & strOperator & &quot;'&quot; & strStar & strLastName & strStar & &quot;'&quot;
else
QueryAdd1 = &quot;last_name &quot; & strOperator & &quot;'&quot; & strLastName & strStar & &quot;'&quot;
end if
else
QueryAdd1 = &quot;last_name &quot; & strOperator & &quot; '&quot; & strLastName & &quot;'&quot;
end if

strQuery = strQuery & QueryAdd1
if strFirstName <> &quot;&quot; then
QueryAdd2 = &quot; AND first_name = '&quot; & strFirstName & &quot;'&quot;
strQuery = strQuery & QueryAdd2
end if
Else
if strFirstName <> &quot;&quot; then
QueryAdd2 = &quot;first_name = '&quot; & strFirstName & &quot;'&quot;
strQuery = strQuery & QueryAdd2
end if
end if

strQuery = strQuery & &quot;;&quot;
%> <%= strQuery %> <%
Set objRS = objConn.Execute(strQuery)

if objRS.EOF then
'response.redirect &quot;../messages/no_records.asp&quot;
end if

 

[link9]

Ok -- and what is the error message you are getting???

 

[PeterMac]

no error message, just that the EOF returns true, when I know that there is data to be returned...

 

[alistairpaul]

are you querying against SQL Server, or is it MS Access? the &quot;like&quot; wildcard for SQL is actually %, instead of *.

also - if you want to do a little more efficient search, do the following:

SELECT * FROM people WHERE last_name like 'mac*'

lastnamevariable = &quot;mac&quot;
sqlstring = &quot;SELECT * FROM people WHERE left(last_name, &quot;
sqlstring = sqlstring & cstr(len(lastnamevariable))
sqlstring = sqlstring & &quot = 'mac'&quot;

will output the following:

SELECT * FROM people WHERE left(last_name, 3) = 'mac'

This will work in SQL Server, and SHOULD work in access, but truthfully, I haven't tried it. I've been told that left(fieldname) searches will be faster than a &quot;like&quot; because of how it searches the data.

 

[PeterMac]

Thanks, I'll try the left thing... I am doing this in Access as it is being built, but it will eventually be moving to SQL 7

 

[chiph]

Since you're eventually moving to SQL 7, I strongly suggest that you develop on MSDE and not MS Access. As you've seen, the SQL is slightly different in Access. If you were to use MSDE, not only is the SQL the same, but the database files themselves use the same format as SQL 7. You could do a simple file copy from your development box to your production box.

Chip H.