Hi all
I am trying to get data from Sqlite database
Table is Users and Primary key is UserID as integer
perhaps i do wrong the query to the database
as following code:
Var rs as RowSet
rs = db.SelectSQL("SELECT * FROM Users WHERE UserID =" number)
the above code does not work. perhaps syntax error?
the following code works as i see all my database records
rs = db.SelectSQL("SELECT * FROM Users ") //Get all of the data from Users
Thank you all in advance
You are missing a + in front of number and number needs to be a string:
dim rs as RowSet = db.SelectSQL("SELECT * FROM Users WHERE UserID =" + number.ToString)
God bless you for the help
i tried the code and get nothing…
i do not know the reason…
if i use the following code and use number 1 or 2 or 3 etc…
i get the specific records:
rs = db.SelectSQL("SELECT * FROM Users WHERE UserID =2" )
rs = db.SelectSQL("SELECT * FROM Users WHERE UserID = ?", number)
will work…
it does not work either
perhaps and it is sure the error comes earlier as i use 2 windows
in the first window user types UserID in TextField and then at Keydown Event i open the second window as following:
var w as new WindowListusers
w.TextField1.Text = number.ToText
w.callBack = self
w.ShowModal()
and at the second opened window at open event and i convert the text to number
number = TextField1.Text.ToInteger
is there any better way to pass data between 2 windows without all the mess i do above?
This doesn’t help much to know what is wrong. When you debug do you get an error (which?)
Do you know the value of ‘number’ when you do the SelectSQL? Maybe number = 0?
Can you share a sample project?
You can use a property of type integer in Window2 to insert the number before doing your database query…
God bless you.!!!
i typed
number = WindowUpdateUser.TextField1.Text.Val
yes now it works.!!
Thank you all above [Beatrix Willius], [Thomas Roemert], [AlbertoD] at your support.!!!
Good evening.
One more question to all friends above or anyone who can help:
i have a Sqlite database called entries a table called users
and a column called Username.
When i read data to fill a listbox by this command
rs = db.SelectSQL("SELECT * FROM Users")
and all record form database fill my listbox.
The problem is when i try to filter the username
where someone may enter a small part of user name
instead of whole Username
rs = db.SelectSQL("SELECT * FROM Users WHERE Usename = ", name)
i want to search all database and display all records that their username start from
“Smith” instead of writing the whole Username “John Smith” (John Smith is an example)
Thank you all in advance.
To get all the records with the field “Username” starting with “Smith” you have to write
"SELECT * FROM Users WHERE Username LIKE '" + name + "%' "
If you want all the record with Username containing “Smith” (e.g. “John Smith”) you have to write
"SELECT * FROM Users WHERE Username LIKE '%" + name + "%'"
Thank you so much. It works.!!!
Thank you also Emile very much for the info.!!!
i was trying to find syntax and tutorials.
Be well Friends.!!!
Please use
rs = db.SelectSQL("SELECT * FROM users WHERE username = ?", "%" + name + "%")
instead. This kind of query with a parameter array is automatically protecting your query against sqlinjection:
“Passing values as parameters will protect your database by automatically creating a SQLitePreparedStatement.” See docs: SelectSQL
And another small hint: to minimize datatransfer, avoid the use of * in a query, better name the columns needed, so only their content is read…
So your query could look like this (if you have columns named GUID, username and email in your table):
rs = db.SelectSQL("SELECT GUID, username, email FROM users WHERE username = ?", "%" + name + "%")
oooh that is really very usefull.!!!
Thank you also for the links.!!!
“Passing values as parameters will protect your database by automatically creating a SQLitePreparedStatement.” See docs: SelectSQL
Any suggestions that will make us better programmers are welcomed.!!!
Thank you again for your valuable time.!!!
i think it should be a like argument, this % or sometimes * is a joker char, means begins with anything or ends with anything.
rs = db.SelectSQL("SELECT GUID, username, email FROM users WHERE username like ?", "%" + name + "%")
you should also use order by username or without it could be a random order (i guess)
hi
please describe how can i put order by username in the above argument?
Try:
rs = db.SelectSQL("SELECT GUID, username, email FROM users WHERE username = ? order by username", "%" + name + "%")
As MarkusR pointed out, its better to use LIKE than =
rs = db.SelectSQL("SELECT GUID, username, email FROM users WHERE username LIKE ? ORDER BY username ASC", "%" + name + "%")
Erm yes, I agree, so consider my post suitably modified.
Ha, in fact actually mine wouldn’t work at all because @nektarios_borbilas is unlikely to have any usernames that start and end with a %.