Thread: Database probelm

Hi, i'm trying to learn some ODBC database programming and i'm using MS Access with MFC. As an exersice i started to create this database app which should contain information about some music bands. Inside Access, i have 2 tables: artistinfo and albums. 'artistinfo' should contain band names, genre and stuff like that. 'albums' should contain lists of albums for each band. That is, each field holds album names of one of the bands. Then in the MFC app, i have a combobox to load those albums names. When i hit the 'next' button in the app and go to a next record, correct album names should load into the combobox. Now my problem is that when adding more bands into the db at runtime, i must somehow append new fields to the albums table, and i'm not sure if this is possible. Is it possible? or is there another way altogether for doing this? i tried adding 'lookup columns' to the table at design time, but i cannot figure out how to use thoes lookup values from the MFC app. Any help please.

Thanks a lot.

>>i must somehow append new fields to the albums table, and i'm not sure if this is possible

Is possible. Have you derived a 'data consumer' class (Look at CRecordset) for each table in your DB. Can make working with the table easier.

I would use an index in the Band table (auto number should do, a GUID is not required).

Then use this as a key in the Albums table (ie each album record contains a field holding the index number of the band record).

When stacking the combo add a LPARAM value for each band which equals this index. Use CComboBox::SetItemData() / GetItemData(). When the combo selection changes find the new LPARAM (Band index number)

When adding to the DB sequence is something like (add error checks).

Sorry a bit rusty on ODBC so forgive me if this is not 100%.....

Code:

//find all albums by a band
//iComboLParam = index of band record from combobox LPARAM
CString   SQL;
//write the SQL query to find all the albums by the selected band
SQL.Format("SELECT * FROM AlbumsTable WHERE BandIDNumber = %d",iComboLParam);//sometimes the table 
//open the DB
CDatabase DB;
DB.Open();
//open the albums
CAlbumsTable    Albums(&DB);//open the Albums table using your derived class
Albums.Open(AFX_DB_USE_DEFAULT_TYPE, SQL, CRecordset::readOnly); //Open the recordset allowing only reads (no edit)


//add records to the albums
//open the DB
CDatabase DB;
DB.Open();
//DB.CanUpdate() test to see if you have a read only
//start a transaction
DB.BeginTrans();
//open the albums
CAlbumsTable    Albums(&DB);//open the Albums table using your derived CRecordset class and the open database
Albums.Open(CRecordset::dynaset); //Open the recordset 


//insert a record
TRY
{
	if(!Albums.IsEOF())//we have records / albums
	{
		Albums.AddNew();//insert empty record
		
		//fill in album record values
		
		Albums.Update(); //save record
	}
}
CATCH
{
	DB.Rollback();//clear changes on error
}
//tell DB to save all changes
DB.CommitTrans();
//close DB
DB.Close();