Unanswered: Error 3061: Parameters expected... Or is it?
I'm a dabbler in VB and I have written an application which catalogues my media. It is (was?) well-behaved but since adding extra function to it I've hit a problem in a sequence of operations that I may not have tested before. Anyway, the result is that VB is throwing up Error 3061 at me. I've checked on the net but I can't see any problems with the SQL - but we all say that don't we?
1. I create an SQL query in VB6 and search a JET database. It works fine and displays the retrieved records in a grid.
2. I have a Sort function on the form and I sort the recordset created in (1). I update the recordset's .sort property and perform an OpenRecordSet. This too works fine and I see the records in the desired sequence.
3. I click on a grid row and update the underlying record. I want to redisplay the form and I use the same code to produce the SQL query as used in (1); the sole difference being that the "Order By" clause is updated to reflect the recordset sequence set in (2).
The "Order by" clause in case (1) is "ORDER BY AlbumTitle, Trackno"
The .Sort property in (2) is "ORDER BY Track_XfldA"
The "Order by" clause in (3) is "ORDER BY Track_XfldA"
Using the IDE Immediate Window I've verified that the queries in (1) and
(3) are absolutely identical up to the final "Order by" clause.
In case it's relevant, XfldA is a table in the Track table which I select in the SQL query as Track_XfldA. I have tried sorting on Track.XfldA but it makes no difference.
Here's the final SQL query:
SELECT Category, Composer, ArtistName, Mp3key, Album.AlbumId As Album_AlbumId, AlbumTitle, Album.CategoryId As Album_CategoryId, Album.MediaId As Album_MediaId, Trackno, Time, Year, Track.ComposerId As Track_ComposerId, Track.ArtistId As Track_ArtistId, Quality, Tracktitle, Track.XfldA as Track_XfldA FROM (((Category INNER JOIN (Media INNER JOIN Album ON Media.MediaId = Album.MediaId) ON Category.CategoryId = Album.CategoryId) INNER JOIN Track ON Album.AlbumId = Track.AlbumId) INNER JOIN Artist ON Track.ArtistId = Artist.ArtistId) INNER JOIN Composer ON Track.ComposerId = Composer.ComposerId WHERE (ArtistName NOT LIKE '<<*') ORDER BY Track_XfldA