Prev Next

ADO Parameter

The Parameter object in ADO is used to define input, output, or return parameters for a Command object. Parameters enhance security by preventing SQL injection and providing flexibility in executing queries and stored procedures.

Key Topics

Using Parameters in Queries

Example

<%
    Dim conn, cmd
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=myUser;Password=myPassword;"

    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = "SELECT * FROM Employees WHERE Department = ?"
    cmd.Parameters.Append cmd.CreateParameter("Department", 200, 1, 50, "IT")

    Dim rs
    Set rs = cmd.Execute

    Do Until rs.EOF
        Response.Write("Name: " & rs("EmployeeName") & "<br>")
        rs.MoveNext
    Loop

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

Explanation: This example demonstrates using a parameterized query to retrieve employees from the IT department securely.

Parameters in Stored Procedures

Example

<%
    Dim conn, cmd
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=myUser;Password=myPassword;"

    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = "usp_GetEmployee"
    cmd.CommandType = 4 ' Stored Procedure

    cmd.Parameters.Append cmd.CreateParameter("@EmployeeID", 3, 1, , 101)

    Dim rs
    Set rs = cmd.Execute

    If Not rs.EOF Then
        Response.Write("Name: " & rs("EmployeeName") & "<br>")
    End If

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

Explanation: This example shows how to use a parameter with a stored procedure to fetch specific employee details.

Key Takeaways

  • The Parameter object defines input, output, or return parameters for a Command object.
  • Parameters improve security by preventing SQL injection.
  • They are essential for executing parameterized queries and stored procedures.
Prev Next