|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Error 440 or 3021doing wrong. At the line a = rs("ID") after the 19th iteration I get error 440 or 3021. 440 & 3021: Either BOF or EOF is true or the current record has been deleted. Requested operation requires a current record. Without this line the sub will go thru the whole file (I've run up to 532 text lines through it multiple times without this line). But when a = rs("ID") is in the code it will always return an error at the 20th iteration. I've been messing with this thing for over half a day and can't figure it out. Any help would be greatly appreciated. Thank you, Jim Sub PostTranscript() Dim fs, fFile Dim RetString As String Dim LineFields() As String Dim PhotoFileName As String Dim sSql As String Set fs = CreateObject("Scripting.FileSystemObject") 'Read member submission file Const ForReading = 1, ForWriting = 2, ForAppending = 3 Set fs = CreateObject("Scripting.FileSystemObject") Set fFile = fs.OpenTextFile(txtSubmissionFolder.Text & "\Transcript Submission.txt", ForReading, False) 'open rs Set rs = New ADODB.Recordset rs.CursorType = adOpenKeyset rs.LockType = adLockOptimistic Do While fFile.AtEndOfStream <> True 'Get current line RetString = fFile.ReadLine ReDim LineFields(18) As String ' Split line at tabs into array LineFields = Split(RetString, Chr(9)) 'Get photo file PhotoFileName = "'" & LineFields(1) & "'" 'Compose stmt sSql = "Select Photos.ID " sSql = sSql & "From Photos " sSql = sSql & "Where Photos.FileName = " & PhotoFileName & " And Photos.SubmissionID = " & CLng(txtBatch.Text) 'Get rs rs.Open sSql, cn, , , adCmdText a = rs("ID") 'Error 440 or 3021 here after 19th iteration rs.Close Loop fFile.Close End Sub Most possible because there is no matching record. Check using the
surrounding If statement. Do While fFile.AtEndOfStream <> True 'Get current line RetString = fFile.ReadLine ReDim LineFields(18) As String ' Split line at tabs into array LineFields = Split(RetString, Chr(9)) 'Get photo file PhotoFileName = "'" & LineFields(1) & "'" 'Compose stmt sSql = "Select Photos.ID " sSql = sSql & "From Photos " sSql = sSql & "Where Photos.FileName = " & PhotoFileName & " And Photos.SubmissionID = " & CLng(txtBatch.Text) /Henning 'Get rs rs.Open sSql, cn, , , adCmdText If Not (rs.BOF Or rs.EOF) Then a = rs("ID") 'Error 440 or 3021 here after 19th iteration Else MsgBox "There is no matching record!" End If rs.Close Loop Show quoteHide quote "Jim" <jc***@NOJUNKMAILsbcglobal.net> skrev i meddelandet news:9Ozwl.14026$8_3.944@flpi147.ffdc.sbc.com... >I wonder if someone could look at the following code and tell me what I'm >doing wrong. > > At the line a = rs("ID") after the 19th iteration I get error 440 or 3021. > > 440 & 3021: Either BOF or EOF is true or the current record has been > deleted. Requested operation requires a current record. > > Without this line the sub will go thru the whole file (I've run up to 532 > text lines through it multiple times without this line). But when a = > rs("ID") is in the code it will always return an error at the 20th > iteration. > > I've been messing with this thing for over half a day and can't figure it > out. Any help would be greatly appreciated. > > Thank you, > Jim > > Sub PostTranscript() > > Dim fs, fFile > Dim RetString As String > Dim LineFields() As String > Dim PhotoFileName As String > Dim sSql As String > > Set fs = CreateObject("Scripting.FileSystemObject") > > 'Read member submission file > Const ForReading = 1, ForWriting = 2, ForAppending = 3 > Set fs = CreateObject("Scripting.FileSystemObject") > Set fFile = fs.OpenTextFile(txtSubmissionFolder.Text & "\Transcript > Submission.txt", ForReading, False) > > 'open rs > Set rs = New ADODB.Recordset > rs.CursorType = adOpenKeyset > rs.LockType = adLockOptimistic > > Do While fFile.AtEndOfStream <> True > 'Get current line > RetString = fFile.ReadLine > > ReDim LineFields(18) As String > ' Split line at tabs into array > LineFields = Split(RetString, Chr(9)) > > 'Get photo file > PhotoFileName = "'" & LineFields(1) & "'" > > 'Compose stmt > sSql = "Select Photos.ID " > sSql = sSql & "From Photos " > sSql = sSql & "Where Photos.FileName = " & PhotoFileName & " And > Photos.SubmissionID = " & CLng(txtBatch.Text) > > 'Get rs > rs.Open sSql, cn, , , adCmdText > > a = rs("ID") 'Error 440 or 3021 here after 19th iteration > > rs.Close > Loop > fFile.Close > > End Sub > "Henning" <computer_h***@coldmail.com> wrote in message That should be And....not Or. You need to check if BOTH are False, not just news:49c2d11d$0$16212$57c3e1d3@news3.bahnhof.se... > > If Not (rs.BOF Or rs.EOF) Then one or the other. -- Mike
Show quote
Hide quote
"MikeD" <nob***@nowhere.edu> skrev i meddelandet Exatcly what it does, se NOT( ;)news:uDbL8xOqJHA.4108@TK2MSFTNGP06.phx.gbl... > > "Henning" <computer_h***@coldmail.com> wrote in message > news:49c2d11d$0$16212$57c3e1d3@news3.bahnhof.se... >> >> If Not (rs.BOF Or rs.EOF) Then > > > That should be And....not Or. You need to check if BOTH are False, not > just one or the other. > > -- > Mike > /Henning
Show quote
Hide quote
"MikeD" <nob***@nowhere.edu> skrev i meddelandet Using And BOTH BOF And EOF has to be true, ie an empty table.news:uDbL8xOqJHA.4108@TK2MSFTNGP06.phx.gbl... > > "Henning" <computer_h***@coldmail.com> wrote in message > news:49c2d11d$0$16212$57c3e1d3@news3.bahnhof.se... >> >> If Not (rs.BOF Or rs.EOF) Then > > > That should be And....not Or. You need to check if BOTH are False, not > just one or the other. > > -- > Mike > /Henning "MikeD" <nob***@nowhere.edu> wrote in message Immediately after opening the recordset you are either at EOF if it is empty news:uDbL8xOqJHA.4108@TK2MSFTNGP06.phx.gbl... > > "Henning" <computer_h***@coldmail.com> wrote in message > news:49c2d11d$0$16212$57c3e1d3@news3.bahnhof.se... >> >> If Not (rs.BOF Or rs.EOF) Then > > > That should be And....not Or. You need to check if BOTH are False, not > just one or the other. or not at EOF if there are records. Testing "If Not rs.EOF Then" is sufficient in every case I've come across. Is there a database driver where it makes a difference?
Show quote
Hide quote
"Bob Butler" <noway@nospam.ever> skrev i meddelandet I don't know for sure, I was once told to look for both. Before that I was news:%23GQZ9sPqJHA.1292@TK2MSFTNGP02.phx.gbl... > > "MikeD" <nob***@nowhere.edu> wrote in message > news:uDbL8xOqJHA.4108@TK2MSFTNGP06.phx.gbl... >> >> "Henning" <computer_h***@coldmail.com> wrote in message >> news:49c2d11d$0$16212$57c3e1d3@news3.bahnhof.se... >>> >>> If Not (rs.BOF Or rs.EOF) Then >> >> >> That should be And....not Or. You need to check if BOTH are False, not >> just one or the other. > > Immediately after opening the recordset you are either at EOF if it is > empty or not at EOF if there are records. Testing "If Not rs.EOF Then" is > sufficient in every case I've come across. Is there a database driver > where it makes a difference? > also just checking for EOF. I guess it doesn't hurt to check both. /Henning
Show quote
Hide quote
"Bob Butler" <noway@nospam.ever> wrote in message A need to check for both is necessary in a scenario where you are doing anews:%23GQZ9sPqJHA.1292@TK2MSFTNGP02.phx.gbl... > > "MikeD" <nob***@nowhere.edu> wrote in message > news:uDbL8xOqJHA.4108@TK2MSFTNGP06.phx.gbl... > > > > "Henning" <computer_h***@coldmail.com> wrote in message > > news:49c2d11d$0$16212$57c3e1d3@news3.bahnhof.se... > >> > >> If Not (rs.BOF Or rs.EOF) Then > > > > > > That should be And....not Or. You need to check if BOTH are False, not > > just one or the other. > > Immediately after opening the recordset you are either at EOF if it is empty > or not at EOF if there are records. Testing "If Not rs.EOF Then" is > sufficient in every case I've come across. Is there a database driver where > it makes a difference? > great deal of navigation (possibly combined with deletion) using .MoveNext (test for EOF), .MovePrevious (test for BOF), or .Move <+/- count> methods (test for both). This is because, by design, the "Move" methods don't normally check to see if the new position is pointing to a valid record until either the position is dereferenced or an EOF/BOF test is made. Complex, and at the time "optimized", data management routines that used ..Move <count> were common back in the day of structured storage but less frequent used (or even needed) with relational databases. In fact I believe Bob would have a little talk with any programmer working for him that wrote such a thing today. <g> IIRC there was also a problem with early drivers (Oracle?) that returned multiple recordsets, "empty" or "invalid" recordsets were set to BOF, but that was years ago. -ralph
Show quote
Hide quote
"Bob Butler" <noway@nospam.ever> wrote in message news:%23GQZ9sPqJHA.1292@TK2MSFTNGP02.phx.gbl... I just remember that many years ago (10 - 15) when I first started doing database development, my mentor (whom I had complete trust > > "MikeD" <nob***@nowhere.edu> wrote in message news:uDbL8xOqJHA.4108@TK2MSFTNGP06.phx.gbl... >> >> "Henning" <computer_h***@coldmail.com> wrote in message news:49c2d11d$0$16212$57c3e1d3@news3.bahnhof.se... >>> >>> If Not (rs.BOF Or rs.EOF) Then >> >> >> That should be And....not Or. You need to check if BOTH are False, not just one or the other. > > Immediately after opening the recordset you are either at EOF if it is empty or not at EOF if there are records. Testing "If Not > rs.EOF Then" is sufficient in every case I've come across. Is there a database driver where it makes a difference? > in) mentioned to always check that both were True to determine if 0 rows were returned (or conversely, both False to determine if at least 1 row was returned). As Ralph said, maybe that was a caveat with early drivers/providers. -- Mike MikeD wrote:
Show quoteHide quote > "Bob Butler" <noway@nospam.ever> wrote in message news:%23GQZ9sPqJHA.1292@TK2MSFTNGP02.phx.gbl... I often use disconnected ADO recordsets and filters. It makes a >> "MikeD" <nob***@nowhere.edu> wrote in message news:uDbL8xOqJHA.4108@TK2MSFTNGP06.phx.gbl... >>> "Henning" <computer_h***@coldmail.com> wrote in message news:49c2d11d$0$16212$57c3e1d3@news3.bahnhof.se... >>>> If Not (rs.BOF Or rs.EOF) Then >>> >>> That should be And....not Or. You need to check if BOTH are False, not just one or the other. >> Immediately after opening the recordset you are either at EOF if it is empty or not at EOF if there are records. Testing "If Not >> rs.EOF Then" is sufficient in every case I've come across. Is there a database driver where it makes a difference? >> > > I just remember that many years ago (10 - 15) when I first started doing database development, my mentor (whom I had complete trust > in) mentioned to always check that both were True to determine if 0 rows were returned (or conversely, both False to determine if at > least 1 row was returned). As Ralph said, maybe that was a caveat with early drivers/providers. > difference to this discussion. Try the following to see what I mean... Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset With rst 'CREATE AN EMPTY RECORDSET... .Fields.Append "Title", adVarWChar, 255, adFldIsNullable .Open 'ADD TWO RECORDS... .AddNew .Fields("Title").Value = "Smith's Crisps" .Update .AddNew .Fields("Title").Value = "Jelly Beans" .Update 'DISPLAY THE TWO RECORDS... .MoveFirst Do Until .EOF Debug.Print .Fields("Title").Value .MoveNext Loop Debug.Print String$(80, "=") 'Now it starts to get interesting... .Filter = "Title = 'Smith''s Crisps'" 'FILTER 'DISPLAYS ONE RECORD (AS IT SHOULD)... If Not (.BOF And .EOF) Then .MoveFirst Do Until .EOF Debug.Print "1. " & .Fields("Title").Value .MoveNext Loop End If 'STILL WORKS - BUT ONLY IF THERE ARE RECORDS IN FILTER .MoveFirst Do Until .EOF Debug.Print "1b. " & .Fields("Title").Value .MoveNext Loop 'BAD - DISPLAY NO RECORDS! If Not (.BOF Or .EOF) Then .MoveFirst Do Until .EOF Debug.Print "2. " & .Fields("Title").Value .MoveNext Loop End If 'BAD - DISPLAYS NO RECORDS! If Not .EOF Then .MoveFirst Do Until .EOF Debug.Print "3. " & .Fields("Title").Value .MoveNext Loop End If End With Set rst = Nothing As I obviously want to be able to reuse code, wherever possible, I can't use "If Not .EOF Then" - especially as I often pass recordsets to routines (which don't know if the recordset is filtered, at BOF or EOF, or somewhere else). Jason "Jason Keats" <jke***@melbpcDeleteThis.org.au> wrote in message Good catch, forgot about filters.news:uvpHuPjqJHA.3420@TK2MSFTNGP03.phx.gbl... > > As I obviously want to be able to reuse code, wherever possible, I can't > use "If Not .EOF Then" - especially as I often pass recordsets to > routines (which don't know if the recordset is filtered, at BOF or EOF, > or somewhere else). > Add .Find to the list of possible gotchas. -ralph "Jim" <jc***@NOJUNKMAILsbcglobal.net> wrote in message I assume you mean besides using the FSO? There's no reason whatsoever to use news:9Ozwl.14026$8_3.944@flpi147.ffdc.sbc.com... >I wonder if someone could look at the following code and tell me what I'm >doing wrong. the FSO in VB, as it has file I/O statements and functions (and can read/write files considerably more efficiently than the FSO). > At the line a = rs("ID") after the 19th iteration I get error 440 or 3021. When you get to that point in your loop, your SQL query isn't returning > > 440 & 3021: Either BOF or EOF is true or the current record has been > deleted. Requested operation requires a current record. > Without this line the sub will go thru the whole file (I've run up to 532 > text lines through it multiple times without this line). But when a = > rs("ID") is in the code it will always return an error at the 20th > iteration. > > I've been messing with this thing for over half a day and can't figure it > out. Any help would be greatly appreciated. anything in the recordset. If that's not to be expected, you need to look at your SQL query or perhaps the data in the table. Most likely, there's nothing in the table matching the criteria of your WHERE clause. To avoid the error, you should be checking the BOF and EOF properties. If both these are True, there are no records in the recordset. You should not rely on the RecordSet object's RecordCount property because depending on the cursor you're using, it may not be accurate. Also, may I suggest that you quit taking shortcuts with your code. Instead of a = rs("ID") write it out fully: If Not (rs.BOF And rs.EOF) Then a = rs.Fields("ID").Value End If Finally, since you're not doing any kind of updating of the recordset, you should be using a forward-only, read-only cursor in order to get the best performance. However, since you're also apparently only getting 1 row returned by the query (or at least only care about the 1st record), this is not really critical. I usually specify these in the Open method: oRS.Open sSQL, m_oConn, adOpenForwardOnly, adLockReadOnly, adCmdText -- Show quoteHide quoteMike > > Sub PostTranscript() > > Dim fs, fFile > Dim RetString As String > Dim LineFields() As String > Dim PhotoFileName As String > Dim sSql As String > > Set fs = CreateObject("Scripting.FileSystemObject") > > 'Read member submission file > Const ForReading = 1, ForWriting = 2, ForAppending = 3 > Set fs = CreateObject("Scripting.FileSystemObject") > Set fFile = fs.OpenTextFile(txtSubmissionFolder.Text & "\Transcript > Submission.txt", ForReading, False) > > 'open rs > Set rs = New ADODB.Recordset > rs.CursorType = adOpenKeyset > rs.LockType = adLockOptimistic > > Do While fFile.AtEndOfStream <> True > 'Get current line > RetString = fFile.ReadLine > > ReDim LineFields(18) As String > ' Split line at tabs into array > LineFields = Split(RetString, Chr(9)) > > 'Get photo file > PhotoFileName = "'" & LineFields(1) & "'" > > 'Compose stmt > sSql = "Select Photos.ID " > sSql = sSql & "From Photos " > sSql = sSql & "Where Photos.FileName = " & PhotoFileName & " And > Photos.SubmissionID = " & CLng(txtBatch.Text) > > 'Get rs > rs.Open sSql, cn, , , adCmdText > > a = rs("ID") 'Error 440 or 3021 here after 19th iteration > > rs.Close > Loop > fFile.Close > > End Sub > >  
Other interesting topics
Rubberband Line with ScrollBar
Active X and Vista Why there is a limit of 65,536 bytes when writing to file? Value > Long Data Type How to make this call from form to control Differences between VB amd VBA and VBA Education Application.Quit but Word remains open in Task Manager NET Required ??? Knowing what launched an EXE that used CreateProcess()? ShellExecute to html file in VB.net |
|||||||||||||||||||||||
