ODBC Recordset "Read-Only" ?

[fdsouth]

I've created an application that uses a SELECT statment to pull data from multiple tables. I'm not able to update the results. I then tried pulling data from a single table and it did update. Is there a restriction on CRecordset which doesn't allow editing if the recordset is from multiple tables (join)?

 

[Cagliostro]

It is a nonsence to update data what you've gotten from a select statement, because it is a dinamically created snapshot. Maybe you can create a cursor for updating, but the rules are the same as in the database driver. John Fill

ivfmd@mail.md

 

[fdsouth]

I am just a novice programmer, so please have patience. What I need to do is update several fields in three different tables, preferably in one view. Therefore, I used the following SELECT statement to bring up the current data to see if anything needs changing:

CString CDHR_UpdateSet::GetDefaultSQL()
{

	return _T("SELECT DISTINCT [ORDNUM_43], [OPRSEQ_43], [LOGINDATE_43], [INDATE_43], [TNXDTE_43], [ORDNUM_14], [OPRSEQ_14], [MOVDTE_14], [CURCOM_14], [LOTNUM_72], [ORDNUM_72], [OPRSEQ_72]"
			  "FROM [Employee Work], [Job Progress], [Lot Tracking Hist]"
			  "WHERE [Employee Work].[ORDNUM_43] = [Job Progress].[ORDNUM_14] AND [Employee Work].[ORDNUM_43] = [Lot Tracking Hist].[ORDNUM_72] AND [Employee Work].[OPRSEQ_43] = [Lot Tracking Hist].[OPRSEQ_72] AND [Employee Work].[OPRSEQ_43] = [Job Progress].[OPRSEQ_14]"
			  "ORDER BY [Employee Work].[ORDNUM_43], [Employee Work].[OPRSEQ_43]");
}

Once the data is in the view, the user can change the data as needed then hit a button to UPDATE the respective tables.
The SELECT statement is nice because I can sort the data the way I need to for the end user.

Are you saying that with a SELECT statement it is not possible to update separate fields from different tables since the data I'm seeing is really a snapshot? If I can't, how would you approach this problem?

Thanks for your help.

 

[Cagliostro]

you can't do it. What you can do, is to update the tables, using different statements and update cursors. John Fill

ivfmd@mail.md

 

[fdsouth]

One last bit of help (hopefully). Currently the UPDATE statement looks like this:

void CDHR_UpdateView::OnRecordUpdate() 
{
	m_pSet->Edit();
	UpdateData(TRUE);
	if (m_pSet->CanUpdate()) {
		m_pSet->Update();
	}
}

To be able to update the fields, I'm going to have to replace this with an "UPDATE" SQL statement? If so, do you have an example of the syntax. I understand how to use SQL statements in the GetDefaultSQL function to open the tables (in CDatabaseSet class derived from CRecordSet). How would the syntax look in other classes (i.e. CDatabaseView where the update function is)?

Again, thanks for the help. It sucks being a newbie.

 

[Cagliostro]

no, update statement look like
SQLExecDirect(....."update xxx set yyy=zzz"...); John Fill

ivfmd@mail.md