[AccessD] Kindof OT: ADO problem in VB

MartyConnelly martyconnelly at shaw.ca
Wed Feb 12 15:31:01 CST 2003


If using MSDORA and mdac 2.7 there is a memory leak
http://support.microsoft.com/default.aspx?scid=kb;en-us;322968
There are a couple of others

Seth Galitzer wrote:

>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-----
>
>
>  
>





More information about the AccessD mailing list