ODBC Command does not provide the count of inserted records

Question

I am using an ODBC connnection to connect to a database. When I execute OdbcCommand.ExecuteNonQuery with an insert statement, the records are inserted into the table, but the method returns 0.

ExecuteNonQuery returns the number of records affected. It's working fine in case of Delete and Update but not working in case of Insert.

query = "Insert into table1 (field1, field2) values (1,2)";

OdbcConnection = _remoteconn = new OdbcConnection(constring);
OdbcCommand cmd = new OdbcCommand(query, _remoteconn);
recordsAffected = cmd.ExecuteNonQuery();

Answer 1

ODBC is not a driver - it is a wrapper around a driver. The answer to this question is going to depend on the underlying ODBC driver that you are using, which would typically be specified in the Connection String.

The MSDN documentation is really more of an expectation or a suggestion, but the ODBC interface cannot force the driver to return a particular result. It may be that the driver has some optimizations or settings which are interfering with your results, not unlike SQL Servers NO COUNT setting (which would override a driver's attempts to report affected rows, etc).

See wikipedia for a more thorough explanation of how ODBC works:
http://en.wikipedia.org/wiki/ODBC

"Because different technologies have different capabilities, most ODBC drivers do not implement all functionality defined in the ODBC standard. Some drivers offer extra functionality not defined by the standard."

If you tell us which driver you are using, it may help to find a solution.

---

EDIT

To the best of my knowledge, the ODBC API function in question is the SQLRowCount function, as defined here:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms711835(v=vs.85).aspx

According to the ODBC standard, this function must be implemented for a driver to be considered ODBC-compliant. (That is not to say that the function will return the expected or correct result, just that it's there).

According the Transoft documentation (page 67) I've been able to dig up the SQLRowCount function was implemented. There is no mention of this function in any way being disabled or of any required configuration.

Given the above, I would contact the vendor directly. It would appear that there is an error in their implementation or an undocumented "feature".