Unable to retrieve Data from Oracle database

[Zhil]

Hi all

I am not able to get the data from Oracle from vb6 using stored proc. no exception is throw but I am not getting any data. Please help !!!!! Below is my vB sample code and Oracle stored proc.. Thanks in advance.


VB Code
**************************************************************************************
Dim intResult As Integer
Dim strSQL As String
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String

Dim snpData As adodb.Recordset
Dim dbDatabase As adodb.Connection

On Error Resume Next

Set dbDatabase = New adodb.Connection

strDatabase = "KUCDM02" '"MyDB" 'From tnsnames.ora
strUserName = "appuser" '"MyUserID"
strPassword = "appuser123" '"MyPassword"


dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle.1;Data Source=KUCDM02;User ID=appuser;Password=appuser123;"


dbDatabase.ConnectionTimeout = 40
dbDatabase.CursorLocation = adUseClient
dbDatabase.Open


If (dbDatabase.State <> 1) Or (Err <> 0) Then
intResult = MsgBox("Could not connect to the database. Check your user name and password." & vbCrLf & Error(Err), 16, "Oracle Connection Demo")
Exit Function
Else

Dim Policyval As String
Policyval = "U500001923"

Dim dresult As String

Dim cmdMine As adodb.Command, rsMine As adodb.Recordset

cmdMine.ActiveConnection = dbDatabase
cmdMine.CommandTimeout = 300
cmdMine.CommandType = adCmdStoredProc
cmdMine.CommandText = "{ call SP_AMS_GETMIGPOL }"

cmdMine.Parameters.Append cmdMine.CreateParameter(, adVarChar, adParamInput, , Policyval)
Set rsMine = cmdMine.Execute


If Not rsMine Is Nothing Then
If rsMine.RecordCount > 0 Then
MsgBox rsMine.Fields("la_policy_number").Value
End If
End If

rsMine.Close

Exit Function
End If

If dbDatabase.State = 1 Then
dbDatabase.Close
End If

Set snpData = Nothing
Set dbDatabase = Nothing
************************************************************************************

Oracle Stored Procedure
*************************************************************************************
create or replace PROCEDURE "SP_AMS_GETMIGPOL" (
p_policyno IN VARCHAR2,
p_resultset OUT TYPES.cursortype
)
IS

BEGIN
OPEN p_resultset FOR
SELECT chdrnum la_policy_number
FROM cdmtgt.ext_la_yaiapf
WHERE chdrsel = p_policyno;
END;

*************************************************************************************

[DataMiser]

On Error Resume Next

Get rid of that line. It is stopping you from seeing any exception that may be occurring.