I need to make a selection from a database but I am fairly sure I have the syntax wrong. This works for a line in a listbox but I have looking all day for a way to use the contents of a Text Field.
This is what I am using without any success;
sqlDelete = “Select * from Personell where VICID = '” + txtVICID.Text + “’”
It doesn’t seem to be able to find the line so it won’t delete it. I have a listbox with about 200 lines, a text box where I enter the beginning of a name which then reduces the items in the list as I type. Then I select the line I need to delete (or edit) and it copies the line identifier into a text box as the listbox no longer has all of the data.
I have a recollection of a similar problem years ago and I think it was the '" + part at each end that was different but I can’t remember what I did to fix it.
Just:
sqlDelete = “Select * from Personell where VICID =” + txtVICID.Text
BTW, “sqlDelete” is an unfortunate name…Also, I hope txtVICID is not an user enterable text field, as that would introduce many new problems (SQLInjection, SQL escaping).
It still didn’t delete the entry. It must be an error elsewhere in the code. txtVICID is not user defined, it is entered from the database. Thanks for the pointer for the sqlDelete, I will change it.
What I find really handy when I am having trouble like this is to use an external editor like Valentina or similar and enter my SQLite command there and try to execute it and see what happens. Then it’s just a matter of transposing the working SQLite statement to Xojo.
Now, I’m not entirely certain of what you are wanting to do. Is SqlDelete a recordset? Or is it part of a prepared statement.
When you do run the select what are you getting back as the recordset? Nil? Do you get back any fields? It is possible you have a field name incorrect or something.
So let’s start with that.
If you actually want to delete from the database though you may want to simple using:
SQLDelete:
DELETE FROM Personnel WHERE VICID = "+txtVICID.Text
But first, let’s find out what you are getting back from the select statement…
sqlDelete = “Select * from Personell where VICID =” + txtVICID.Text
If PersonellRegister.error then
msgbox "DB Error: " + PersonellRegister.errormessage
return
End
Dim rsDelete As RecordSet = PersonellRegister.SQLSelect(sqlDelete)
rsDelete.DeleteRecord
PersonellRegister.commit
PopulateList
rsDelete.Close
The contents of the text box is in a text field in the database but is a number. in another area wheere I pick the same information out of a list box, the statement; sqlDelete = “Select * from Personell where VICID = '” + txtVICID.Text + “’” works fine. If I try to take the information from a text box it doesn’t work.
sqlDelete = “Select * from Personell where VICID =” + txtVICID.Text
If PersonellRegister.error then
msgbox "DB Error: " + PersonellRegister.errormessage
return
End
Dim rsDelete As RecordSet = PersonellRegister.SQLSelect(sqlDelete)
rsDelete.DeleteRecord
PersonellRegister.commit
PopulateList
rsDelete.Close
[/code]
You do not need a recordset. This is the correct code:
[code]Dim sqlDelete as string = “Select * from Personell where VICID =” + txtVICID.Text
PersonellRegister.SQLExecute(sqlDelete)
If PersonellRegister.error then
msgbox "DB Error: " + PersonellRegister.errormessage
else
PopulateList
End[/code]
Now, the correct way to do this would be to use a prepared statement rather than a direct “+txtVCID.Text” as this could lead to SQL injection attacks. However, that is for another post for now.
Dim sqlDelete as string = "DELETE from Personell where VICID =" + txtVICID.Text
PersonellRegister.SQLExecute(sqlDelete)
If PersonellRegister.error then
msgbox "DB Error: " + PersonellRegister.errormessage
else
PopulateList
End
previous code was a SELECT statement not a DELETE statement
[quote=200626:@Dave S]actually this code is MORE correct
Dim sqlDelete as string = "DELETE from Personell where VICID =" + txtVICID.Text
PersonellRegister.SQLExecute(sqlDelete)
If PersonellRegister.error then
msgbox "DB Error: " + PersonellRegister.errormessage
else
PopulateList
End
previous code was a SELECT statement not a DELETE statement[/quote]
Goodness, you are right, Dave! How did I miss that?