Solved QSqlQuery for ms access database

[behruz montazeri]

@sgaist
I tested several times doesn't work.

[SGaist]

What exactly did you test ?

[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 '*''."

[MrShawn]

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.

[behruz montazeri]

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

[behruz montazeri]

@behruz-montazeri

query.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 ?

[MrShawn]

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 + "*");

[behruz montazeri]

@mrshawn
There is no error but do not work.

[SGaist]

Can you write an example query that currently works for that use case ?

[behruz montazeri]

@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

[SGaist]

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.

[behruz montazeri]

@sgaist
I have no idea.

[RonaldViscarraL]

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?

[behruz montazeri]

@ronaldviscarral
Thanks for your response here is my screenshot :

link imaget

[behruz montazeri]

This post is deleted!

[SGaist]

@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.

[behruz montazeri]

@sgaist
Yes that works.Then what should be inside the % % instead of foo for binding ???
what about binding ??

[RonaldViscarraL]

try this:

QSqlQuery  query ;
query.prepare("SELECT * FROM names where name like '%?%'" );
query.addBindValue("a");

[RonaldViscarraL]

I tested in Qt using access both 32 bits, this code works for me:

QString st = "a";
query->prepare("SELECT * FROM names WHERE name LIKE ?;");

QString criteria("%" + st + "%");
query->addBindValue(criteria);

[behruz montazeri]

@ronaldviscarral
Thank you very much it works now.