Checking the existence of a record in MSSQL ASP

Some time we will check or match the presence of a record in a table. We want to know here the matching condition and get the true or false value for further processing.

For example in a signup form we are asking the member to enter their preferred userid and password. So once the user submits the details we have to check for the userid if already exist ( before ). This is to keep the userids unique in our table. So before we add the member details to the table we can display an error message or validation message to the visitors saying the userid is already taken.

For matching of records we will use one simple table and it is available at the end of this page to download and create table in MSSQL database (using import data)

We will have our MSSQL connecting string at the root of the server and we will use it in our script.

We will have our record set object rs and through this we will access our records . Our sql query will be like this

select emp_no,name from  emp_m where     emp_no='145'

We have used SQL where clause to match the record for emp_m table.

Of our record set object the end of the record property rs.EOF will be true if there is no record found. So we will use one if condition to check the status of rs.EOF . If the matching record found then the rs.EOF will be true and we will display the name of the employee and if record is not found then we will display that message by using else condition. Here is the code. Note that we have our conn.asp file included at the starting of the page with the connection string inside it. So the variable aConnectionString is already declared and assigned the connecting string values.

Dim conn,rs
Set conn=Server.CreateObject("ADODB.Connection")
conn.Mode=adModeRead
conn.ConnectionString = aConnectionString
conn.Open
Set rs =Server.CreateObject("ADODB.Recordset")
rs.open "select emp_no,name from  emp_m where     emp_no='145'", conn

if rs.EOF Then 
Response.Write "No Records exist"
Else 
Response.Write "There is a record with name " & rs("name")
End if 
Set rs = Nothing
conn.Close
Set conn = Nothing

Here is the table with data in CSV format. You can create table using this

"emp_no","name","desg","dept","pw"
145,"John Reid","Manager","Sales","test8"
245,"Peter Horn","VP","Finance","test"
175,"Rabin Pack","Manager","Sales","test"
187,"Ran Keth","VP","Finance","test"
180,"Arvin Jones","Manager","Finance","test"
179,"Kethi Rain","VP","Sales","test"
174,"Reck Stick","Assistant","Finance","test"
173,"Brick Jon","Technician","Production","test"
172,"Marry Tod","Assistant","Production","test"
170,"Avin Car","VP","Production","test"

Connecting MSSQL SELECT records New Table
Display Identity Inserted record ID Row count
ASP Home

Searching for a matching record in a table of MSSQL in ASP