经典ASP参数化查询报错：@P1附近语法不正确

Let me break down what's going wrong here and how to fix it properly—you're on the right track with parameterization, but the approach you're using isn't how SQL parameters work.

The Core Issue #

You're trying to pass an entire chunk of SQL conditions (qry_str) as a single parameter, but SQL parameters can only replace values, not structural parts of the query (like AND clauses or entire conditional blocks). When your code runs, the database sees something like:

SELECT * FROM VW_RESULTS_2 WHERE 0=0 '@P1' ORDER BY ...

That extra quote around the parameter value breaks the SQL syntax, hence the error.

Correct Approach: Build Parameterized Query Step-by-Step #

Instead of passing all conditions as one parameter, you'll build your SQL string with separate placeholders for each value, then add a corresponding parameter for each placeholder. Here's how to adjust your code:

1. Initialize your base query and parameter collection
2. For each filter condition:
   • Append the conditional clause (like AND TN_ID = ?) to your SQL string
   • Add a parameter with the correct data type and value

Here's the revised code:

' Initialize base query
Dim sql : sql = "SELECT * FROM VW_RESULTS_2 WHERE 0=0 "
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = tadsdb

' Handle serial_number filter (numeric type example)
If serial_number <> "" Then
    sql = sql & " AND TN_ID = ?"
    ' Use adDouble if TN_ID is a decimal; adjust type based on your column
    cmd.Parameters.Append cmd.CreateParameter("@TN_ID", adDouble, adParamInput, , serial_number)
End If

' Handle base filter (numeric type example)
If base <> "" Then
    sql = sql & " AND BASE_ID = ?"
    cmd.Parameters.Append cmd.CreateParameter("@BASE_ID", adDouble, adParamInput, , base)
End If

' Handle engineer filter (string type)
If engineer <> "" Then
    sql = sql & " AND POC_USERID = ?"
    cmd.Parameters.Append cmd.CreateParameter("@POC_USERID", adVarChar, adParamInput, 50, engineer)
End If

' Handle damage_area filter (numeric type example)
If damage_area <> "" Then
    sql = sql & " AND DAMAGE_NOUN_ID = ?"
    cmd.Parameters.Append cmd.CreateParameter("@DAMAGE_NOUN_ID", adDouble, adParamInput, , damage_area)
End If

' Handle part_number LIKE filter (string with wildcard)
If part_number <> "" Then
    sql = sql & " AND PART_NUMBER LIKE ?"
    ' Move the % wildcards into the parameter value, not the SQL string
    cmd.Parameters.Append cmd.CreateParameter("@PART_NUMBER", adVarChar, adParamInput, 100, "%" & part_number & "%")
End If

' Add sort order
sql = sql & sortorder(" ORDER BY ")

' Assign the final SQL to the command
cmd.CommandText = sql

' Execute the query (example)
Set rs = cmd.Execute

Key Notes: #

• Match Data Types: Make sure the parameter type (adDouble, adVarChar, etc.) matches the data type of your database column. This prevents implicit conversion issues and adds another layer of safety.
• LIKE Wildcards: Always put wildcards in the parameter value, not the SQL string. This keeps the query structure clean and avoids any risk of injection through the wildcard characters.
• Avoid Dynamic SQL Fragments: Never pass SQL keywords or clause structures as parameters—only pass the actual values your users input.

内容的提问来源于stack exchange，提问作者user8053788