MFC/SQL Server 8 + ODBC problems

Hi. I get the feeling ive probably not understood something fundamental but im having issues with using CRecordSet / ODBC from MFC to access data in an SQL server 8 database.

I open the database without any problems using a DSN for SQL native client. THis opens fine and is updateable. I then get a record set out using the dynaset flag. This is where the problem occurs. If i use dynaset then the record is not updateable. If I use the cursorlib for the database, then it doesnt support dynaset anyway.

Now, the question is - should i be using dynaset anyway? I am trying to develop a multi-user database application. THe problem im looking at is I have a customer records screen. If 2 users both look at the record, they both try to update the record, how do you stop this happening. What is the best way to lock the record as soon as one starts to edit it.

I thought about using a locked field to say which user is locking the record, but then you run into the problem of the system crashing and leaving a record locked perminantly.

Ive looked time and time again in books and online but cant find a good answer to this problem? dynaset seemed to be better for this than snapshot as you can get details on updates by other users.

Any information would be great.

Thanks

Adrian

Check the CanUpdate property of CRecordset. Most likely it will return FALSE. Then bring up your query, and we’ll see if we can make it updateable.

Zlatko

|||

Yes - i use the CanUpdate to decide that the recordset is indeed not updateable. The query is simply a get all from a table so the sql in hte GetDefaultSQL() is return _T("[tblClients]");. It does seem there is a problem with using dynaset records. Ive now rewritten the application to use snapshot instead.

Adrian

|||

Adrian,

Let’s figure out first why your dynaset is not updatable. I need you to clarify a few things:

1. Does table tblClients have a primary key?

2. If you have single client that opens a dynaset from that table, is that dynaset updatable or not?

3. Do you pass any options to CRecordset.Open()?

4. Do you override DoFieldExchange() or DoBulkFieldExchange()?

If you can send me the code that opens the connection, the recordset, and that tries to update, that would be helpful.

Thanks,

Zlatko

|||

Adrian Brown wrote:

Hi. I get the feeling ive probably not understood something fundamental but im having issues with using CRecordSet / ODBC from MFC to access data in an SQL server 8 database.

I open the database without any problems using a DSN for SQL native client. THis opens fine and is updateable. I then get a record set out using the dynaset flag. This is where the problem occurs. If i use dynaset then the record is not updateable. If I use the cursorlib for the database, then it doesnt support dynaset anyway.

Now, the question is - should i be using dynaset anyway? I am trying to develop a multi-user database application. THe problem im looking at is I have a customer records screen. If 2 users both look at the record, they both try to update the record, how do you stop this happening. What is the best way to lock the record as soon as one starts to edit it.

I thought about using a locked field to say which user is locking the record, but then you run into the problem of the system crashing and leaving a record locked perminantly.

Ive looked time and time again in books and online but cant find a good answer to this problem? dynaset seemed to be better for this than snapshot as you can get details on updates by other users.

Any information would be great.

Thanks

Adrian

Hello man!

In first snapshot is the best way to use for Your application.

In second Your tabels it's must to have a cod_id for protection the records in this situation.

I use snapshot with CDatabase and CRecordset and I do not have any problem for this time.

Please escuse my English and have a nice day:

CDatabase dbDateServ;

CRs_Tab1* rsTab_1; // in my case CRs_Tab1 is a class derivved from CRecordset class mapp to table Tab1 from Sql Server

//2000

TRY

{

dbDateServ.Open("ODBC; DSN=SQL_Server; UID=sa; PWD=florin; DATABASE=DateServ");

}

CATCH(CDBException, e)

{

e->ReportError();

}

END_CATCH

rsTab1 = new CRs_Tab1(&dbDateServ);

TRY

{

rsTab1->Open(CRecordset::snapshot, "SELECT * FROM Tab1");

}

CATCH(CDBException, e)

{

e->ReportError();

}

END_CATCH

rsTab1->Close();

delete rsTab1;

dbDateServ.Close();