bind variables for Oracle ADODB SQL statements

Question

I've tried unsuccessfully to find a really simple example. I'm close but something is still wrong. I want to use bind variables in a VB program I'm writing. I keep getting missing oracle expression if I use @ for my bind variables. If I use & for them, I get not all variables are bound

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim par As ADODB.Parameter

Dim test As String
Dim plcynbr As String


Dim sSQL As String
Dim lngLenName As String

lngsize = 50
lngLenName = "'bozo test'"

sSQL = "UPDATE MyTable SET ID_Name = @theName WHERE ID = @id"

        dbConnectStr = "Provider=msdaora;Data Source=P1ltcent;User ID=userid;Password=password"
        cnt.ConnectionString = dbConnectStr
        cnt.Open dbConnectStr



Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = cnt
    .CommandType = adCmdText
    .CommandText = sSQL
    Set par = .CreateParameter("@theName", adVarChar, adParamInput, lngsize, lngLenName)
    .Parameters.Append par
    Set par = .CreateParameter("@id", adInteger, adParamInput, , 1)
    .Parameters.Append par


 For Each prm In .Parameters
    Debug.Print prm.Name & " : " & prm.Value
Next

Debug.Print cmd.CommandText


    .Execute , , adCmdText And adExecuteNoRecords
End With

So I displayed my parameters and they look good to me &theName : 'bozo test' &id : 1

I displayed my SQL and it looks good to me

UPDATE MyTable SET ID_Name = &theName WHERE ID = &id

but I'm still getting not all variables bound

Try ? or:theName instead of @theName. Parameter must be without i.e. Set par = .CreateParameter("theName", adVarChar, adParamInput, lngsize, lngLenName) — Wernfried Domscheit
– Wernfried Domscheit, Commented Feb 25, 2016 at 20:58
Write lngLenName = "bozo test" instead of lngLenName = "'bozo test'" - that is the purpose of bind variables, you don't have to quote or escape the values. — Wernfried Domscheit
– Wernfried Domscheit, Commented Feb 25, 2016 at 20:59

Beege · Accepted Answer · 2016-02-25 21:00:14Z

2

Bind variables in oracle are usually prefaced with :

:my-bind-var

Look here for a similar discussion: http://www.vbforums.com/showthread.php?681536-RESOLVED-bind-variables-against-Oracle-%28ADODB-ORACLE-BIND-PARAMETER-QUERY%29

Effectively, you need something like this in your code

sSQL = "UPDATE MyTable SET ID_Name = :theName WHERE ID = :id"

... then bind them (without the colon) ...

Set par = .CreateParameter("theName", adVarChar, adParamInput, lngsize, lngLenName)

edited Feb 25, 2016 at 21:00

answered Feb 25, 2016 at 19:26

Beege

6654 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

yarnseeker Over a year ago

still get not all variables are found when i use the : instead

yarnseeker Over a year ago

actually I had to ONLY put ? where i want the variables. I can't use names like &id etc. And then in the bind variables you have to call them by the right names param1, param2, etc. Once I changed those few things, it worked like a champ. Thanks!

el_technic0 Over a year ago

You can use :variable or ? notation for SQL bind vars in VB6 ADO, but what really is important is to get the order right when adding the parameters to the parameter collection of the command object. The name of the parameter seems useless unfortunately.

ArcherBird Over a year ago

I too continue to get the Not all variables bound error messages when using : bind notation. My guess is it has something to do with the oracle driver I'm using? (OraClient11g)

yarnseeker · Accepted Answer · 2016-02-25 21:30:51Z

 Dim cnt As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim cmd As ADODB.Command
 Dim par As ADODB.Parameter

Dim test As String
 Dim plcynbr As String


 Dim sSQL As String
 Dim lngLenName As String

 lngsize = 50
 lngLenName = "bozo test"

sSQL = "UPDATE MyTable SET ID_Name = ? WHERE ID = ?"

    dbConnectStr = "Provider=msdaora;Data Source=P1ltcent;User ID=userid;Password=password"
    cnt.ConnectionString = dbConnectStr
    cnt.Open dbConnectStr



Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cnt
  .CommandType = adCmdText
  .CommandText = sSQL

   Set par = .CreateParameter("param1", adChar, adParamInput, lngsize, lngLenName)
  .Parameters.Append par


Set par = .CreateParameter("param2", adInteger, adParamInput, , 1)
.Parameters.Append par



For Each prm In .Parameters
    Debug.Print prm.Name & " : " & prm.Value
Next

Debug.Print cmd.CommandText


.Execute , , adCmdText And adExecuteNoRecords
End With

Collectives™ on Stack Overflow

bind variables for Oracle ADODB SQL statements

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related