An update.

When I opened the project in 2019r1.1 and changed to RecordSet, EOF, and SQSelect (API 1.x) it works as planned.

Matthew

@Matthew M When I opened the project in 2019r1.1 and changed to RecordSet, EOF, and SQSelect (API 1.x) it works as planned.

You can use/run API 1 code in 2019r2+. Maybe try that to make sure it's not a plugin issue.

If that works, then try a Select count(*) in your code above to verify the automatic prepared statement is working correctly.

i think your filter is wrong
try this

WHERE PrimarySub LIKE ?

and filter text with ms sql is "%abc%"
you would find anything that contains abc

% is a wildcard character

Jay,

The API 1.x code ran fine 2019r2.1. The COUNT statement returned 46 rows, as expected without the limiting TOP(10).

Markus,

LIKE is my preferred conditional and I generally use the & wildcards. In this situation I was trying to eliminate any variation from the documentation examples in the event said variance was the problem. I did, however, try this but the results were the same.

Thanks to both of you for your suggestions.

@Matthew M The API 1.x code ran fine 2019r2.1. The COUNT statement returned 46 rows, as expected without the limiting TOP(10).

Maybe try it without the TOP limit, so see if that is an issue in API 2.

No change.

SELECT TOP(10) AS Detail ....
Perhaps?

"SELECT * FROM tDigitalCatalog WHERE PrimarySub = ? ORDER BY SomeField DESC LIMIT 10"

Use Limit instead of top if it doesn't work for your db

unfortunately it is not possible to see the generated query that SelectSQL made :(

have you test this style if it makes a difference?

    For Each row As DatabaseRow In rsDetails
      lstResults.AddRow(row.Column("Detail").StringValue)
    Next

Derk - The LIMIT parameter throws a syntax error with MSSQL.

Markus - I did try the DatabaseRow format, but had the same problem.

Thanks for both suggestions.

@Matthew M Derk - The LIMIT parameter throws a syntax error with MSSQL.

Markus - I did try the DatabaseRow format, but had the same problem.

Thanks for both suggestions.

Try your query without () so ... TOP 10 ...

More here:

In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it's the only way to predictably indicate which rows are affected by TOP.

https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15

Still no change. It seems that I will have to use 2019r1.1 until such time that this is fixed.

@Matthew M Still no change. It seems that I will have to use 2019r1.1 until such time that this is fixed.

You'll need to submit a Feedback case if it is to be fixed.

One other thought, what if you try it without a prepared statement? Just build the entire query string including the where value, like the first example code for SelectSQL .

Funny you should mention that. I did just try that while preparing my Feedback case and it works fine. It seems to have something to do with the SQL statement preparation so I'm going to look into that a bit more.

Since this is to be a web app I really want to use prepared statements for security reasons.

Thanks.

Matthew,

I just created a test app in 2019R2.1 and ran it against Express 2012 and 2019 and both work correctly, using the automatic prepared statement like you show. So it must be something with your installation or setup.

@Markus R unfortunately it is not possible to see the generated query that SelectSQL made

For future reference, if you want to see the SQL text being sent to SQL Server from local or external applications (and you have admin privileges on the Server) try SQL Profiler . It is part of the MS SQL Server Management Studio install (free).

Of course you'll have to install and run Profiler from a Windows machine.

I hope that helps.

So Jay's works and ours doesn't. What version of SQL are you hitting?