Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 26th, 2004, 09:54 AM
Friend of Wrox
 
Join Date: Jul 2004
Location: , , .
Posts: 119
Thanks: 0
Thanked 0 Times in 0 Posts
Default shoakat

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

 
Old July 26th, 2004, 12:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

 
Old July 26th, 2004, 12:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old July 26th, 2004, 03:37 PM
Friend of Wrox
 
Join Date: Jul 2004
Location: , , .
Posts: 119
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 26th, 2004, 04:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
shoakat shoakat Classic ASP Databases 9 September 2nd, 2004 06:14 PM
shoakat shoakat Classic ASP Databases 2 August 6th, 2004 09:09 PM
Shoakat shoakat Classic ASP Databases 4 August 4th, 2004 06:46 AM
shoakat shoakat Classic ASP Databases 0 August 3rd, 2004 11:07 AM
shoakat shoakat Classic ASP Databases 2 July 28th, 2004 03:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.