ADO parameterised queries and return values
Back before ADO.NET, we used good old ADO to do our data access in our ASP pages. To improve performance and avoid SQL injection attacks, it is good practice to use parameterised queries or stored procedures. Usually these are interchangeable but I’ve noticed today that ADO throws up the following error if you try and set up a return value parameter:
Microsoft OLE DB Provider for SQL Server error ‘80040e21’
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
For example, the following code gives that error:
Set Cmd = Server.CreateObject(“ADODB.Command”) Cmd.ActiveConnection = conn
Dim ParamReturn Set ParamReturn = Cmd.CreateParameter(“Return”, adInteger, adParamReturnValue) Cmd.Parameters.Append ParamReturn Cmd.CommandText = “INSERT INTO table (col1) VALUES (?); RETURN SCOPE_IDENTITY()” Cmd.CommandType = adCmdText
Cmd.Parameters.Append Cmd.CreateParameter(“p1”, adInteger, adParamInput, , “value”)
Cmd.Execute
I haven’t found any solution, other than to use a stored procedure instead. Presumably return values aren’t supported for plain parameterised queries, though I haven’t seen that documented anywhere (yet).
Categories: SQL