Solved QSqlQuery for ms access database

[behruz montazeri]

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 ?

[SGaist]

Hi,

Before changing the query, are you already connected to the Access database ?

[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"

[SGaist]

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 ?

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