Worksheet::PutRecordset

PutRecordset

1 Description
2 Syntax
3 Parameters
4 Return
5 Examples
6 Remark
7 See Also
8 header to Include

Description

Put data from a recordset into an Origin worksheet

Syntax

BOOL PutRecordset( Object & objrs, int nRowBegin = 0, int nNumRows = -1, int nColBegin = 0, int nOptions = LAYWKGETRECORDSET_BY_COLUMN_INDEX, int * pnErrorCode = NULL, POSTREADRS postReadRS = NULL )

BOOL PutRecordset( _VARIANT & varobjrs, int nRowBegin = 0, int nNumRows = -1, int nColBegin = 0, int nOptions = LAYWKGETRECORDSET_BY_COLUMN_INDEX, int * pnErrorCode = NULL, POSTREADRS postReadRS = NULL )

Parameters

objrs: [input] The recordset.
nRowBegin: [input] The starting row in the worksheet. default is 0.
nNumRows: [input] Total number of records to retrieve,or < 0 to retrieve all available.; default is -1.
nColBegin: [input] Applies only to the options LAYWKGETRECORDSET_BY_COLUMN_INDEX with or without LAYWKGETRECORDSET_BY_COLUMN_INDEX and LAYWKGETRECORDSET_SET_COLUMN_NAME bits; the column in worksheet from which to begin. default is 0.
nOptions: [input] Options from LAYWKGETRECORDSET_BY_COLUMN_INDEX , etc (OC_const.h). // beginning with the starting column nColBegin retrieve all fields (will add columns as necessary); default is LAYWKGETRECORDSET_BY_COLUMN_INDEX
pnErrorCode: [output,optional] if supplied, *pnErrorCode receives additional error info as values from PUTRSET_* enumeration

varobjrs: [input] The recordset.
nRowBegin: [input] The starting row in the worksheet. default is 0.
nNumRows: [input] Total number of records to retrieve,or -1 to retrieve all available. default is -1.
nColBegin: [input] Applies only to the options LAYWKGETRECORDSET_BY_COLUMN_INDEX and LAYWKGETRECORDSET_BY_COLUMN_INDEX_WITH_COLUMN_RENAMING; the column in worksheet from which to begin. default is 0.
nOptions: [input] Options from LAYWKGETRECORDSET_BY_COLUMN_INDEX, etc (OC_const.h).; default is LAYWKGETRECORDSET_BY_COLUMN_INDEX.
pnErrorCode: [output,optional] if supplied, *pnErrorCode receives additional error info as values from PUTRSET_* enumeration
postReadRS: [input,optional] (8.1SR2) if supplied, postReadRS->nCodePage holds the code page to be used when converting string values from database. If postReadRS->nCodePage > 0, the code page value postReadRS->nCodePage is used. If postReadRS->nCodePage < 0, the current code page used in Origin for various string manipulations (which might be different from OS code page) is used. If postReadRS->nCodePage == 0 (same as postReadRS == NULL), then the current OS code page is used.

Return

TRUE for success; otherwise FALSE.

Examples

EX1

// SQL Server access to data
// Must have server and edit connection string to suit your system
int Worksheet_PutRecordset_Ex1()
{
    Object ocrs;
    ocrs = CreateObject("ADODB.Recordset");
    try
    {
        ocrs.open("select * from customers", "Provider=SQLOLEDB; Data Source=myServer; Initial Catalog=northwind; User ID=myID; Password=myPassword;");
    }
    catch(int nErr)
    {
        printf("Failed to open");
        return 0;
    }
    Worksheet wks = Project.ActiveLayer();
    // Put the recordset into worksheet:
    BOOL bRet;
    if(wks)
    {
        bRet = wks.PutRecordset(ocrs, 0, -1, 0, LAYWKGETRECORDSET_BY_COLUMN_INDEX | LAYWKGETRECORDSET_SET_COLUMN_NAME);
    }
    return (int)bRet;
}

EX2

// Retrieve data from Office Sample database and put into a worksheet
// Must have Jet Engine and database installed - edit path as needed
void Worksheet_PutRecordset_Ex2()
{
    Object ocrs;
    ocrs = CreateObject("ADODB.Recordset");
    if( !ocrs )
        return;
    string strConn;
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb";
    string strQuery = "select * from customers";
    ocrs.CursorLocation = 3; //set as adUseClient, which will make recordset reading faster.
    try
    {
        ocrs.open( strQuery, strConn, 1, 3);
    }
    catch(int nErr)
    {
        printf("Failed to open");
        return;
    }
    Worksheet wks(Project.ActiveLayer());
    if( wks.IsValid() )
        if( wks.PutRecordset(ocrs) )
            printf("Success to put dataset into worksheet!");
        else
            printf("Fail to put dataset into worksheet!");
    else
        printf("Workbook not active\n");
}

EX3

void Worksheet_PutRecordset_Ex3()
{
    Object ocrs;
    ocrs = CreateObject("ADODB.Recordset");
    try
    {
        ocrs.open("select * from customers", "Provider=SQLOLEDB; Data Source=myServer; Initial Catalog=northwind; User ID=myID; Password=myPassword;");
    }
    catch(int nErr)
    {
        printf("Failed to open");
        return;
    }
    Worksheet wks(Project.WorksheetPages(0).GetName());
    // Put the recordset into worksheet:
    BOOL bRet = wks.PutRecordset(ocrs, 0, -1, 0, LAYWKGETRECORDSET_BY_COLUMN_INDEX | LAYWKGETRECORDSET_SET_COLUMN_LABEL);
    out_int("bRet = ", bRet);
}

Remark

It is recommended to use the ReadRecordset method instead as it returns the error code, while this older method returns BOOL, even though the error code can also be obtained in another argument.

Notes: The performance depends on how the recordset is opened, as controlled by CursorLocation (adUseClient, or adUseServer, see in query_utils.h). For reading into worksheet, usually set CursorLocation as adUseCient is enough, which will cause CursorType be set as adOpenStatic, which means that any other application's update into source database will not affect this recordset once it is open. It is like the recordset is open as read-only. See CursorType, CursorLocation for more details.