I'm working with a SQL db to create usernames and passwords.  The user 

has to provide their SSN to check if they exist in the db so we can 

access more information about them.  If the user gives an invalid SSN 

or it doesn't exist in the db, how can I tell them the information 

provided it's in the db.  I'm taking the data from the form by request 

and then opening the connection, then I use 



SQLFind = SELECT * FROM db WHERE ssn = '" & SSN & "';"  

Set rsFind = Conn.Execute(SQLFind)



Yes that is right syntax, the SSN field is a varchar.  So if the that 

select statement doesn't get any information from the db, I want to let

the user know that their infor was invalid, but when I try to do that, 

or even display an if/else  that checks if there is a ssn or not, it 

gives me a RS error.  So my question is, does anyone know how to find 

out if the select statement has no data in it.  I tried the Find method

and it didn't work.  It told me that I could go backwards  on the rs.  

Please let me know if anyone can help out



JR Shortall

Elon College, NC

----------------------------------------

JR Shortall

Email: John.Shortall@e...

President, ECRH

http://www.elon.edu/ecrh

SQLFind = SELECT * FROM db WHERE ssn = '" & SSN & "';"

 Set rsFind = Conn.Execute(SQLFind)



if rsFind.eof then

    response.write "No user"

end if

You are missing an oppening quote:

Incorrect -

SQLFind = SELECT * FROM db WHERE ssn = '" & SSN & "';"



Correct -

SQLFind = "SELECT * FROM db WHERE ssn = '" & SSN & "';"



Hope this helps.

Peter

JR,



After you execute the query, simply do the following test...



If Not (rsFind.BOF And rsFind.EOF) Then

	...code to do whatever you need for a valid record

	...access the DB columns in the normal way: rsFind("columnname")

Else

	...code to tell the user there's no record

End If



If you try to test on rsFind("ssn"), and there is no record, you'll get the

standard "can't find that ordinal blah blah" error from VBScript.



Feel free to email if you have any more questions.



-Peter



p.s. How's the roller hockey?



-----Original Message-----

From: JR Shortall [mailto:John.Shortall@e...]

Sent: Monday, January 22, 2001 1:49 PM

To: ASP Databases

Subject: [asp_databases] Finding a record in a recordset





I'm working with a SQL db to create usernames and passwords.  The user

has to provide their SSN to check if they exist in the db so we can

access more information about them.  If the user gives an invalid SSN

or it doesn't exist in the db, how can I tell them the information

provided it's in the db.  I'm taking the data from the form by request

and then opening the connection, then I use



SQLFind = SELECT * FROM db WHERE ssn = '" & SSN & "';"

Set rsFind = Conn.Execute(SQLFind)



Yes that is right syntax, the SSN field is a varchar.  So if the that

select statement doesn't get any information from the db, I want to let

the user know that their infor was invalid, but when I try to do that,

or even display an if/else  that checks if there is a ssn or not, it

gives me a RS error.  So my question is, does anyone know how to find

out if the select statement has no data in it.  I tried the Find method

and it didn't work.  It told me that I could go backwards  on the rs.

Please let me know if anyone can help out



JR Shortall

Elon College, NC

----------------------------------------

JR Shortall

Email: John.Shortall@e...

President, ECRH

http://www.elon.edu/ecrh