Recordset opens as read-only -- WHY?

Hello,
I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a MySQL database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here?
I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).
I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.
In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.
I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.
Thanks,
SL
"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here?
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL
>
This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:
http://support.microsoft.com/default...b;en-us;246636
However, I don't do much client-side coding, so I might be on completely the
wrong track here.
Simon
|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here?
It would have helped if you had posted the query.
I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.
When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.
The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||As a matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my MySQL database, the
primary key fields were not marked as such in the SQL Server tables.
Problem fixed. Thanks!
Silvio
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...[vbcol=seagreen]
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
a[vbcol=seagreen]
message[vbcol=seagreen]
> CRecordset::Open(),
> SQL_CONCUR_ROWVER
need[vbcol=seagreen]
> to
> defaults
little[vbcol=seagreen]
DRI)[vbcol=seagreen]
for[vbcol=seagreen]
plan[vbcol=seagreen]
help
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/default...b;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon
>