Install CursorType with ADODB.Command.Execute

Question

Is there a way to set CursorType for the ADODB.RecordSet that I get from ADODB.Command.Execute ?

I know that this is possible if I do this:

 rs = Server.CreateObject("ADODB.RecordSet") rs.Open(cmd) 

However, I am currently using Command.Execute with a Parameters Parameters that automatically handles parameter arrays ? for safe interpolation. Therefore, using RecordSet.Open not an option.

In particular, my code currently looks like this:

 function ExecuteSQL(conn, sql, args) set ExecuteSQL_CmdObj = Server.CreateObject("ADODB.Command") ExecuteSQL_CmdObj.CommandType = adCmdText ExecuteSQL_CmdObj.CommandText = sql ExecuteSQL_CmdObj.ActiveConnection = conn if Ubound(args) = -1 then set ExecuteSQL = ExecuteSQL_CmdObj.Execute else set ExecuteSQL = ExecuteSQL_CmdObj.Execute(,args) end if end function 

If I want to support the same API, but also control CursorType , how can this be done?

Answer 1

The answer, as far as I was able to determine, is that this is not possible with ADODB.Command.Execute , but with ADODB.RecordSet.Open you can use ADODB.Command.Parameters :

 function CreateSQLParameter(arg) set param = Server.CreateObject("ADODB.Parameter") select TypeName(arg) case "String" param.Type = adVarChar param.Size = Len(CStr(arg)) param.Value = CStr(arg) case "Integer" param.Type = adInteger param.Value = CLng(arg) case "Double" param.Type = adDouble param.Value = CDbl(arg) case else ' 13 is the "Type Mismatch" error code Err.Raise(13,,, "Type '" & TypeName(arg) "' is not handled. Please add support for it to CreateSQLParameter") end select set CreateSQLParameter = param end function function CreateSQLCommand(sql, args) set cmd = Server.CreateObject("ADODB.Command") 'From http://www.w3schools.com/asp/prop_comm_commandtype.asp. 'adCmdText is for some reason undefined in our scope. cmd.CommandType = 1 cmd.CommandText = sql for i = Lbound(args) to Ubound(args) set param = CreateSQLParameter(args(i)) cmd.Parameters.Append(param) next set CreateSQLCommand = cmd end function function ExecuteSQL(conn, sql, args) set cmd = CreateSQLCommand(sql, args) set rs = Server.CreateObject("ADODB.RecordSet") rs.Open(cmd, conn) set ExecuteSQL = rs end function 

Answer 2

Hope this helps you:

 ExecuteSQL_CmdObj.ActiveConnection.CursorType = yourType 

From: http://www.w3schools.com/asp/prop_conn_cursorlocation.asp

Syntax:

 objConnection.CursorLocation objRecordset.CursorLocation