ODBC-MFC CRecordset::Update() Syntax error or access violation
- From: "Dorota Laudon" <DorotaLaudon@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 21 Jun 2005 08:35:04 -0700
SQL Server 2000 sp3 (and sp4)
Tool: VC++ 6.0 with the MFC
Connection: ODBC MDAC 2.8
Problem:
"Syntax error or access violation" error when the public role does not
select rights on the sysdatabases and the CRecorset::Update() is invoked.
The problem described below uses the Patient table as an example. This
problem persist through the whole application whenever we try to update the
data using the
CRecorset::Update();
/////////////////////////////////////////////
class Patient : public CRecordset
{
public:
Patient(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(Patient)
long m_PATIENT_ID;
.....
CString m_PAT_FNAME;
CString m_PAT_LNAME;
public:
virtual CString GetDefaultConnect();
virtual CString GetDefaultSQL();
virtual void DoFieldExchange(CFieldExchange* pFX);
CString Patient::GetDefaultSQL()
{
return _T("[dbo].[PATIENT]");
}
///////////////////////////////////////////////////////////////////////////
//We are connecting to the database using ODBC (DSNless connection) TCP/IP
CString CONNECTSTRING_EX;
CDatabase *currDatabase;
CONNECTSTRING_EX.Format("DRIVER={SQL
Server};DSN='';UID=MYDB_USER;PWD=Pwd;DATABASE=MYDB;" "SERVER=server1;Network=DBMSSOCN;Address=127.0.0.1,1456");
currDatabase->OpenEx( _T(CONNECTSTRING_EX), CDatabase::noOdbcDialog);
Patient *us = new Patient(currDatabase);
us->m_strFilter = "PATIENT_ID = 2" ;
us->Open(CRecordset::snapshot,NULL,CRecordset::none);
//The recordset opens fine and retrieves the right record.
//We want to update this record:
us->Edit();
us->m_PAT_FNAME = "Some_NAME";
us->Update();
/////////////// PROBLEM /////////////////////////////
Error Message: syntax error or access violation
///////////////////////////////////////////////////////
The user MYDB_USER is a member of the MYDB_USERS role that has select,
update, delete and insert rights on all users tables in MYDB database.
This user is able to update the Patient table from SQLQuery Analyzer.
Everything was working fine until the select permission on the public role
were removed in all system tables in the master database
When We investigated this problem, we discovered that the lack of the select
permission for the public role for sysdatabases table in master database is
the cause of the error.
We were looking for the symilar problem report but we have not been able to
find anything.
It seems that the MFC recordset is trying to acceess the master database
sysdatabase table.
.
- Prev by Date: Time out in fast updation of sql server database
- Next by Date: SQLFreeStmt crashes/leaks
- Previous by thread: Time out in fast updation of sql server database
- Next by thread: SQLFreeStmt crashes/leaks
- Index(es):
Relevant Pages
|
