|
| |
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
 |
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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)
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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)?????
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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)
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
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?
< Message edited by rdouglass -- 6/13/2006 10:46:40 >
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
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%'
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
connipu
Posts: 17
Joined: 10/18/2005
Status: offline
|
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
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
)
)
