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:
' 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
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.