Excel_UDF_Update Cells with Oracle data 2

[LevelThought]

Creating UDF for the first time.

I am interested in creating an User Defined Function within an Excel worksheet that uses ADO to pull SQL results from an Oracle database into a cell.

For example, I have account numbers in column A of a worksheet and would like to enter the following function in column B to "pull" the payor payment amount from the Oracle table titled "preview_encounter" for each unique account number in column A.

What modifications do I need so that the results are input into column B for each unique account number in column A?

Also, I assume that I can enter the function at B1 and copy down the column. Is this so?

(Note, I prefer to use a UDF instead of MS Query or SQL.Request.)

Function PayorPymt(rng As Range) As Double
Dim adoCon As ADODB.Connection
Dim rs As ADODB.Recordset
On Error Resume Next
If adoCon.State = adStateClosed Then
Set adoCon = New ADODB.Connection
adoCon.Open "Provider=MSDAORA.1;" & _
"Data source = Vcc;" & _
"User ID = Ct_jacx;" & _
"Password=password;" & _
"Persist Security Info=True"
End If
strSQL = "SELECT total_payments FROM Ct_jacx.preview_encounter where " & _
"Ct_jacx.preview_encounter.account_no = rng.value;"

Set rs = adoCon.Execute(strSQL)
Debug.Print rs
If IsNull(rs(0)) Then
FnlVal = 0
Else
FnlVal = rs(0)
End If

End Function

Thanks in advance for any insight.

 

[PHV]

A starting point:
Dim adoCon As ADODB.Connection
Function PayorPymt(rng As Range) As Double
Application.Volatile
Dim rs As ADODB.Recordset
If adoCon = Nothing Then
Set adoCon = New ADODB.Connection
End If
If adoCon.State = adStateClosed Then
adoCon.Open "Provider=MSDAORA.1;" & _
"Data source = Vcc;" & _
"User ID = Ct_jacx;" & _
"Password=password;" & _
"Persist Security Info=True"
End If
strSQL = "SELECT total_payments FROM Ct_jacx.preview_encounter WHERE " & _
"account_no ='" & rng.Value & "'"
Set rs = adoCon.Execute(strSQL)
If rs.EOF Or rs.BOF Then
PayorPymt = 0
Else
PayorPymt = IIf(IsNull(rs(0)), 0, rs(0))
End If
End Function

If account_no is defined as numeric in preview_encounter then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LevelThought]

Upon typing "=PayorPymt(A1)" at B1, an error dialog box appears with

Compile error:
User-defined type not defined

and the following line is highlighted

Dim adoCon As ADODB.Connection

Any clue as to what the solution is?

Thanks in advance.

 

[fredericofonseca]

you need to add reference to Microsoft ActiveX Data Objects 2.x on the VBE menu. (tools->references)


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LevelThought]

I added a reference to "Microsoft ActiveX Data Objects 2.7 Library."

Upon trying to use =PayorPymt(A1), "Nothing" is highlighted on the line "If adoCon = Nothing Then" and the error dialog box displays "Compile error: "Invalid use of object"

When I comment out the section containing the first If clause, the error received is #VALUE!

Does this imply that the SQL script is not valid?

 

[fredericofonseca]

try the following
if it doesn't work then try to comment line signaled with "A" and uncomment the line with "B" and execute it again.

Dim adoCon As ADODB.Connection
Dim badocon As Boolean

Function PayorPymt(rng As Range) As Double
Application.Volatile
Dim rs As ADODB.Recordset
If badocon = False Then
    Set adoCon = New ADODB.Connection
    badocon = True
End If
If adoCon.State = adStateClosed Then
    adoCon.Open "Provider=MSDAORA.1;" & _
        "Data source = Vcc;" & _
        "User ID = Ct_jacx;" & _
        "Password=password;" & _
        "Persist Security Info=True"
End If
strSQL = "SELECT total_payments FROM Ct_jacx.preview_encounter WHERE " & _
    "account_no = '" & rng.Value & "'" 'A
'    "account_no = " & rng.Value   'B

Set rs = adoCon.Execute(strSQL)
If rs.EOF Or rs.BOF Then
    PayorPymt = 0
Else
    PayorPymt = IIf(IsNull(rs(0)), 0, rs(0))
End If
End Function

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LevelThought]

The error persists - continue to receive #VALUE!


Any other suggestions would be appreciated.

 

[fredericofonseca]

if you debug which lines are executed?

and what do you get if you do a

print.debug strSQL

before the "set rs =" line, and then copy the output of the debug into your Oracle sqlplus and execute it. does the SQL work?

Also try the following code.

Dim adoCon As ADODB.Connection
Dim badocon As Boolean

Function PayorPymt(rng As Range) As String
On Error GoTo error_handler
Application.Volatile
Dim rs As ADODB.Recordset
If badocon = False Then
    Set adoCon = New ADODB.Connection
    badocon = True
End If
If adoCon.State = adStateClosed Then
    adoCon.Open "Provider=MSDAORA.1;" & _
        "Data source = Vcc;" & _
        "User ID = Ct_jacx;" & _
        "Password=password;" & _
        "Persist Security Info=True"
End If
strSQL = "SELECT total_payments FROM Ct_jacx.preview_encounter WHERE " & _
    "account_no = '" & rng.Value & "'" 'A
'    "account_no = " & rng.Value   'B

Set rs = adoCon.Execute(strSQL)
If rs.EOF Or rs.BOF Then
    PayorPymt = 0
Else
    PayorPymt = IIf(IsNull(rs(0)), 0, rs(0))
End If
Exit Function
error_handler:
If adoCon.Errors.Count > 0 Then
    PayorPymt = adoCon.Errors(0).NativeError & "=" & adoCon.Errors(0).Description
Else
    PayorPymt = 999999999
End If

End Function

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LevelThought]

This is still not working as planned!

When I insert the line "print.debug strSQL" the error message received upon trying to use the function is "Method not valid without suitable object."

Upon commenting out the line "print.debug strSQL" and trying to use the function, the error I receive as displayed on the Excel worksheet in column B1 is

"12154=ORA-12154: TNS:could not resolve service name"

Also, when I depress the F8 key to step thru the code, I continually receive the audible bell sound!

What am I missing?

 

[fredericofonseca]

oops.

debug.Print strsql




"12154=ORA-12154: TNS:could not resolve service name"
This basically means that your connection string is incorrect or that you do not have Oracle client correctly installed in your computer. Nothing to do with your code anymore. Contact your Oracle guys to see what do ou need to install and how to configure it.

You can also try the following if you are sure Oracle is installed correctly.

Create a new text file on your desktop. Then rename it to have a extension of ".udl". Now open this file, and a Data Link wizard should show up. Select the correct provider on your case (your code is using the Microsoft OLE DB provider for Oracle), add required connection data, and test the connection.
Once the connection is working save the wizard, and then open the file with notpad. The connection string you will see is the one you need to use.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LevelThought]

Per some additional research,

It appears that the function is maybe using the wrong type of argument.

Maybe the argument of the function that need to be numeric is referring to a cell that contain labels...

Any thoughts/comments?

 

[fredericofonseca]

until you solve the "12154=ORA-12154: TNS:could not resolve service name" error you can not do anything. note that this error is issued on the open bit, not on the execution of your SQL.

And the function is perfectly fine as it is now, and the only think you may wish to change is the return value to be numeric

"PayorPymt(rng As Range) As String" this was changed to string so you could get the error string on the cell. nothing else.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[LevelThought]

No longer receive TNS error. However, I now receive the
#VALUE! error.

Displayed below is the function as of today.


Dim adoCon As ADODB.Connection
Dim badocon As Boolean
Function PayorPymt(rng As Range) As Double

'First iteration
'Dim adoCon As ADODB.Connection
On Error GoTo error_handler
Application.Volatile
Dim rs As ADODB.Recordset
If badocon = False Then
Set adoCon = New ADODB.Connection
badocon = True
End If
If adoCon.State = adStateClosed Then
adoCon.Open "Provider=MSDAORA.1;" & _
"Data source = Vcc;" & _
"User ID = Ct_jacx;" & _
"Password=password;" & _
"Persist Security Info=True"
End If

strSQL = "SELECT total_payments FROM Ct_jacx.preview_encounter WHERE Account_no = " & rng.Value

Debug.Print strSQL


Set rs = adoCon.Execute(strSQL)
If rs.EOF Or rs.BOF Then
PayorPymt = 0
Else
PayorPymt = IIf(IsNull(rs(0)), 0, rs(0))
End If
Exit Function
error_handler:
If adoCon.Errors.Count > 0 Then
PayorPymt = adoCon.Errors(0).NativeError & "=" & adoCon.Errors(0).Description
Else
PayorPymt = 999999999
End If
End Function


Account Number has a numeric data type within the Oracle database.

The Payor Payment should be in the format such that the figures in column B should be like 1000.00

Currently, there are two account numbers in column A and I am entering the function in column B similar to =PayorPymt(A1) and copying down column B.

 

[fredericofonseca]

you really did not fix your problem with Oracle.

You are now getting a "#value" error because you changed the return type to be a "double", and your function is still populating its return value with the Oracle error (string).


and for you to be sure change your latest version of the function as follows

    PayorPymt = adoCon.Errors(0).NativeError & "=" & adoCon.Errors(0).Description

to

    MsgBox adoCon.Errors(0).NativeError & "=" & adoCon.Errors(0).Description

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com