是否有可能使用命名参数时，命令类型为adCmdText？ [英] Is it possible to use named parameters when command type is adCmdText?

问题描述

我试图使用命名参数通过Adodb.Command但没有运气下面的查询。

I'm trying to use named parameters with following query through Adodb.Command but no luck.

Select * From mytable Where col1 = Lower(@param1) And col2 = Upper(@param2) And col3 = @param1

在code我用类似的东西。

The code I used something like that.

Dim cmd
Set cmd = Server.CreateObject("Adodb.Command")
cmd.NamedParameters = True
cmd.CommandType = adCmdText
cmd.CommandText = "Select * From mytable Where col1 = Lower(@param1) And col2 = Upper(@param2) And col3 = @param1"
cmd.Parameters.Append cmd.CreateParameter("@param1", adVarchar, adParamInput, 20, "some text 1")
cmd.Parameters.Append cmd.CreateParameter("@param2", adVarchar, adParamInput, 20, "some text 2")
Set cmd.ActiveConnection = cn
Dim rs
Set rs = cmd.Execute

的问题是，这会导致对RDBMS侧的错误。

The problem is that, this causes an error on RDBMS side.

Microsoft OLE DB Provider for SQL Server error '80040e14' 

Must declare the scalar variable "@param1".

有没有任何麻烦，如果查询是一个存储过程调用。所以，我想的 namedparameters 会影响它调用存储过程只有命令对象。
当我使用SQL Server Profiler跟踪查询，只见刚才以下查询跑的（注意，没有参数声明，没有赋值）的。

There's no any trouble if the query is a stored procedure call. So, I guess namedparameters affects for only command objects which calls stored procedures. When I track the query using SQL Server Profiler, I saw just the following queries ran (note that, no parameter declaration, no value assignment) .

SQL:BatchStarting   Select * From mytable Where col1 = Lower(@param1) And col2 = Upper(@param2)
SQL:BatchCompleted  Select * From mytable Where col1 = Lower(@param1) And col2 = Upper(@param2)

不过，我需要做的是对本地有所不同的查询。我该怎么办呢，什么是最好的做法做，没有字符串连接等。我不想问号prepare查询，我不喜欢传递参数与秩序（这是neccesary问号和不命名参数），并多次。任何帮助将大大AP preciated。

However I need to do it on native vary queries. How can I do it, what is the best practice doing that without string concatenation etc. I don't want prepare queries with question marks, and I don't like passing parameters with order (it's neccesary with question marks and without named parameters) and multiple times. Any help would be greatly appreciated.

这是误解的更新：我要寻找不是取决于数据库是一个通用的解决方案。

Update on misunderstandings: I am looking for a general solution that is not depending on the database.

推荐答案

您不能使用命名与OLE DB即席查询参数。如果您使用的SQL Server，你可以做这样的事情：

You can't use named parameters with ad-hoc queries in OLE DB. If you're using SQL Server, you could do something like this:

Dim sql
sql = ""
sql = sql & "DECLARE @param1 varchar(20) = ?;"
sql = sql & "DECLARE @param2 varchar(20) = ?;"
sql = sql & "Select * From mytable Where col1 = Lower(@param1) And col2 = Upper(@param2) And col3 = @param1;"

' This section is basically the same as in the original question
Dim cmd
Set cmd = Server.CreateObject("Adodb.Command")
cmd.CommandType = adCmdText
cmd.CommandText = sql
cmd.Parameters.Append cmd.CreateParameter("@param1", adVarchar, adParamInput, 20, "some text 1")
cmd.Parameters.Append cmd.CreateParameter("@param2", adVarchar, adParamInput, 20, "some text 2")
Set cmd.ActiveConnection = cn
Dim rs
Set rs = cmd.Execute

您还必须在位置绑定参数，但这样一来，正确的顺序是更容易地看到，您可以在多个位置使用相同的参数。

You still have to bind the parameters positionally, but this way, the correct order is easier to see and you can use the same parameter in multiple locations.

这篇关于是否有可能使用命名参数时，命令类型为adCmdText？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！