problem with database

[Doctor Luz]

I am doing a simple editing of an acces database with the help of an ODBC connection and a CRecordset derived class.

m_pSet->Edit();
m_pSet->m_NAME=_T("Hello");
m_pSet->Update();

After the Update() statement I receive an exception:
AFX_SQL_ERROR_NO_ROWS_AFFECTED

I don't know what's happening and why receive this error.

I have found on MSDN that this error is produced when two users disturbs each other trying to access the same field if one of them has optimistic locking. The problems is threre is not any other user, only me.

I can't find more information.

Do anybody experienced this problem? Can anybody help me?

[sunny_sz]

Debug it step by step, and you will find where the error you mentioned occurred.

[Doctor Luz]

The error is in the Update statement.

[sunny_sz]

On an Update operation, one and only one record should be updated. That record is the current record, which corresponds to the data values in the fields of the recordset. If for some reason no records are affected or more than one record is affected, an exception is thrown.

[Doctor Luz]

There is only one record affected which is the current record. The record is valid since EOF() and BOF() returns both FALSE and you can see the data members. No body else is accesing that record.

The effect looks the same like if the record is being locked but then the question is Who in hell is locking the record if I am the unique user?

[JohnCz]

AFX_SQL_ERROR_NO_ROWS_AFFECTED does not tell much about an error.
You must have some more descriptive explanation.

The only scenario I can think of at the moment is:
- You have more than one column in a table
- The column you are not setting values does not allow NULL records.

Your problem may be completely unrelated to the scenario above; if so, consider posting your project and database file or test app that would duplicate the problem.

[srelu]

A quote from MSDN:

If no records were updated, or if more than one record was updated, an exception is thrown.

And another one from the same topic:

Update saves the data. Only those fields marked or detected as changed are updated.

So if the field already contains the text "Hello", no update is performed and an exception is thrown.

[Doctor Luz]

Thank you srelu and John

The code works good with other ODBC sources like MyODBC

I think there is something in the database that makes Update() fail.

I attach a simple application which reproduces the error.

I can't see where is the problem.

[GCDEF]

Does your table have a primary key or unique index? Have you stepped into the Update code to try to see exactly where it's failing?

[Doctor Luz]

My table does not have primary key or unique index. You can see it, I have attached it.

The debugger does not help too much because just after calling Update() jumps to strange dissasembled code like this which I don't understand

5F42C84E   push        5F4ABBB8h
5F42C853   mov         ecx,dword ptr [ebp-4Ch]
5F42C856   call        5F426967

[Doctor Luz]

The exception is trown in CRecordset::ExecuteUpdateSQL()

I'm not sure but looks like SQLExecDirect() fails.

The m_strUpdateSQL variable is:

UPDATE `DATA` SET `C10`=?,`C18`=? WHERE CURRENT OF SQL_CUR00396258

C10 and C18 are field names of my DB but in my example I was only trying to update C18

[Doctor Luz]

The problem is now solved.

In the DB I posted the field C10 was empty or at least looked like empty.
I entered some text in the C10 field, run the program and the program worked.
I made the C10 field again empty run the program again and the program worked as well.

What was the problem with the C10 field? I can't understand it since C10 was allowed to be empty and not required.

[JohnCz]

The reason you receive this message is because m_C10 is a null field. Obviously ODBC driver using cursor library is not able to update null field when database is open in a snapshot mode. Since this is not considered an error, it gives you only a message informing that nothing was updated.

After you have entered some string, you have changed field status to not-null; removing string changed a value to nothing but field was no longer null.

To test it change m_nDefaultType = dynaset.
Run following code using database verion with a trouble:

 		m_pSet->Edit();

		m_pSet->SetFieldNull(&m_pSet->m_C10, FALSE);
		m_pSet->SetFieldDirty(&m_pSet->m_C10);
		m_pSet->Update();

than change m_nDefaultType back to a snapshot and run your code again.
If you run your program using database wit a problem setting m_nDefaultType = dynaset you won’t experience any problem.

[Doctor Luz]

Thank you John.

I have to investigate about null fields. I think I don't fully understand what is a null field. I just thought empty=null but now I see this is not true.

Unfortunately the final release of this program is going to use MyODBC instead of Microsoft Access' ODBC. MyODBC does not support dynaset.

[JohnCz]

Null field is the field that does not have any value. When you create database and do not assign value to a field that field will be null.
You can also nullify field by using SetFieldNull with second parameter set to TRUE. The same with SetParamNull but I do not remember ever using it.
Having empty string as a value does not constitute a field as empty.