I'm trying to use named parameters with following query through Adodb.Command but no luck.
\[code\]Select * From mytable Where col1 = Lower(@param1) And col2 = Upper(@param2) And col3 = @param1\[/code\]The code I used something like that. \[code\]Dim cmdSet cmd = Server.CreateObject("Adodb.Command")cmd.NamedParameters = Truecmd.CommandType = adCmdTextcmd.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 = cnDim rsSet rs = cmd.Execute\[/code\]The problem is that, this causes an error on RDBMS side. \[code\]Microsoft OLE DB Provider for SQL Server error '80040e14' Must declare the scalar variable "@param1".\[/code\]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) .
\[code\]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)\[/code\]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.
\[code\]Select * From mytable Where col1 = Lower(@param1) And col2 = Upper(@param2) And col3 = @param1\[/code\]The code I used something like that. \[code\]Dim cmdSet cmd = Server.CreateObject("Adodb.Command")cmd.NamedParameters = Truecmd.CommandType = adCmdTextcmd.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 = cnDim rsSet rs = cmd.Execute\[/code\]The problem is that, this causes an error on RDBMS side. \[code\]Microsoft OLE DB Provider for SQL Server error '80040e14' Must declare the scalar variable "@param1".\[/code\]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) .
\[code\]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)\[/code\]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.