Advanced POST Example: Updating Tabular Data

The previous two samples are easy solutions if you know the absolute positions of the cells that are going to be edited. We had two cells, and we knew the cells' addresses, so we could reference them in the code with certainty. This sample is slightly different because it allows you to edit tabular data from any combination of cells. For example, if your data set has 100 rows and 8 columns, that's 800 possible cells that can be edited.

This technique requires a bit more configuration of the template workbook and the server-side scripts than the first two samples.

The sample works as follows:

1. Generate.asp creates a worksheet with tabular data using the ExcelTemplate object.
2. Each time a cell is changed, the cell's address, new value, corresponding table primary key value, and table name are submitted to the server in a specially formatted request.
3. On the server-side, these special requests are parsed and the database is updated.
4. To properly configure this sample for use with your own data, do the following:
   1. Use the included template.xls as a starting point since it includes the necessary VBA code to process the cell changes
   2. Open template.xls and go to Tools > Macros > Visual Basic editor. In the Initialize module, configure the URL to which the data should be posted, the primary key column (1-based) from the worksheet, and the table name from where the data came.
   3. In response.asp, the server-side response script, edit the oColDict array so that the script can be aware of the database column names that correpond to each Excel worksheet column.

Run the Sample

Important Things to Note About This Sample

1. Response.asp
   
   
   • The server-side script needs to be made aware of the names of the database columns for each Excel worksheet column. You need to initialize the oColDict array with these values. For example, if the sixth column of the worksheet contains data from the Customer.FirstName column, set the oColDict element as follows:

     oColDict(6) = "Customer.FirstName"
     	

     Do this for all columns in your tabular worksheet.
   
   
2. Template.xls
   
   
   • In the Initialize module of the template.xls workbook, it's important to set the URL, Primary Key column, and the table name. For this technique to work properly, the primary key value must be located somewhere in the Excel worksheet (note, the column can be hidden if you don't want to display it to the client):

     oXLWUpdater.UpdateURL = strURL
     oXLWUpdater.PKeyColumn = intPkeyColumn
     oXLWupdater.TableName = strTableName
     	

Top

Copyright © 2003, SoftArtisans, Inc.