Re: How do I use Oracle bind variables using ADO in Visual Basic?

In article <df35b276.0205080727.6933e5f3_at_posting.google.com>, burgess_at_sympatico.ca says...
> We are using VB through ADO & OLEDB with Oracle 8i. Right now, we're
> just sending hard-coded SQLs (ie. SELECT x FROM y WHERE z = 5) to the
> Execute method of the ADODB Connection object and getting the results
> from the Recordset object.
> We'd MUCH rather use bind variables in cases like this.
>
> I'd rather not rewrite the database layer to use OO4O, if at all
> possible. If there a way to use bind variables (ie. SELECT x FROM y
> WHERE z = :v) in ADO?
>

Just use ADO's parameters collection and a question mark (?) as a bind variable in your SQL. Here's an example from the docs:

Dim objCon As NEW ADODB.Connection
Dim objCmd As NEW ADODB.Command
Dim prEmpno As NEW ADODB.Parameter
Dim prEname As NEW ADODB.Parameter
...
objCmd.ActiveConnection = objCon
objCmd.CommandType = adCmdText

' Create and append the parameters to the command object Set prEmpno = objCmd.CreateParameter("prEmpno", adSmallInt, adParamInput, ,8521)
' prEname is bound to a NVARCHAR column in the EMP table Set prEname = objCmd.CreateParameter("prEname", adBSTR, adParamInput, , "Joe")
objCmd.Parameters.Append prEmpno
objCmd.Parameters.Append prEname

' Enabling the NDatatype property indicates to the provider ' that one or more of the bound parameters is of N datatype objCmd.Properties("NDatatype") = TRUE

' Assume column ENAME in table EMP is of NVARCHAR type objCmd.CommandText = "INSERT INTO EMP (EMPNO, ENAME) VALUES (?, ?)"

' Execute the SQL
objCmd.Execute

' It is a good idea to disable the property after execute as the same command
' object may be used for a different SQL statement objCmd.Properties("NDatatype") = FALSE

• tojo