473,570 Members | 2,856 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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::Add New() and
CRecordset::Edi t() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Ope n(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWV ER
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::Ope n() 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
Jul 20 '05 #1
3 12098

"Silvio Lopes de Oliveira" <si*****@aaesys .com> wrote in message
news:s9******** ********@news.u swest.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::Add New() and
CRecordset::Edi t() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Ope n(), I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWV ER 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::Ope n() 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
Jul 20 '05 #2
Silvio Lopes de Oliveira (si*****@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::Add New() and
CRecordset::Edi t() cause an exception to be thrown with the error
message "Recordset is read-only". Stepping through the code for
CRecordset::Ope n(), I can see that it indeeds fail to open the recordset
with SQL_CONCUR_ROWV ER 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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
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" <sq*@hayes.ch > wrote in message
news:40******** **@news.bluewin .ch...

"Silvio Lopes de Oliveira" <si*****@aaesys .com> wrote in message
news:s9******** ********@news.u swest.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::Add New() and
CRecordset::Edi t() cause an exception to be thrown with the error message "Recordset is read-only". Stepping through the code for CRecordset::Ope n(),
I can see that it indeeds fail to open the recordset with

SQL_CONCUR_ROWV ER
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::Ope n() 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

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2487
by: Kevin Shea | last post by:
I'm currently running into a problem with ie6 on a windows 2000 machine. I've developed a web application where the client, using JavaScript, opens up an ADODB.Recordset recordset and then reads the data for the recordset from a recordset created on the server via XML through an ASP page. This normally works fine. However, for very big...
1
304
by: Ant | last post by:
Hello, I'm developing a program that will include a window showing current & previous records using a scrollable recordset. I need to add records to the recordset & have new records immediately available in the current record. What is the most common way of adding new records. Is it: i) by adding records via the same form & hence using the...
27
6176
by: Oscar | last post by:
I am looking for a way to pass an ADO recordset that has been retrieved in an ASP page to another HTML-page. Is there someone who can provide me with a small sample or a link to see how this is done? regards, Oscar
0
2643
by: CFW | last post by:
I thought this was going to be easy but I'm missing something . . . I need to open an ADODB recordset using the recordset source for a list box on my for. When my form opens, the list box ADODB recordset is established and set during On Open. Next I want to populate a recordset from that list box so I can filter it on a single field using...
3
1814
by: JB | last post by:
Hello, I am trying to write a query that selects a row from another table with a field that matches the Me!RecordingID that's on the form. For some reason my query is not working and the recordset I am trying to build will not open. Can someone help me with this? Thanks for your help! Else 'Update ItemsTable record with new info...
19
59924
by: bdt513 | last post by:
I am trying to extract the values from a query using VBA. Specifically, I want to concatenate all the values of the "rosEmail" field from query "qselRosterEmailList" into one string (strEmails). I can get one record's result by using the DLookup fuction, of course, but I want to get every record's value. To do this, I believe I need to...
2
7759
by: Sunil Korah | last post by:
I am having some trouble with opening recordsets. I have used code more or less straight from the access help. But still I am getting some errors. I am unable to work out what exactly I am doing wrong. 1.When I try the following code it gives the error message "compile error- type mismatch" on the last line. ( I have seen in past postings...
2
6069
by: ajspacemanspiff | last post by:
I currently have a solution that requires 2 sub queries, where each of them is convereted into a crosstab query and then I join the crosstab queries to a result. I would like to make this more dynamic and allow for users to filter, and to do so I would like to be able to create the resulting query all in VBA code. I can create a...
3
3114
by: Susan Bricker | last post by:
I might not have phrased the question correctly in the Subject of this post. Please read the entire explanation. I have a form with a command button (Add New Person). This button opens up another form (frmPeople) with a NewRecord (blank fields). The frmPeople form has a command button to save the record. I call this scenario ADD Mode. ...
10
2025
by: LucaBrasi | last post by:
I am using a DAO recordset based on a table on the back end, and the table has almost 11,000 rows. Is there a way to 'filter' the table to use only the rows that meet my criteria ? I know I could base the recordset on a query, but I need the criteria to be based on 3 text boxes on my main form. A command button on the main form, opens the...
0
7724
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7944
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7699
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5523
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5247
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3671
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2131
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.