Searching for a matching record in a table of MSSQL in 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"

Be the first to post comment on this article :





Post your comments , suggestion , error , requirements etc here .




HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer