Query Returning a Blank recordset NOT an empty one

[Loggerhead2000]

Hello,
My problem is when my VB 6.0 application queries my Access database (set up through Visual Data Manager). My SQL statement work fine, it find the record when it should and returns with nothing when it doesn't find a match. The problem I'm having happens when it's should return no records found, but insted of send back an empty recordset it sends back recordset of 1( I use Recount to get the number of records),and all it feilds are blank. I've tried using EOF and BOF but both indicate that there is a record in the recordset. I'v try Binding the Controls in the If stmt and out side doesn't have any effect on the outcome
[/i]
If adoPlay.EOF Then
Label9.Caption = "EOF "
Else
Label9.Caption = " not EOF "
End If

If adoPlay.BOF Then
Label9.Caption = "BOF"
Else
Label9.Caption = Label9.Caption & " not BOF"
End If

Label8.Caption = adoPlay.RecordCount

If adoPlay.BOF Or adoPlay.EOF Then
MsgBox "Could not find the song"
else
Set lblSong.DataSource = adoPlay
lblSong.DataField = "Song"

MsgBox "The Song selected was found"
End If
[/i]
I could store the recordset feilds in variable and check for "" but I thought there has to be a easier way then that. If anyone can suggest a fix to my problem that would be great

Thanks Timmeh

 

[SemperFiDownUnda]

I'm a bit confused to your statement
"...returns with nothing when it doesn't find a match. The problem I'm having happens when it's should return no records found,"

what is the difference between it returning "nothing" and what it does when "no records found"?

as for your blank record I'd say you actually have a blank record in your database.

I would have to see the code you actually populate the adoPlay recordset with to say more.

 

[CCLINT]

I can see what is maybe happening:
If you have functions in the query, then even if no records are found, the recordset is not empty. It will return 1 record.
This is correct and should be so.
Look at the following:

SELECT Count(*) As Total FROM SomeTable WHERE TRUE = 0.

Obviously, this recordset will not find any records.
But the function will return a value as requested: ZERO

So, even though no records are found, I can still get the value returned by the Count function:

rs.Fields(0).Value

will return 0.

 

[Loggerhead2000]

CCLINT
Yes I do have functions in query,but I don't really understand the Code you showed me or how I can use it to solve my problem.

 

[SemperFiDownUnda]

Show us the SQL statement you are using.

 

[Loggerhead2000]

Here is the SQL I use, it looks for a the path to a mp3 file that corresponds to what they click in the datagrid.

SELECT TrackList.TrackFilePath FROM TrackList WHERE TrackList.Album_ID = ( SELECT Album.Album_ID FROM Album WHERE Album.Album_ID = lblAlbumID.Caption) and TrackList.Track_Num = dgdSongList.Columns(0)


Private Sub dgdSongList_DblClick()
Dim strSelect As String
strSelect = "Select TrackList.TrackFilePath from _
trackList where TrackList.Album_ID =" _
& "(Select Album.Album_ID From Album Where _
Album.Album_ID= " & lblAlbumID.Caption & &quot and _
TrackList.Track_Num = " & dgdSongList.Columns(0)

Dim adoPlay As Recordset
Set adoPlay = New Recordset


adoPlay.Open strSelect, db, adOpenStatic,
adLockBatchOptimistic

Set lblTrackFilePath.DataSource = adoPlaySong
lblTrackFilePath.DataField = "TrackFilePath"

If adoPlaySong.BOF Or adoPlaySong.EOF Then
MsgBox "Could not find the song "
Else
MediaPlayer.FileName = lblTrackFilePath.Caption
End If

End Sub