James Mace

James Mace

  • NA
  • 1
  • 0

Saving changes to a spreadsheet with ADO.net

Jan 9 2006 2:12 PM
I am using ASP.net, ADO.net, and Microsoft Excel as my data source. I am able to retrieve data from my spreadsheet, as well as, update certain fields, BUT the spreadsheet is never saved. Therefore, my calculations do not reflect the new values. After running the page I can open the spreadsheet in Microsoft Excel and see that the changes have been made. When I go to close the spreadsheet it asks if I want to save it; I choose yes. When I go to refresh my aspx page the correct data is displayed based on the updated values.

From what I can gather, ADO or ASP.net is not saving the Excel spreadsheet. Does anyone have any ideas on how I can remedy this? I have to use ADO, because my Web host will not install Microsoft Office on a shared server... :-( My code is as follows:

public void UpdateVelocity (String velocity) {

// Create connection string variable. Modify the "Data Source"

// parameter as appropriate for your environment.

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=c:\\WebSites\\MACE\\db\\ballistics.xls;" +

"Extended Properties=\'Excel 8.0;HDR=Yes;IMEX=2\'";

// Create connection object by using the preceding connection string.

OleDbConnection objConn = new OleDbConnection(sConnectionString);

// Open connection with the database.

objConn.Open();

//Create update command and execute

OleDbCommand objCmdUpdate =new OleDbCommand("UPDATE Info SET INFO = " +velocity+ " WHERE INDEX = 'Vol'", objConn);

objCmdUpdate.ExecuteNonQuery();

// Clean up objects.

objConn.Close();

}