how to resolve inconsistent query results (1 Viewer)

[silverblatt]

I'm using the SQL shown below to query a simple Access database. When I start a new query in that database, paste the SQL code into SQL view and then run the query, it performs as expected, i.e., it returns all the records where the string "red" appears anywhere in any of the three columns referenced in the WHERE clause. So far, so good. However, when I use the identical query in a VB.Net 2017 application, it returns 0 records but does not throw any exceptions. When I remove the WHERE clause in the VB.Net application, the query returns all records as expected.

How can I resolve this inconsistency? What I want to do is have the query work the same way in the VB.Net application as it does in a native Access query.

SELECT Max(Artist.ArtistID) AS ArtistID, Max(Artist.ArtistName) AS ArtistName, Song.SongID,
Max(Song.SongTitle) AS SongTitle, Max(Song.Deprecated) AS Deprecated, Max(Played.PlayDate) AS LastPlayed
FROM ((Artist INNER JOIN Song ON Artist.ArtistID = Song.ArtistID)
LEFT OUTER JOIN Played ON Song.SongID = Played.SongID)
LEFT OUTER JOIN SongAttribute ON Song.SongID = SongAttribute.SongID
WHERE ((Artist.ArtistName LIKE '*red*') OR (Song.SongTitle LIKE '*red*') OR (Song.Comments LIKE '*red*'))
GROUP BY Song.SongID
ORDER BY Max(Artist.ArtistName), Max(Song.SongTitle), Max(Played.PlayDate)

 

[Gasman]

So vba is different to vb.net?
I have never seen order by Max() ?
There can only be one max?

Remove the max on order by fields and run again in all environments.

 

[pbaldy]

Try % instead of * as the wildcard.

 

[plog]

Can you show your VB.net code? My gut is telling me its the single quotes around your criteria. Of course I'd expect a syntax error by the debugger though.

 

[silverblatt]

Here's the operative code from the VB.Net application. It works as expected when there is no WHERE clause in the SQL code:

            'ConnectionString string variable is pre-loaded with connection string for Access database
            Using cn As New OleDb.OleDbConnection(ConnectionString)
                'code here to construct SQL text and assign it to string variable SQL
                Using da As New OleDb.OleDbDataAdapter(SQL, cn)
                    Using dt As New DataTable
                        da.Fill(dt)
                        RecordCount = dt.Rows.Count
                    End Using
                End Using
            End Using

 

[silverblatt]

Changing the asterisks to percent signs in the VB.Net application solved the problem, but I'm really baffled by that. The MS Access documentation specifies the asterisk as the wildcard character for multiple characters, and does not mention the percent sign at all, and it is the Access database engine that's processing the query. I know an MS-SQL database would use the percent sign, but again this is Access.

 

[pbaldy]

It was an educated guess. I haven't used VB.Net but even within Access an ADO recordset requires the % where a DAO recordset (and pretty much everything else) requires the *.

 

[Gasman]

Changing the asterisks to percent signs in the VB.Net application solved the problem, but I'm really baffled by that. The MS Access documentation specifies the asterisk as the wildcard character for multiple characters, and does not mention the percent sign at all, and it is the Access database engine that's processing the query. I know an MS-SQL database would use the percent sign, but again this is Access.

So vba is not like vb.net in some respects?

 

[silverblatt]

I guess it's just another weird quirk in the world of Microsoft. Thanks for the quick help, pbaldy!

 

[pbaldy]

I guess it's just another weird quirk in the world of Microsoft. Thanks for the quick help, pbaldy!

Happy to help!

 

[Pat Hartman]

I guess it's just another weird quirk in the world of Microsoft. Thanks for the quick help, pbaldy!

Native Access uses DAO.. Why DAO was designed with different wildcard characters is a question for history. ADO is more closely aligned with SQL Server and other RDBMS'.

 

[ebs17]

and does not mention the percent sign at all

In the access options you can set:
SQL Server compatible syntax (ANSI92)
Then the percent sign is used as a wildcard.

ANSI89 is used as standard.

The History of SQL Standards

Find out how the SQL standard has changed over 30 years, from SQL-86 to the standard data language of the 21st century.

learnsql.com