shoakat

shoakat · July 26th, 2004, 09:54 AM

Hello Sir, In my asp page when the user enters firstName
in control box, If he types in two alphabets, I should get
all the names from the data base starting with these two alphabets

I am trying to use the store procedure in my sql statements, but gives me an error. Can anyone tell me how to use sql statement so that when user enters two or three alphabets, it returns all the names startings with them

stu9820 · July 26th, 2004, 12:46 PM

Can you be more specific about your project? Are you using MS Access or MS SQL Server. Can you post your code. etc.

happygv · July 26th, 2004, 12:57 PM

You can try that with LIKE operator.

Say your ASP Variable strName hold those 2/3 characters type by the user. Then your sql statement construction should look like this.

Code:

strSql = "Select <FIELD_LIST,..,..,> from TABLE where NAMEFIELD like '" & strName & "%'"

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

shoakat · July 26th, 2004, 03:37 PM

ALTER Procedure get_shortNames(@FirstName varchar(40), @LastName varchar(45))
AS
Select FirstName, LastName, City, EmployeeID
From Employees
where (FirstName LIKE '@FirstName%' OR @FirstName ="")
AND (LastName LIKE '@LastName%' OR @LastName ="")
I am trying to pass this store procedure in an asp page. when the
user enters first name like "NA" the name starting with "NA" will
return all the names

happygv · July 26th, 2004, 04:36 PM

Change it to this.

Code:

ALTER Procedure get_shortNames(@FirstName varchar(40)=NULL, @LastName varchar(45)=NULL)
AS
If @FirstName is NOT NULL and @LastName is NOT NULL
BEGIN -- Display only Names stating with @FirstName and @LastName
    Select FirstName, LastName, City, EmployeeID 
    From Employees
    where (FirstName LIKE @FirstName + '%' AND LastName LIKE @LastName + '%')
END
ELSE -- Display all names, if nothing is passed
BEGIN
    Select FirstName, LastName, City, EmployeeID 
    From Employees
END

Cheers!

_________________________
- Vijay G
Strive for Perfection