I have excel VBA script:
Set cоnn = CreateObject("ADODB.Connection") conn.Open "report" Set rs = conn.Execute("select * from table" ) Script work fine, but i want to add parameter to it. For example " where (parentid = myparam)", where myparam setted outside query string. How can i do it?
Of course i can modify query string, but i think it not very wise.
You need to use an ADODB.Command object that you can add parameters to. Here's basically what that looks like
Sub adotest() Dim Cn As ADODB.Connection Dim Cm As ADODB.Command Dim Pm As ADODB.Parameter Dim Rs as ADODB.Recordset Set Cn = New ADODB.Connection Cn.Open "mystring" Set Cm = New ADODB.Command With Cm .ActiveConnection = Cn .CommandText = "SELECT * FROM table WHERE parentid=?;" .CommandType = adCmdText Set Pm = .CreateParameter("parentid", adNumeric, adParamInput) Pm.Value = 1 .Parameters.Append Pm Set Rs = .Execute End With End Sub The question mark in the CommandText is the placeholder for the parameter. I believe, but I'm not positive, that the order you Append parameters must match the order of the questions marks (when you have more than one). Don't be fooled that the parameter is named "parentid" because I don't think ADO cares about the name other than for identification.
Alternative example returning a command from a function:
Function BuildCommand(conn As ADODB.Connection) As ADODB.Command Dim cmd As ADODB.Command Set cmd = New ADODB.Command cmd.ActiveConnection = conn cmd.CommandType = adCmdText cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 255, "Dave") cmd.CommandText = "SELECT * FROM users WHERE name = @name;" Set BuildCommand = cmd End Function A couple things to note:
When using adVarChar data type, the size argument to cmd.CreateParameter (e.g. 255) is required. Not supplying it results a run-time error 3708: Application-defined or object-defined error, as indicated in the documentation:
If you specify a variable-length data type in the Type argument, you must either pass a Size argument or set the Size property of the Parameter object before appending it to the Parameters collection; otherwise, an error occurs.
If the cmd.ActiveConnection property is set when cmd.CommandText is set, and cmd.CommandText contains named parameters, cmd.Parameters will be populated accordingly. Calling cmd.Parameters.Append afterwards could result in duplicates. For example:
cmd.ActiveConnection = conn cmd.CommandType = adCmdText Debug.Print cmd.Parameters.Count ' 0 cmd.CommandText = "SELECT * FROM users WHERE name = @name;" Debug.Print cmd.Parameters.Count ' 1 cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, adParamInput, 255, "Dave") Debug.Print cmd.Parameters.Count ' 2 I believe this is what is meant in the documentation, which is slightly inaccurate:
If the Prepared property of the Command object is set to True and the Command object is bound to an open connection when you set the CommandText property, ADO prepares the query (that is, a compiled form of the query that is stored by the provider) when you call the Execute or Open methods.
As a workaround, either set cmd.CommandText or cmd.ActiveConnection after adding parameters.
Must Declare the scalar variable "@name"
cmd.NamedParameters = True and the connection must use an ADO provider which supports named parameters (e.g. SqlClient, not ODBC/OLEDB).
Provider=SQLNCLI11 in my connection string I still get Must declare the scalar variable "@name". Changing the order of assignment has no effect. I also tried setting cmd.NamedParameters = True and cmd.Prepared = True.