Using Oracle bind variables from VB via ADO
The command object in VB is what you use for Oracle bind variables:
Example:
**************************************************
dim conn As ADODB.Connection
dim cmd As ADODB.Command
dim mySQLString As String
'[be sure to initialize the conn and cmd objects somewhere]
mySQLString = "DELETE FROM MyTable WHERE Company = ? AND Dept = ?"
With cmd
If .ActiveConnection Is Nothing Then
Set .ActiveConnection = conn
End If
.CommandText = mySQLString
.Prepared = True 'use this for long SQL statements only
.Parameters.Append .CreateParameter(, adVarChar, , 35, MyCompVariable)
.Parameters.Append .CreateParameter(, adVarChar, , 20, MyDeptVariable)
.Execute , , adCmdText + adExecuteNoRecords
End With
******************************************
This code is for deleting, updating, and inserting.
The code for selecting is below:
******************************************
dim tbl As ADODB.Recordset
mySQLString = "SELECT * FROM MyTable WHERE Dept = ?"
With cmd
If .ActiveConnection Is Nothing Then
Set .ActiveConnection = conn
End If
.CommandText = mySQLString
.Prepared = True 'use this for long SQL statements only
.Parameters.Append .CreateParameter(, adVarChar, , 20, MyDeptVariable)
Set tbl = .Execute(, , adCmdText)
End With
