Any ASP experts out there?

regi

That's more likely to be the database rather than ASP. After all, ASP will probably initialise the object (ADO Muso?) which connects to the DB, and via a public method allow you to fire SQL statements at the DB. Try going through the documentation of the DB you're using.

Kali

all i can thnk of is puttin your variable your searching for within quotes.
SELECT * FROM $table_name
WHERE str_name LIKE 'john%'
is what i use with php & mysql..
but i take it you arent using mysql if your accessing it with asp (ughh)

kayos

Kali has it right here the correct syntax for the like operator is

SELECT
*
FROM
TABLENAME
WHERE
COLUMNNAME LIKE 'a%'

you prob ain't your ASP but its your SQL.

KaYoS

mewso

Thanks guys but no joy with % or *. AS far as I can tell I'm fairly certain I'm programming it correctly. There must be something odd with ASP that I don't know about. I'm accessing a MSAccess97 mdb file. I have used the same code in Access on a form to test it and it works.
I'm new to ASP but fairly familiar with Access and VB prgramming so I've got to just keep studying ASP until I figure it out.

M

mewso

Using ASP for database access on our Intranet and for the life of me I can't get ASP to recognise the 'like' SQL statement. It opens fine but returns nothing when it should be a list of names 'like a*' for example I get zilcho. Any ideas?

M

regi

Muso, wanna post the code and then we look at it?

mewso

O.k. Regi here ye go:-

I'll start with an explanation. I have a database of Staff Names, Department, E-Mail and phone Ext., location of picture. This ASP file has a form with a Name selection box containing '[A-C]', '[D-F]' Etc. This is to facilitate the like Sql statement. I can take the value of the name list box and use it with * to get names beginning with a,b or c (like [A-C]*). This Sql command works in MSAccess97. There is also a Dept. list box. and a 'Go' button. The form uses a 'get' method to return the values of the form controls to itself.
At the moment when you open the file you see the form plus a table with all the staff names. Names have being turned into email links using the email from the database and putting a mailto link behind the name(also taken from the databse) and pictures have been inserted if the picture location field in the database is not null. When the ASP file opens I need to check the following:-

1. Are the two list box values = "" then just show all records.
2. Are the two list box values = "ALL" (an option) then just show all records.
3. Is the name list box = "ALL" and the Dept list box <> "ALL" then show only records from selected dept.
4. Is the name list <>"ALL" and the Dept list = "ALL" then show only records whose names begin with 'selected name value'
5. Are both lists <> "ALL" then just show all records from chosen dept and inform the user that is what we have done.

I have all options working except for number4. Here is the code I use to come up with the needed Sql statement:-

<%
dim myconnection, rs, dept, namebeg, my_sql_string


set myconnection = Server.CreateObject("ADODB.Connection")
myconnection.open "DSN=tel_index"

if request.querystring("name")="" and request.querystring("depts")="" then
my_sql_string = "select * from t_index_for_asp order by name"

elseif request.querystring("name")="ALL" and request.querystring("depts")="ALL" then
my_sql_string = "select * from t_index_for_asp order by name"

elseif request.querystring("name")="ALL" and request.querystring("depts")<>"ALL" then
dept = "'" & request.querystring("depts") & "'"
my_sql_string = "select * from t_index_for_asp where department =" & dept

elseif request.querystring("name")<>"ALL" and request.querystring("depts")="ALL" then
namebeg = "'" & request.querystring("name") & "*'"
my_sql_string = "select * from t_index_for_asp where name like " & namebeg
elseif request.querystring("name")<>"ALL" and request.querystring("depts")<>"ALL" then
dept = "'" & request.querystring("depts") & "'"
my_sql_string = "select * from t_index_for_asp where department =" & dept
%> PLEASE NOTE ALL STAFF FROM <%= request.querystring("depts")%> ARE BEING LISTED<%
END IF

Currently number 4 gives me no records at all, not an error. Whereas the code works when used in access. And I mean all the code not just using a query. I mean creating a form and making a button press try to do what number 4 does using recordsets and it works.

M

Talliesin

Originally posted by musician:

my_sql_string = "select * from t_index_for_asp where name like " & namebeg

Is the only use of like I can find.
It should be.
my_sql_string = "select * from t_index_for_asp where name like '" & namebeg & "'"

mewso

Nope that didn't work either. Gave up and got it working using rs.filter = "name like 'A*'" (that format) where rs is the recordset. At the end of the day I'm more comfortable with recordset manipulation than Sql. Still it's an intriguing mystery the way like would not work with Sql in ASP. I could have missed something very simple but I tried alot of variations. In the end there was no point in getting bogged down.

M