Find a matching SQL Server record safely

To test whether a record exists, run a query for the key and then check the returned Recordset's EOF property. When the key comes from the request, pass it as an ADO parameter rather than concatenating it into SQL.

Parameterized query

SELECT emp_no, name FROM emp_m WHERE emp_no = ?

Complete Classic ASP example

Dim conn, cmd, rs, empNo
empNo = Trim(Request.QueryString("emp_no"))

Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = aConnectionString
conn.Open

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT emp_no, name FROM emp_m WHERE emp_no = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter("pEmpNo", adInteger, adParamInput, , CInt(empNo))
Set rs = cmd.Execute

If rs.EOF Then
  Response.Write "No matching record"
Else
  Response.Write "Name: " & Server.HTMLEncode(CStr(rs("name")))
End If

rs.Close
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

This example assumes adovbs.inc is included so constants such as adInteger, adParamInput and adCmdText are available. Validate that the request value is numeric before calling CInt().

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: