|
-
Dec 25th, 2006, 02:20 PM
#1
Re: Empty recordset while it shouldn't be empty
When the problem occurs, is it after a long duration of inactivity?
EDIT: Weird... why did this become post #1? Should be #11
-
Dec 25th, 2006, 02:59 PM
#2
Junior Member
Empty recordset while it shouldn't be empty
Something strange is happening with this message... This should be # 12. Anyway, the problem occurs random, sometimes we don't have problems the whole day, sometimes it happens several times in a row.
It's very difficult to discover the reason why..
Last edited by AvdK; Dec 25th, 2006 at 03:05 PM.
-
Jul 3rd, 2007, 03:57 AM
#3
Junior Member
Empty recordset while it shouldn't be empty
I'm using a VB program to retrieve data from an Access database with 400 records over a network. I use DAO to connect to the database.
An analyser sends a request to the program and then the program does a query on the database and send the results back to the analyser.
Usually there are 10 - 20 requests in a row. There are 2 - 3 requests per second. This worked without significant errors for years.
Since a couple of weeks it happens that there are no records found. The recordset is empty, but as soon as I close the program and start it again, the records are retrieved instantly.
What can be the reason for this and how can I solve this?
-
Jul 3rd, 2007, 04:00 AM
#4
Re: Empty recordset while it shouldn't be empty
You ask a question but provide no code, SQL or any other information to use to answer such a question.
What errors are you getting?
What has changed on the computer where the app runs?
etc...
-
Jul 3rd, 2007, 04:18 AM
#5
Junior Member
Re: Empty recordset while it shouldn't be empty
Here is a small part of the program, the SQL-query:
Code:
Set QDfRequest = DbWerk.CreateQueryDef("")
With QDfRequest
.SQL = "SELECT * FROM Request WHERE ((Combikey Like '" & sMonsternummer & sAppCode & "')" & _
" AND (JN_Deelbepaling Like '*1*')" & _
" AND (Val(BepStatusVerw) > " & Val(sMinBepStatusVerw) & ")" & _
" AND (Val(BepStatusVerw) < " & Val(sMaxBepStatusVerw) & "))" & _
" ORDER BY MonsterNummer;"
Set RstRequest = .OpenRecordset
End With
If RstRequest.RecordCount > 0 Then
RstRequest.MoveFirst
While Not RstRequest.EOF
..
..
..
I get no errors. The program hasn't been changed for years. As I said, the program usually works OK. Only sometimes it returns "No Requests" while there are requests in the database. When you close the program and start it again and do the same query it immediately returns the right records.
-
Jul 3rd, 2007, 04:42 AM
#6
Re: Empty recordset while it shouldn't be empty
That sounds like a resource problem...
Do you do this in your app?
Code:
Set QDfRequest = Nothing
-
Jul 3rd, 2007, 04:50 AM
#7
Junior Member
Re: Empty recordset while it shouldn't be empty
I suspect the resources too. that's why I build this extra loop with first a QDFRequest.Close and RstRequest.Close for testing purposes only.
It didn't help however.
Do you think it needs RstRequest.Nothing to cleanup?:
Code:
If RstRequest.RecordCount = 0 Then
Do Until iRetry = 5
bRetry = True
QDfRequest.Close
RstRequest.Close
DoEvents
DoEvents
Set QDfRequest = DbWerk.CreateQueryDef("") With QDfRequest
.SQL = "SELECT * FROM Request WHERE ((Combikey Like '" & sMonsternummer & sAppCode & "')" & _
" AND (JN_Deelbepaling Like '*1*')" & _
" AND (Val(BepStatusVerw) > " & Val(sMinBepStatusVerw) & ")" & _
" AND (Val(BepStatusVerw) < " & Val(sMaxBepStatusVerw) & "))" & _
" ORDER BY MonsterNummer;"
Set RstRequest = .OpenRecordset End With
If RstRequest.RecordCount > 0 Then
Exit Do
Else
iRetry = iRetry + 1
End If
Loop
End If
-
Jul 3rd, 2007, 05:07 AM
#8
Re: Empty recordset while it shouldn't be empty
Everytime you finish using a resource get rid of it from memory. IE set it to nothing. I would need to see more of the code that you loop thru.
-
Jul 3rd, 2007, 05:12 AM
#9
Member
Re: Empty recordset while it shouldn't be empty
I'd suggest at the end, after using the recordset, setting it to Nothing
rstRequest = nothing
-
Jul 3rd, 2007, 05:17 AM
#10
Re: Empty recordset while it shouldn't be empty
Do you use:
Set something = NEW otherthing
???
-
Jul 3rd, 2007, 06:00 AM
#11
Junior Member
Re: Empty recordset while it shouldn't be empty
This is more code. As you can see I didn't use rstRequest=Nothing.
I will add this line and see what happens.
I still find it strange that it worked OK for years and all of a sudden it starts to generate problems with "no requests".
Code:
If CheckSampleID(sMonsternummer) Then
'sMessIn = " SampleID checked: OK"
'ListIn (sMessIn)
'sMessIn = " Request for sample: " & sMonsternummer
'ListIn (sMessIn)
bSuccess = True
bAgain = True
Set QDfRequest = DbWerk.CreateQueryDef("") 'Initialiseer Query-definitie
With QDfRequest
.SQL = "SELECT * FROM Request WHERE ((Combikey Like '" & sMonsternummer & sAppCode & "')" & _
" AND (JN_Deelbepaling Like '*1*')" & _
" AND (Val(BepStatusVerw) > " & Val(sMinBepStatusVerw) & ")" & _
" AND (Val(BepStatusVerw) < " & Val(sMaxBepStatusVerw) & "))" & _
" ORDER BY MonsterNummer;"
Set RstRequest = .OpenRecordset
End With
DoEvents
If bSuccess = True Then
iAantal = 0
bRetry = False
iRetry = 0
If RstRequest.RecordCount = 0 Then
Do Until iRetry = 5
bRetry = True
QDfRequest.Close
RstRequest.Close
DoEvents
DoEvents
Set QDfRequest = DbWerk.CreateQueryDef("") 'Initialiseer Query-definitie
With QDfRequest 'SQL-statement definieeren voor query
.SQL = "SELECT * FROM Request WHERE ((Combikey Like '" & sMonsternummer & sAppCode & "')" & _
" AND (JN_Deelbepaling Like '*1*')" & _
" AND (Val(BepStatusVerw) > " & Val(sMinBepStatusVerw) & ")" & _
" AND (Val(BepStatusVerw) < " & Val(sMaxBepStatusVerw) & "))" & _
" ORDER BY MonsterNummer;"
Set RstRequest = .OpenRecordset 'Query uitvoeren naar recordset RstRequest
End With
If RstRequest.RecordCount > 0 Then
Exit Do
Else
iRetry = iRetry + 1
End If
Loop
End If
DoEvents
If RstRequest.RecordCount > 0 Then
RstRequest.MoveFirst
While Not RstRequest.EOF
If bRetry = True Then
sMessOut = " Retry query gelukt na " & iRetry & " pogingen."
ListOut (sMessOut)
End If
iAantal = iAantal + 1
sMonsternummer = RstRequest.Fields("Monsternummer")
sCombi = sMonsternummer & RstRequest.Fields("Apparaatkode")
sJN = RstRequest.Fields("JN_Deelbepaling")
sPatientId = RstRequest.Fields("PatientNummer")
sSampleType = RstRequest.Fields("Materiaal")
Select Case sSampleType
Case "BL"
sSampleType = "1"
Case "UR"
sSampleType = "3"
Case "PL"
sSampleType = "2"
Case Else
sMessIn = "(CASE I)Case ELSE Sampletype --> CLOSE PROGRAM!!"
ListIn (sMessIn)
End Select
sLocation = RstRequest.Fields("Aanvr_Afdeling")
sPriority = RstRequest.Fields("Urgentie")
bSendReq = False
RstCheckRequest.Index = ("CombiKey")
RstCheckRequest.Seek "=", sCombi
If RstCheckRequest.NoMatch Then
sMessOut = " New request for " & sMonsternummer & "; added to local database"
ListOut (sMessOut)
RstCheckRequest.AddNew
RstCheckRequest.Fields("CombiKey") = sCombi
RstCheckRequest.Fields("JN_Deelbepaling") = sJN
RstCheckRequest.Update
bSendReq = True
Else
For iIndexJN = 1 To 99
If InStr(iIndexJN, sJN, "1") = 0 Then
If bAgain = True Then
sMessOut = " Request for " & sMonsternummer & " has already been sent to Dimension"
ListOut (sMessOut)
SendMessage "NRMC"
End If
Exit For
Else
If Val(Mid(sJN, iIndexJN, 1)) > 0 And Val(Mid(RstCheckRequest.Fields("JN_Deelbepaling"), iIndexJN, 1)) = 0 Then
sMessOut = " Request for " & sMonsternummer & " has changed, send again"
ListOut (sMessOut)
RstCheckRequest.Edit
RstCheckRequest.Fields("JN_Deelbepaling") = sJN
RstCheckRequest.Update
bSendReq = True
bAgain = False
End If
End If
Next iIndexJN
End If
If bSendReq = True Then
iTell = 1
iTestCount = 0
For iIndexJN = 1 To 99
If Mid(sJN, iIndexJN, 1) = "1" Then
'Zoeken in parametertabel naar volgnummer van test en bijbehorend testnummer inlezen in sTestOrder
iTestCount = iTestCount + 1
RstPar.Index = ("CombiAT")
RstPar.Seek "=", RstRequest.Fields("Apparaatkode") & Format(iIndexJN, "00")
If Not RstPar.NoMatch Then
sTestName(iTestCount) = RstPar.Fields("DimCode")
End If
End If
If InStr(iIndexJN, sJN, "1") = 0 Then 'Dan zijn geen 1 meer gevonden in string
Exit For
End If
Next iIndexJN
For iTell = 1 To iTestCount
'put the array listing of the separate testcodes into one variable
'separated by delimiters
sTestNameA = sTestName(iTell)
sTestNameB = sTestNameB & FieldDelimiter & sTestNameA
Next iTell
sTestCount = CStr(iTestCount) 'Converting Integer into String
sRecordCreated = CreateRecord("D", sPatientId, sMonsternummer, sSampleType, sLocation, sPriority, sTestCount, sTestNameB)
SendMessage "RQM"
DoEvents
End If
RstRequest.MoveNext
Wend
Else 'No records found
If bRetry = True Then
sMessOut = " Retry query niet gelukt"
ListOut (sMessOut)
End If
sMessOut = " Geen record gevonden in database, verstuur No Request Message"
ListOut (sMessOut)
SendMessage "NRMC"
End If
RstRequest.Close
Else
sMessOut = " bSuccess is False, verstuur No Request Message"
ListOut (sMessOut)
SendMessage "NRMC"
End If
QDfRequest.Close
Else
sMessIn = " ----> (Case I) SampleID FALSE, No Request Message is verzonden"
ListIn (sMessIn)
SendMessage "NRMC"
End If
-
Jul 3rd, 2007, 02:23 PM
#12
Re: Empty recordset while it shouldn't be empty
Also are you using a static cursor?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote