Hi,
I have a window with a search text field, and a search button.
When the search button is pressed - I need to search my database for matches, then erase previous listbox entries, and then show all the matching results in the same listbox.
I have somehow managed to scrape up the code below, and I would appreciate it if someone could just tell me if the code is correct, or if I am on the right path.
Thank you all in advance.
[code]// ENSURE THE SEARCH FIELD IS NOT EMPTY
if NSSearchField1.text<>"" Then
dim sql as String = "SELECT * FROM Snippets WHERE Title LIKE "+NSSearchField1.text+" order by Title Asc"
dim rs as RecordSet = db.SQLSelect(sql)
if rs <> Nil then
// EMPTY LISTBOX ROWS
MainWindow1.Listbox1.deleteAllRows
// LOOP THROUGH RESULTS AND POPULATE LISTBOX
while not rs.EOF
MainWindow.Listbox1.AddRow(rs.Field("Title").StringValue)
rs.MoveNext
wend
// CLOSE THE RECORDSET
rs.Close
// CLOSE THE SEARCH WINDOW
SearchWindow.Close
else
// DISPLAY ERROR MESSAGE
MsgBox("No matching records found!")
// CLOSE THE SEARCH WINDOW
SearchWindow.Close
end if
else
// DISPLAY ERROR MESSAGE
MsgBox(“Please enter some text before searching!”)
Hi,
looks effective so far, but I would use PreparedStatements instead and bind the value you’re looking for. By this you would avoid SQL injections.
Also, it looks like you’re only deleting the entries from the listbox if the query was successful. I would do that prior the SQL command. If the result is nil, you should have a nil result, not an old one. Here, you close the window but if you don’t…
Another thing to bear in mind (and it may or not may not be important in this application)… but SQL statements such as “LIKE” (and other comparisons) are CASE SENSITIVE…
// DEFINE THE SQL STATEMENT
dim sql as String = “SELECT * FROM Snippets WHERE Title LIKE “+NSSearchField1.text+” order by Title Asc”
dim rs as RecordSet = db.SQLSelect(sql)
if rs <> Nil then
// LOOP THROUGH RESULTS AND POPULATE LISTBOX
while not rs.EOF
MainWindow.Listbox1.AddRow(rs.Field("Title").StringValue)
rs.MoveNext
wend
// CLOSE THE RECORDSET
rs.Close
// CLOSE THE SEARCH WINDOW
SearchWindow.Close
else
// DISPLAY ERROR MESSAGE
MsgBox("No matching records found!")
// CLOSE THE SEARCH WINDOW
SearchWindow.Close
end if
else
// DISPLAY ERROR MESSAGE
MsgBox(“Please enter some text before searching!”)
// DEFINE THE SQL STATEMENT
dim sql as String = “SELECT * FROM Snippets WHERE Title LIKE %”+NSSearchField1.text+"% order by Title Asc"
dim rs as RecordSet = db.SQLSelect(sql)
if rs <> Nil then
// LOOP THROUGH RESULTS AND POPULATE LISTBOX
while not rs.EOF
MainWindow.Listbox1.AddRow(rs.Field("Title").StringValue)
rs.MoveNext
wend
// CLOSE THE RECORDSET
rs.Close
// CLOSE THE SEARCH WINDOW
SearchWindow.Close
else
// DISPLAY ERROR MESSAGE
MsgBox("No matching records found!")
// CLOSE THE SEARCH WINDOW
SearchWindow.Close
end if
else
// DISPLAY ERROR MESSAGE
MsgBox(“Please enter some text before searching!”)
Wayne - I am initially trying to learn how to do it this way, and then when I know it is all working correctly and the code is efficient - I will then also convert it as a prepared statement in order to prevent the apostrophe catastrophe
This way I learn both ways. It’s more of a learning exercise.
After you helped me yesterday, I managed to create 2 other prepared statements alone - which edited and deleted database entries
I have never tried to search a database yet, so I started it this way and will then proceed to a prepared statement.
Your help was fantastic yesterday
First of all however, I need to know this looks correct.
