1.4.8 Read and Write Recordset in workshhet

Example Codes

Running "ReadRecordset2Wks" to find all entrys from stars access database Stars table and put into one worksheet.

Then do some change in this worksheet and run "WriteWks2Recordset" to put back this change to table;

However, if run "WriteWks2Recordset 0" will append all rows from worksheet to table. NOTE if you wants to use append, and there is a Key field which not allow duplicate data, then you have to make sure after appending no duplicate data in key field/column.

• ---The example will update build-in database Samples\Import and Export\stars.mdb, so please backup this file before running the following codes.

#define DB_SETUP_STR			"Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source="
#define DB_FILE_FULLPATH		GetAppPath(TRUE) + "Samples\Import and Export\stars.mdb"
#define TABLE_NAME 				"Stars"

 
//function to retrieve data and put into a worksheet
int ReadRecordset2Wks()
{	
	//create the ADODB.Recorset object
	Object ocrs = CreateObject("ADODB.Recordset");	
	if( !ocrs )
	{
		out_str(" ADO init error!");
		return -1;
	}
 
	// prepare and open database recordset
	string strConn = DB_SETUP_STR + DB_FILE_FULLPATH + ";";
	string strQuery = "select * from "+ TABLE_NAME + ";";
	ocrs.CursorLocation = 3;
	ocrs.open(strQuery, strConn, 1, 3);	
 
	//prepare worksheet
	Worksheet wks;
	wks.Create("origin");
 
	//read data into the worksheet.
	int nRet = wks.ReadRecordset(ocrs);
	if ( nRet )
		return nRet;
	if (ocrs.State == 1 ) //adStateOpen
         ocrs.Close();
	return 0;
}
 
 
// when bReplace is true, it will delete all entry firstly then copy data from current worksheet to table.
// NOTE if you wants to use append, and there is a Key field which not allow duplicate data, then you have to make sure after appending no duplicate data in key field/column. 
int  WriteWks2Recordset(bool bReplace = TRUE)
{
	Worksheet		wks = Project.ActiveLayer();
	if(!wks)
	{
		// assume use active wks
		out_str("NO active wks!");
		return -1;
	}
 
	Object ocrs;
	ocrs = CreateObject("ADODB.Recordset");
	if( !ocrs )
	{
		out_str(" ADO init error!");
		return -1;
	}
	
	// Get Access file name
	string strDBFileName = DB_FILE_FULLPATH;
	if ( !strDBFileName.IsFile() )
	{
		out_str("Invalid database file path!");
		return -1; 
	}

	// prepare and open database recordset
	string strConn = DB_SETUP_STR + strDBFileName; 
	ocrs.CursorLocation = 3;
	string strQuery = "Select * From "+ TABLE_NAME  + ";";
	ocrs.open( strQuery, strConn, 0, 3);
 
	int nOption = LAYWKSETRECORDSET_APPEND;	
	if ( bReplace )
		nOption = LAYWKSETRECORDSET_REPLACE;

	//write worksheet data to recordset.
	int nRet =  wks.WriteRecordset(ocrs,nOption );
	out_int("nRet = ", nRet);
	if ( nRet )
		return nRet;
	if (ocrs.State == 1 ) //adStateOpen
         ocrs.Close();
	return 0;
}

open method prototype and its explaination

/**
QueryString : Query string build by user using SQL language

ActiveConnectionString : The string to indicate the connection method to a database. This sample only shows a string using SQLOLEDB as provider to connect to an access database.

CursorType : [optional]
= 2 
	Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, 
	and all types of movement through the Recordset are allowed, except for bookmarks, 
	if the provider doesn't support them. 
= 0 
	Default. Uses a forward-only cursor. Identical to a static cursor, except that you 
	can only scroll forward through records. This improves performance when you need to 
	make only one pass through a Recordset. 
= 1 
	Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that 
	other users add, although records that other users delete are inaccessible from your Recordset. 
	Data changes by other users are still visible. 
= 3 
	Uses a static cursor. A static copy of a set of records that you can use to find data 
	or generate reports. Additions, changes, or deletions by other users are not visible. 
= -1 
	Does not specify the type of cursor.

LockType : [optional]	
= 4 
	Indicates optimistic batch updates. Required for batch update mode. 
= 3 
	Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records 
	only when you call the Update method. 
= 2 
	Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful 
	editing of the records, usually by locking records at the data source 
	immediately after editing. 
= 1 
	Indicates read-only records. You cannot alter the data. 
= -1 
	Does not specify a type of lock. For clones, the clone is created with the same lock type as the original. 

*/
recordset.Open(QueryString, ActiveConnectionString, CursorType, LockType, Options)