MFC-DAO objects with SQL Server?

[deni4ka]

I have an application in MFC which works with Access db. Currently, I
transfer this application to work with MS SQL Server. I changed
CDAODatabase connection from mdb file to ODBC driver and application
manages (practicly without many changes) to present all the records in
the db (on SQL Server). Now, when I try to allow user to make chnges
in the db, it doesn't work. Objects of class CDAORecordset return
error while trying to do Edit() or Update() or AddNew() methods.
Please advice!

[John Bell]

Hi

How are you opening the recordset and database ?
Is dbReadOnly being used?
What does CanUpdate Return?

John

[deni4ka]

John Bell <jrm_...@hotmail.com> wrote in message news:<ili7sukt4m7uqkqio...@4ax.com>...

> Hi
>
> How are you opening the recordset and database ?
> Is dbReadOnly being used?
> What does CanUpdate Return?
>
> John
>

////////////////////////////////////////////////////////////

I solved the problem with Edit() it was because SQLServer imported db
without indexing. Because of this, Recordset became read-only. Now, I
have a different problem: when program reaches Update() I get an
error: ODBC -- update on linked table '???' failed .
Please advice.

Here is my code:

CDaoRecordset rs(m_pEcgDB);
txt.Format("%ld", Pat.mDB_ID);

SQLstr =_T("SELECT * FROM Patients WHERE (Patients.PLastName='");
SQLstr = SQLstr + AntiGeresh(Pat.LastName) + "' AND
Patients.PFirstName='";
SQLstr = SQLstr + AntiGeresh(Pat.FirstName) + "' AND
Patients.PIDNumber='";
SQLstr = SQLstr + AntiGeresh(Pat.ID) + "' AND Patients.PID<>" + txt +
");";
m_pEcgDB->m_pWorkspace->BeginTrans(); // Start the transaction
rs.Open(AFX_DAO_USE_DEFAULT_TYPE, SQLstr);
if (rs.IsEOF())
{
rs.Close();
SQLstr.Format(_T("SELECT * FROM Patients WHERE (Patients.PID=%
ld);"),Pat.mDB_ID);
rs.Open(AFX_DAO_USE_DEFAULT_TYPE, SQLstr);
if (!rs.IsEOF())
{
rs.Edit();
rs.SetFieldValue("PIDNumber", (LPCTSTR) Pat.ID);
rs.SetFieldValue("PLastName", (LPCTSTR) Pat.LastName);
rs.SetFieldValue("PFirstName", (LPCTSTR) Pat.FirstName);

COleVariant var;
var = (LONG) Pat.mDB_GroupID;
rs.SetFieldValue("PGroupID", var);
var = Pat.BirthDate;
rs.SetFieldValue("PBirthDate", var);
var = (BYTE) Pat.Sex;
rs.SetFieldValue("PSex", var);
var = (LONG) Pat.Weight;
rs.SetFieldValue("PWeight", var);
var = (LONG) Pat.mDB_Height;
rs.SetFieldValue("PHeight", var);

rs.SetFieldValue("PPhone1", (LPCTSTR) Pat.mDB_Phone1);
rs.SetFieldValue("PPhone2", (LPCTSTR) Pat.mDB_Phone2);
rs.SetFieldValue("PFax", (LPCTSTR) Pat.mDB_Fax);
rs.SetFieldValue("PEMail", (LPCTSTR) Pat.mDB_EMail);
rs.SetFieldValue("PAddress", (LPCTSTR) Pat.mDB_Address);
rs.SetFieldValue("PMedications",(LPCTSTR) Pat.mDB_Medications);
rs.SetFieldValue("POther", (LPCTSTR) Pat.mDB_Other);

rs.Update();

[John Bell]

Hi

The lack of indexes does seem to cause alot of problems!

I found this link:

http://support.microsoft.com/default.aspx?scid=KB;en-us;305617&

If this has been globally set, then you may start to have problems
elsewhere that expects NO COUNT to be on, for instance I believe
stored procedures called by ADO should have no count on to work in ASP
pages. Therefore you will need to go an change each SP and set it on
at the beginning and off at the end.

John

sde...@t2.technion.ac.il (deni4ka) wrote in message news:<c0d5a945.02111...@posting.google.com>...