SQLite Syntax

Hi Folks,

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 + “’”

Can anyone point me in the right direction?

Cheers,

John.

What is the result of your SQL? How does it differ from the SQL with the listbox?

HI Beatrix,

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.

John.

If the “VICID”-Field is an integer field, you must not use the quotes in your SQL.

Hi Maximilian,

Do you mean the single or double quotes, and do I need the + at each end?

John.

The single quotes.

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.

John.

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…

Hi Jon,

The method consists of;

Dim sqlDelete as string

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.

John.

Well, I didn’t expect it to. Why don’t you try the simple:

sqlDelete = “Delete from Personell where VICID =” + txtVICID.Text

OK. If the field in the database is a text field then definitely put single quotes around the search item:

sqlDelete = “Select * from Personell where VICID = '” + txtVICID.Text + “’”

Now, I see a bigger problem though in that you are not checking for a NIL recordset. What is being returned in the recordset?

I think using the SQLDelete command is a far better approach.

Thanks everyone,

It is 23:45 here now and I am getting brain freeze. I will get some sleep and have a fresh look in the morning.

Thanks again for the input.

John.

Your posted code does not make sense. You posted:

[code]Dim sqlDelete as string

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.

2 things to do.

Examine the contents of sqldelete in the debugger. Pay attention to the length, both in characters and bytes. Look at the hex values, too.

Copy the contents of the string and paste it into an external query browser. Then type the same thing in by hand.

This should give you some clues.

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=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?

Stupid me…