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 3 12125
"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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 data sets, the browser on the client side hangs, just
stops responding.
|
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 same recordset?
(This seems the simplest way as you can keep the scope of the data object
local to...
|
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
|
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 the value of a combo box for the filter string. I have a second
combo box that i woul like to use...
|
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
Debug.Print ("attempting to open recordset")
If Me.ItemID <> Null Then
| |
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 build a recordset, but I do not know how.
I've read about doing it using DAO in Access 97, but I am...
|
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
that this error is usually because of DAO/ADO mismatch. Is the
recordsource for forms in Access 2000...
|
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 recordset of each inital query, but is there a way to
run a query on that, and then subsequently, a query...
|
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. When the record is saved we stay in ADD Mode and get
another NewRecord.
This form (frmPeople)...
|
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
recordset and performs the actions on that recordset, but I think it
would be much more efficient to...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |