QSqlQuery for ms access database
-
wrote on 29 Aug 2019, 18:15 last edited by
I wrote this code for sqlite database i need same query for ms access database
QSqlQuery query("SELECT * FROM names where name like '%' || ? || '%'"); query.addBindValue(QString("%1%").arg(st));
How could i change it to work for ms access ?
-
Hi,
Before changing the query, are you already connected to the Access database ?
-
wrote on 29 Aug 2019, 18:21 last edited by behruz montazeri
Yes it works on other queries.
I get this error :QODBCResult::exec: Unable to execute statement: "[Microsoft][ODBC Driver Manager] Function sequence error"
-
Did you check the error returned by the query ?I must say that it doesn't look correct but my SQL is a bit rusty.
Shouldn't the percent signes be directly doubled ?
-
Did you check the error returned by the query ?I must say that it doesn't look correct but my SQL is a bit rusty.
Shouldn't the percent signes be directly doubled ?
wrote on 29 Aug 2019, 18:37 last edited by behruz montazeri@sgaist
I tested several times doesn't work. -
What exactly did you test ?
-
wrote on 30 Aug 2019, 10:27 last edited by behruz montazeri
@sgaist
I changed the code to at least get it work :QSqlQuery query ; query.prepare("SELECT * FROM names where name like ? "); query.addBindValue(st);
my intention is searching everything from begin of the word or everywhere.
The query should be like this :*?*
But it doesn't work
Error :Syntax error (missing operator) in query expression 'name like '*' Pa_RaM000 '*''."
-
wrote on 30 Aug 2019, 12:04 last edited by
MS Access query syntax is different from t-sql. I would suggest using MS access itself and build your queries there, then take that as the baseline for your program's query strings.
-
MS Access query syntax is different from t-sql. I would suggest using MS access itself and build your queries there, then take that as the baseline for your program's query strings.
wrote on 30 Aug 2019, 13:20 last edited by@mrshawn
I want something like this :SELECT * FROM names where name Like "*"&[, the text that you want to use as a prompt, and then ]&"*"
How should i write in query.prepare ?
-
@mrshawn
I want something like this :SELECT * FROM names where name Like "*"&[, the text that you want to use as a prompt, and then ]&"*"
How should i write in query.prepare ?
wrote on 30 Aug 2019, 13:29 last edited by behruz montazeriquery.prepare("SELECT * FROM names where name Like '*'&[ st ]&'*' "); query.addBindValue(st);
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
What should i write to pass a parameter ?
-
wrote on 30 Aug 2019, 13:34 last edited by
Try changing
query.prepare("SELECT * FROM names where name Like '*'&[ st ]&'*' ");
to
QString st = "something"; query.prepare("SELECT * FROM names where name Like :st"); query.bindValue(":st", "*" + st + "*");
-
Try changing
query.prepare("SELECT * FROM names where name Like '*'&[ st ]&'*' ");
to
QString st = "something"; query.prepare("SELECT * FROM names where name Like :st"); query.bindValue(":st", "*" + st + "*");
wrote on 30 Aug 2019, 13:42 last edited by@mrshawn
There is no error but do not work. -
Can you write an example query that currently works for that use case ?
-
wrote on 30 Aug 2019, 16:45 last edited by
@sgaist
As i mentioned this one :QSqlQuery query ; query.prepare("SELECT * FROM names where name like ? "); query.addBindValue(st);
It works but i should write entire word i want to write a part of record in my case name record and it update QTableView
-
What I am asking for is an example without binding that is working. Once we have that it should be easier to come with a solution.
-
What I am asking for is an example without binding that is working. Once we have that it should be easier to come with a solution.
wrote on 1 Sept 2019, 10:23 last edited by@sgaist
I have no idea. -
wrote on 1 Sept 2019, 19:23 last edited by
Hi, the use of % instead of * is the right way. The * is only for using it directly on ms access query.
WHat version of ms access are you using? could you post a screenshot of your database/table?
-
Hi, the use of % instead of * is the right way. The * is only for using it directly on ms access query.
WHat version of ms access are you using? could you post a screenshot of your database/table?
wrote on 2 Sept 2019, 04:10 last edited by behruz montazeri 9 Feb 2019, 04:44@ronaldviscarral
Thanks for your response here is my screenshot :
link imaget -
Hi, the use of % instead of * is the right way. The * is only for using it directly on ms access query.
WHat version of ms access are you using? could you post a screenshot of your database/table?
wrote on 2 Sept 2019, 04:40 last edited by behruz montazeri 9 Feb 2019, 04:41This post is deleted! -
@sgaist
I have no idea.@behruz-montazeri said in QSqlQuery for ms access database:
@sgaist
I have no idea.Something like
"SELECT * FROM names where name like '%foo%'"
to confirm that one is working properly.
1/24