Insert values into the database

Question

I have a table test with three columns ID,value and value_sim. I want to insert values to the value_sim column. I have written the following code but I am getting an error.

qry1->prepare("INSERT INTO TEST (VALUE_SIM)"
              "VALUES (:VALUE_SIM)"
              "where ID = 326");
qry1->bindValue(":VALUE_SIM", hxt_val_ft04);
qry1->exec();

hxt_val_ft04 is a float variable which has a value stored in it.

The following error is then raised:

Error: QODBC Result :: exec: Unable to execute statement: "[Microsoft] [SQL Native Client] [SQL Server] Incorrect syntax near the keyword WHERE [Microsoft] [SQL Native Client] [SQL Server] Statement (s). (s) could not be prepared. "

Kindly help me in correcting the error.

Please, try it, in order to find out whether it is a SQL error, or qt/c++ error. — Amartel
– Amartel, Commented Jun 29, 2015 at 9:32
I agree with @Amartel. Using some kind of SQL editor / db management studio is invaluable when doing database projects. Develop and test your SQL before using it in Qt/C++ code. — Michael Vincent
– Michael Vincent, Commented Jun 29, 2015 at 16:37

Spiek · Accepted Answer · 2015-06-29 12:28:19Z

1

I think you have a syntax error in your query, you cannot use WHERE in an INSERT statement, just try to remove this where condition:

qry1->prepare("INSERT INTO TEST (value_sim) VALUES (:VALUE_SIM);");
qry1->bindValue(":VALUE_SIM",hxt_val_ft04);
qry1->exec();

or you may want to write an update query:

qry1->prepare("UPDATE TEST SET value_sim = :VALUE_SIM WHERE ID = 326;");
qry1->bindValue(":VALUE_SIM",hxt_val_ft04);
qry1->exec();

Edit: For other's may interesting in the final solution:
"casting the value to float solves the problem"

qry1->bindValue(":VALUE_SIM", (float)hxt_val_ft04);

edited Jun 29, 2015 at 12:28

answered Jun 29, 2015 at 9:32

Spiek

3092 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Harish Ramamurthy Over a year ago

I changed it to the UPDATE command which u have specified above but I get an error again. ERROR: QODBCResult :: exec: Unable to execute statement:. "[Microsoft] [SQL Native Client] [SQL Server] Incorrect syntax near 'ID' [Microsoft] [SQL Native Client] [SQL Server] Statement (s) could (n) not to be prepared. "

Harish Ramamurthy Over a year ago

I have corrected the syntax error. But I get a new error again. ERROR : QODBCResult :: exec: Unable to execute statement: "[Microsoft] [SQL Native Client] [SQL Server] Operand type collision: varbinary is incompatible with real [Microsoft] [SQL Native Client] [SQL Server] Statement (s) could (n) are not prepared. "

Spiek Over a year ago

i have tested it with a mysql server, i get two different values in the query, double version --> UPDATE TEST SET value_sim = '1.23000000000000000e+000' WHERE ID = 326; cast to float version --> UPDATE TEST SET value_sim = '1.23' WHERE ID = 326; perhaps you can try casting to a double and look what happened: qry1->bindValue(":VALUE_SIM", (double)hxt_val_ft04); edit: of course i mean the oposite, i should read more carefully your question...

Spiek Over a year ago

you could try executing the query without prepare and look what happened: qry1->exec("UPDATE TEST SET value_sim = " + QString::number(hxt_val_ft04) + " WHERE ID = 326;");

Spiek Over a year ago

@HarishRamamurthy: You're welcome, i have updated my answer with this final solution :-)

|

Vijred · Accepted Answer · 2015-06-29 09:38:34Z

0

It looks like a SQL syntax error for me. Not sure how WHERE condition is used with Insert statement.

-- Example:
insert into test(value)values (1) -- Correct 
insert into test(value)values (1) where id = 1  -- Error

answered Jun 29, 2015 at 9:38

Vijred

3891 gold badge2 silver badges10 bronze badges

1 Comment

Harish Ramamurthy Over a year ago

Thank you I corrected the syntax but I get this error again. ERROR : QODBCResult :: exec: Unable to execute statement: "[Microsoft] [SQL Native Client] [SQL Server] Operand type collision: varbinary is incompatible with real [Microsoft] [SQL Native Client] [SQL Server] Statement (s) could (n) are not prepared. "

Collectives™ on Stack Overflow

Insert values into the database

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related