[Solved] query exec fails when trying to write from qt to MS Sql database in windows using ODBC

Abin

Hi,

I connect to my MSSQL database with odbc and writing data to it. I am not sure why I get the error:

QODBCResult::exec: unable to bind variable: "[Microsoft][ODBC SQL Server Driver]Optional feature not implemented"

bool DataManager::registerClient(Client&)

"INSERT INTO ClientMaster (first_name, mid_name, last_name, phone_number, email_id, address_line1, address_line2, address_line3, dob) VALUES (xxx, xxx , xxx, +xxx-xxx, xx, xx, x, x, 1987-08-30)"

bool DataManager::registerClient(Client&) error "[Microsoft][ODBC SQL Server Driver]Optional feature not implemented QODBC3: Unable to bind variable"

@ mDatabase = QSqlDatabase::addDatabase("QODBC");

mDatabase.setDatabaseName("DRIVER={SQL SERVER};SERVER=localhost;DATABASE=GLINK_CLIENT_MGMNT;");
mDatabase.setUserName("sa");
mDatabase.setPassword("123456");

if(!mDatabase.open())@

@QSqlQuery query(mDatabase);

        query.prepare("INSERT INTO ClientMaster (first_name, mid_name, last_name, phone_number, "
                        "email_id, address_line1, address_line2, address_line3, dob)"
                      "VALUES (:first_name, :mid_name, :last_name, :phone_number, :email_id, "
                        ":address_line1, :address_line2, :address_line3, :dob)");

        QString phoneNumber = "+";
        phoneNumber.append(client.phoneCode);
        phoneNumber.append("-");
        phoneNumber.append(client.phoneNumber);

        query.bindValue(":first_name", client.firstName);
        query.bindValue(":mid_name", client.midName);
        query.bindValue(":last_name", client.lastName);
        query.bindValue(":phone_number", phoneNumber);
        query.bindValue(":email_id", client.emailId);
        query.bindValue(":address_line1", client.addressLine1);
        query.bindValue(":address_line2", client.addressLine2);
        query.bindValue(":address_line3", client.addressLine3);
        query.bindValue(":dob", client.dob);

        regStatus = query.exec(&#41;;@

hskoglund

Hi, just a guess but in your query.prepare() string, line 3: "....address_line3, dob)"
and line 4: "VALUES (:first_name,..."
shouldn't it there be a space between them,
i.e. line 4: " VALUES (:first_name,..."

Abin

Thanks hskoglund,

But it doesn't work.. I had found the same and had edited the code. In the logs I show the query executed. You can see there is space before "VALUES". I think the code i posted is before edit. sorry.

clochydd

Hi, did you look at query.lastError after query.exec()?
And is "xxx, xxx,... " what you expect as the content of client.firstName... ?

Abin

Hi Clochydd,

lastError is

“[Microsoft][ODBC SQL Server Driver]Optional feature not implemented QODBC3: Unable to bind variable”

yes.. there is real data in xxx which comes from client instance. I just hided it with xxx.

clochydd

Hi Abin, I would try this:

Use only one parameter, if successful two parm...
If this is not useful, replace client.firstName with a QString...
(I guess the error message points to a problem with datatypes)

Abin

Thanks all,

The issue got resolved. I hope the below case is the specific reason for the issue.

I gave date input in the format (yyyy-MM-dd), ie
query.bindValue(":dob", client.dob.toString(yyyy-MM-dd));

It works fine... :)

I came to know that MS Sql takes date in the particular format only. I not certain about this, however it works fine now...