ADO and Oracle Bind-Variables

[steph]

Hi,

I need to query an Oracle database from within Excel VBA, and I tried
to do it right and efficient in an Oracle way by using bind variables.

That's my code:

===
Public Function query_wpname(sisin As String)
Dim wpq As ADODB.Command
Dim p_sisin As ADODB.Parameter

Set wpq = New ADODB.Command
wpq.ActiveConnection = fd_con

Set wpr = New ADODB.Recordset

If wpr.State = adStateOpen Then wpr.Close

wpq.CommandType = adCmdText
wpq.CommandText = " select name" & _
vbCrLf & "from table" & _
vbCrLf & "where intern=?" & _

Set p_sisin = wpq.CreateParameter("p_sisin", adVarChar,
adParamInput, 12, sisin)
wpq.Parameters.Append p_sisin

wpr.Open wpq.CommandText, fd_con, adOpenStatic, adLockOptimistic
If wpr.RecordCount <= 0 Then Exit Function

wpr.MoveFirst
Do While Not wpr.EOF
query_wpname = wpr("name")
vkd.MoveNext
Loop
End Function
===

Unforunately viariable-binding does not seem to work with this code,
as I get an "ORA-01008: not all variables bound" in line "wpr.Open
wpq.CommandText, fd_con, adOpenStatic, adLockOptimistic"

As I'm more of an Oracle-person than an Excel-person i probably need
some hints on this. Anyone?

thanks,
stephan

 

[steph]

Hi,

I need to query an Oracle database from within Excel VBA, and I tried
to do it right and efficient in an Oracle way by using bind variables.

That's my code:

===
Public Function query_wpname(sisin As String)
    Dim wpq As ADODB.Command
    Dim p_sisin As ADODB.Parameter

    Set wpq = New ADODB.Command
    wpq.ActiveConnection = fd_con

    Set wpr = New ADODB.Recordset

    If wpr.State = adStateOpen Then wpr.Close

    wpq.CommandType = adCmdText
    wpq.CommandText = " select name" & _
    vbCrLf & "from table" & _
    vbCrLf & "where intern=?" & _

    Set p_sisin = wpq.CreateParameter("p_sisin", adVarChar,
adParamInput, 12, sisin)
    wpq.Parameters.Append p_sisin

    wpr.Open wpq.CommandText, fd_con, adOpenStatic, adLockOptimistic
    If wpr.RecordCount <= 0 Then Exit Function

    wpr.MoveFirst
    Do While Not wpr.EOF
      query_wpname = wpr("name")
      vkd.MoveNext
    Loop
End Function
===

Unforunately viariable-binding does not seem to work with this code,
as I get an "ORA-01008:    not all variables bound" in line "wpr.Open
wpq.CommandText, fd_con, adOpenStatic, adLockOptimistic"

As I'm more of an Oracle-person than an Excel-person i probably need
some hints on this. Anyone?

thanks,
stephan

solved with google ...

see http://www.garayed.com/oracle/143667-how-lock-record-table-using-query-vb6-oracle-database-2.html