SQL WILDCARDS IN ASP - RULES? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

connipu -> SQL WILDCARDS IN ASP - RULES? (5/31/2006 17:09:14)

I KNOW THAT THE ACCESS SQL WILDCARD (*) WHEN USED IN ASP MUST BE CHANGED TO (%).
IS IT POSSIBLE TO HAVE A WILDCARD AT THE BEGINNING OF A STATEMENT, LIKE THIS:
SQL = SQL & "WHERE bname Like '%"& str2 &"' " (WOULD LIKE TO LOOK FOR WORD AT THE END OF THE STRING, EXAMPLE: *CONTROL)

I CAN'T GET ANY RESULTS AND YET I USE THE WILDCARD IN THE OTHER FOLLOWING WAYS WITH GOOD RESULTS:

SQL = SQL & "AND bname Like '%"& str2 &"%' " (LOOKING FOR WORD ANYWHERE IN STRING)
SQL = SQL & "WHERE bname Like '"& str1 &"%' " (LOOKING FOR WORD AT BEGINNING OF STRING POSSIBLE FOLLOWED BY ADDITIONAL TEXT, EXAMPLE: CONTROL*)

IS THERE SOME QUIRK USING ASP THAT DOES NOT PERMIT USE OF THE WILDCARD AT THE BEGINNING OF THE STATEMENT? PLEASE HELP - I CAN'T SEEM TO FIND AN ANSWER.



BeTheBall -> RE: SQL WILDCARDS IN ASP - RULES? (5/31/2006 17:46:57)

Your syntax is correct. Are you sure there are records that meet the criteria?



Spooky -> RE: SQL WILDCARDS IN ASP - RULES? (5/31/2006 22:15:31)

After codng the SQL string, use this code to ensure the complete syntax is correct : 

response.write SQL 
response.end



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/6/2006 10:52:28)

Yes, I have several records that meet the criteria. Could it have something to do with the fact that I am using an application called ASP EXPLORE to mimic a web server/browser for this desktop application. We had trouble with IIS (security issues) and therefore decided to use this application. I can not find any other reason for the fact that I can not use a wildcard as the first character to indicate a search for that keyword as the last word in the product name. i.e. *Control (lawn weed control is one of the product names)



Spooky -> RE: SQL WILDCARDS IN ASP - RULES? (6/6/2006 11:00:17)

Have you written the SQL to ensure the syntax is correct?



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/6/2006 14:44:44)

What do you mean have I written the SQL - refer to my first posting (sql shown)?????



Spooky -> RE: SQL WILDCARDS IN ASP - RULES? (6/6/2006 18:32:31)

As per my post above, you need to response write the finished SQL string.
Above, you show the SQL string prior to processing. We need to see it after. 

response.write SQL 
response.end



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/7/2006 11:56:51)

Thanks - I did not know I could display the SQL statement using response.write. Still no change. As a matter of fact when using the response.wrtie for the SQL it shows the SQL code but does not execute it. I tried this for even those that were working (producing results). When I commented out the response.write for the SQL code - the ones that were working are still working but the code I could not get to work still does not work.

SQL = SQL & "WHERE bname Like '%"& str2 &"' " (Looking for the word Control at the end of the string, EXAMPLE: *CONTROL)



rdouglass -> RE: SQL WILDCARDS IN ASP - RULES? (6/7/2006 13:23:54)

quote:

EXAMPLE: *CONTROL)


Are you actually putting the * in there? That is where the problem is.

See you're using the ODBC drivers so % is your wildcard char so it's already in there per your 'SQL=' statement.

That help any? (Or am I just interjecting where not needed.[8|])



Spooky -> RE: SQL WILDCARDS IN ASP - RULES? (6/7/2006 16:11:12)

Can you post the actual result of the SQL string that is written to the page?
(roger - dont leave me! [;)])



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/12/2006 8:40:52)

No, I am not using the asterisk but rather the percent sign as required. Thanks anyway.



rdouglass -> RE: SQL WILDCARDS IN ASP - RULES? (6/12/2006 10:23:29)

quote:

Can you post the actual result of the SQL string that is written to the page?


As Spooky suggests, can you do this?



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/12/2006 12:05:25)

Yes. Here are the results of the SQL statement


SELECT * FROM tblCPS WHERE bname Like '%control'



rdouglass -> RE: SQL WILDCARDS IN ASP - RULES? (6/12/2006 13:09:02)

quote:

I am using an application called ASP EXPLORE


Boy, that SQL looks OK to me. Have you approached it with that in mind? In terms of the ASP engine, how compatible is it?

Are you using Access?

Are you using ODBC drivers or OLE?



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/13/2006 8:32:56)

Here is the answer to your questions. Yes, I know the SQL is correct. As for the ASP engine - we have asked that question but have not gotten a response.. The database was originally created in ACCESS but does not require an occurrence of ACCESS to run inside ASP. That is one of the reasons we went with this application rather than IIS.

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"



rdouglass -> RE: SQL WILDCARDS IN ASP - RULES? (6/13/2006 8:46:17)

quote:

conn.Provider="Microsoft.Jet.OLEDB.4.0"


Is that all there is there? AFAIK you generally need more there; something like:

connDSN="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/mydatabase.mdb")
conn.open connDSN

or whatever the path to your db is. That help any?



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/13/2006 11:32:14)

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "db\nspirs.mdb"
set rs = Server.CreateObject("ADODB.recordset")



rdouglass -> RE: SQL WILDCARDS IN ASP - RULES? (6/13/2006 11:54:08)

Can you return *anything* from that recordset?

If you can, I have no idea what the problem is if it isn't the ASP engine. Have you tried your same code in an IIS environment to rule it out?



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/13/2006 12:16:03)

Yes. Returns all the required records (have tested it numerous times). No did not try the code which includes the wildcard in the beginning of the keyword with IIs .



rdouglass -> RE: SQL WILDCARDS IN ASP - RULES? (6/13/2006 12:43:02)

quote:

SELECT * FROM tblCPS WHERE bname Like '%control'


Can we revisit that for a moment? What field type is that? Just a text field?

Does it return any records if you wildcard both ends?

SELECT * FROM tblCPS WHERE bname Like '%control%'



connipu -> RE: SQL WILDCARDS IN ASP - RULES? (6/14/2006 8:28:17)

It is a text field. Yes. It works fine returing records in both cases (wildcards at each end or wildcard just at the end).



rdouglass -> RE: SQL WILDCARDS IN ASP - RULES? (6/14/2006 9:21:33)

quote:

It works fine returing records in both cases


Hmm...but does not when using just in the front.

I suspect there may be trailing spaces at the end of those fields; either the search text or the db field text. Either way you could trim the field before comparison.

mySQL = "SELECT trim([bname]) AS newbname WHERE newbname LIKE '%" & trim(str2 & "") & "'"

That any better?



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125