[AccessD] Kindof OT: ADO problem in VB

What version of Oracle?

Drew

-----Original Message-----
From: Seth Galitzer [mailto:sgsax at ksu.edu]
Sent: Wednesday, February 12, 2003 10:50 AM
To: accessd
Subject: [AccessD] Kindof OT: ADO problem in VB


Hi folks,

I'm asking here because I'm hoping somebody can help me out with this
problem.

I wrote this little VB app that collects temperature data in our
greenhouses and then posts that data to an external server (using ADO)
over the network.  The app runs on three PCs and posts data every 15
minutes.  The database server is Oracle.  I've got the Oracle drivers
installed on all PCs.

So here's the problem: the app runs great... for about three days, at
which point it is unable to connect to the Oracle server (returns
"Unknown Error" from the Oracle software layer).  Once this happens, it
is unable to reconnect to the server until I stop and restart the app.

I do not keep the ADO connection open for the duration of the app run. 
I create a new connection each time it posts data.  If it is unable to
connect to the server every once in a while, that's fine, so long as it
can connect on the next cycle.  The problem is that it does not pick up
on the nxt cycle, or the next, or the next...  It will only connect
again after I have closed the app and restarted it.

So my question is this: where is this breaking down?  Is it a problem
with my code, or VB, or the ADO layer, or the Oracle driver layer?  I'm
pretty sure it's not my code (I'll include a copy of the relevant
function at the bottom) since it really doesn't do anything
extraordinary.  I don't know enough about ADO to be able to blame it
(this was my first real try at using ADO).  I do know that the Oracle
software layer is pain in the arse to install and is big, fat, and
heavy, but don't know where to start poking to evaluate it.  Could this
be a limitation of th VB engine and do I just need to rewrite it in C++?

Any suggestions would be welcome.

Seth

'-----Begin code snippet-----
' Watch for word wrap, this is copied and pasted right 
'    out of the code window
' The error is reported as ocurring on line 31 below

' Constants for trapping Oracle errors
Public Const conORA04031 = &H80004005
Public Const conORA04031B = -2147217900

Public Function basPostData(cBoard As clsBoard)
    Dim strConnect As String
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim lngSensorID As Long
    Dim dteTimeStamp As Date
    Dim intI As Integer
    Dim strSQL As String
    Dim strError As String
    Dim lngError As Long

21 On Error GoTo Err_basPostData

    
24     dteTimeStamp = Now()
25     Set conn = New ADODB.Connection
26     Set rst = New ADODB.Recordset

    ' Assemble connection string using system settings
29     conn.ConnectionString = "Provider=" & conProviderString & ";Data
Source=" & DBServer(False) & _
                            ";User ID=" & DBUser(False) & ";Password=" &
DBPassword(False) & ";"
31     conn.Open             'error reported on this line

34     For intI = 0 To cBoard.NumSensors
        ' First need to get sensor ID from database for insertion later
36         rst.Open "SELECT SensorID FROM tbl_Sensors WHERE ComputerID =
" & ComputerID(False) & _
                 " AND BoardNumber = " & cBoard.BoardNum & _
                 " AND ChannelNumber = " & intI + 1, conn           '
1-based index in data table
39         rst.MoveFirst                   'should only get one row back
in the recordset
40         lngSensorID = rst("SensorID")   'store value for later
        ' Insert new row into sensor_data table in database with data
for this channel
42         conn.BeginTrans
43         strSQL = "INSERT INTO tbl_Sensors_Data (datetime, sensorid,
data) VALUES (TO_DATE('" & Format(dteTimeStamp, "yy-mmm-dd:hh:mm") & "',
'YY-MON-DD:HH24:MI'), " & lngSensorID & ", '" & cBoard.sData(intI) &
"')"
44         conn.Execute strSQL
45         conn.CommitTrans
46         rst.Close       'need to close recordset before opening the
next one
47     Next intI

Exit_basPostData:
    ' Clean-up local object variables
51     On Error Resume Next
52     rst.Close
53     conn.Close

55     Set rst = Nothing
56     Set conn = Nothing
Exit Function
Err_basPostData:
59     strError = Err.Description
60     lngError = Err.Number
61     If boolLog Then basLogIt Hex(Err.Number) & ": " &
Err.Description, "basPostData", Erl
62     Select Case lngError
        Case 0
64          Resume Next
        Case conORA04031, conORA04031B
66          Resume Exit_basPostData
        Case Else
68          MsgBox lngError & ": " & strError & vbCrLf & "in function
basPostData", , "Unhandled Error"
70          Resume Exit_basPostData
71     End Select
72     Resume 0

End Function

'-----End code snippet-----


-- 
Seth Galitzer			sgsax at ksu.edu
Computing Specialist		http://puma.agron.ksu.edu/~sgsax
Dept. of Plant Pathology
Kansas State University

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com