RowSet Returning Only a Single Row

I keep my hair cut short so that I don’t pull it out in patches. This issue would do it to me.

I am building a very simple quick and dirty (so I thought) web app using API 2.0 to demonstrate to my client what they could do with an upgrade to a VB6 program that I wrote for them 15 years ago. My only prior web app was under API 1.0 and didn’t return multiple rows of data.

The web page has a text box, a button, and a list box with one column. User enters a keyword, clicks button, and all results go into the list box. The backend database is MSSQL Express 2016. The test query should return 10 rows. The code:

[code]’ Show search results

dim strQuery As String
dim strInput As String

dim rsDetails As RowSet

’ First get the info
if (cnMSSQL0 <> Nil) then
strInput= txtSearchTerms.Text.Trim

Try
strQuery = “SELECT TOP(10) Detail FROM tDigitalCatalog WHERE PrimarySub = ?”
rsDetails = cnMSSQL0.SelectSQL(strQuery, strInput)

lstResults.DeleteAllRows
lstResults.ColumnCount = rsDetails.ColumnCount

while (not rsDetails.AfterLastRow)
  lstResults.AddRow(rsDetails.Column("Detail").StringValue.Trim)
  rsDetails.MoveToNextRow
wend

rsDetails.Close

catch error as DatabaseException
MessageBox("Error: " + error.Message)
end

end

Return
[/code]

I have based my code on examples in the documentation, and I have used the prepared statement to directly query the database (where it returns 10 rows). When I step through the code I see it enter the While loop, add one row to the list box, move to the next row, and exit the loop. I get the same behavior if I use the RowSet class.

I am fully convinced that this issue is a direct result of my ignorance (ie: I’ve overlooked some small thing), but I am at a loss to identify the culprit. I should also mention that I used the same exact code in a desktop app and got the same exact behavior.

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

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.

[quote=465692:@Matthew Mills]The API 1.x code ran fine 2019r2.1. The COUNT statement returned 46 rows, as expected without the limiting TOP(10).
[/quote]
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 :frowning:

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.

[quote=465752:@Matthew Mills]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.[/quote]

Try your query without () so … TOP 10 …

More here:

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.

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.

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.

I have the same problem, simple select sql, works in 2019.1.1, the first row I get in the try catch is empty

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