help writing a query

Hi I was wondering if anyone could help. I have a database which I need
to query but I am not sure how to do it.

There are 3 fields in the table called CONTACTPARAMETER


I want the user to input their username (their email address) and
password in order to login. Here is a sample of the data in the

user_id | | 00510
password | 123456 | 00510

The form fields are user_id and password.



PS I am using coldfusion
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This is how you can do it in cf against an Oracle DB:

<cfquery name="loginQuery" dsn="yourdsn">
select cp1.serialnumber from contractparameter cp1, contractparameter cp2
where cp1.user_id = '#FORM.user_id#'
and cp1.serialnumber = cp2.serialnumber
and cp2.password = '#FORM.password#'

<cfif loginQuery.recordCount eq 1>
  Your logged in!
sorry invalid username or password


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
try this, where strEmail is a user e-mail and strPWD is a password for that user:


set rst=connObj.Execute(sSQL)

if not (rst.eof and rst.bof) then

'user exists in the database
'no user found
end if
Slightly off topic:

I would STRONGLY suggest to search both strEmail and strPWD for single quotes (') and replace them with double (''). Code in VBScript would look like this
strEmail = Replace(strEmail, "'", "''")

Otherwise your code would be vulnerable to SQL injection.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

David WilliamsSoldier of fortuneCommented:
YZlat, your query will fail; you can't have any rows where PARAMETERNAME = both user_id and password!

CheekyCJ has the syntax right.

Note though, there is a presumption that the serial_number is the same for all instances of the same user? Is this valid? Otherwise, there is no way of relating a username and password to each other.

FWIW, your table design is somewhat curious; why not have the userid and password in one row?
billythehamsterAuthor Commented:
hi thanks for the input i will give it a try when I am in the office on monday. in response to your question the table design is not mine. i am building a web interface for another persons product which has a VB front end.
billythehamsterAuthor Commented:
the database is MSSQL 2000 does that make a difference?
David WilliamsSoldier of fortuneCommented:
That won't make any difference; the SQL query above is standard SQL and works with any SQL-compliant database.
I agree, I do question the design a bit :-)

My code should work in MSSQL 2000, let us know if you run into any problems

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.