C# ASP example for Excel won't work for us

[George Ellis]

A couple of use have been trying to figure out why we are not getting
anywhere with this example.

http://support.microsoft.com/default.aspx?scid=kb;en-us;306572
============
using System.Data.OleDb;
using System.Data;
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
"Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);

// Open connection with the database.
objConn.Open();

// The code to follow uses a SQL SELECT command to display the data from the
worksheet.

// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1",
objConn);

// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;

// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();

// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");

// Bind data to DataGrid control.
DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();

// Clean up objects.
objConn.Close();
=====

We followed the directions, but when we Start it, the data in the XLS file
does not populate in the DataGrid. Office 2003 and VS2003 on my set. .Net
1.1 loaded (we have not migrated to 2.0 yet.) The ASPX is running on the
local webserver and runs without error.

Does anyone know something obvious that we have not figured into it?

 

[George Ellis]

Oh, and I fixed this

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
"\"Extended Properties=Excel 8.0;HDR=YES;IMEX=1\"";

 

[Guest]

Does your Excel worksheet have a named range? Because that's how the example
works.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

Oh, and I fixed this

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
"\"Extended Properties=Excel 8.0;HDR=YES;IMEX=1\"";

A couple of use have been trying to figure out why we are not getting
anywhere with this example.

http://support.microsoft.com/default.aspx?scid=kb;en-us;306572
============
using System.Data.OleDb;
using System.Data;
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
"Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);

// Open connection with the database.
objConn.Open();

// The code to follow uses a SQL SELECT command to display the data from
the worksheet.

// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1",
objConn);

// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;

// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();

// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");

// Bind data to DataGrid control.
DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();

// Clean up objects.
objConn.Close();
=====

We followed the directions, but when we Start it, the data in the XLS file
does not populate in the DataGrid. Office 2003 and VS2003 on my set.
.Net 1.1 loaded (we have not migrated to 2.0 yet.) The ASPX is running on
the local webserver and runs without error.

Does anyone know something obvious that we have not figured into it?

 

[George Ellis]

Yes, it does. The funny thing is that we have a vbscript on another machine
that can do it. The workaround is we may use the script to write a temp
table on the SQL server, redisplay the table and then submit it (the
original goal was to read a XLS and display the contents for a verification
before submitting a job on a server). I just hate doing a write to a table
before the verify of the read. That means creating cleanup logic for an
abort/cancel that we may not be able to catch. Sigh.

Does your Excel worksheet have a named range? Because that's how the
example
works.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

Oh, and I fixed this

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
"\"Extended Properties=Excel 8.0;HDR=YES;IMEX=1\"";

A couple of use have been trying to figure out why we are not getting
anywhere with this example.

http://support.microsoft.com/default.aspx?scid=kb;en-us;306572
============
using System.Data.OleDb;
using System.Data;
// Create connection string variable. Modify the "Data Source"
// parameter as appropriate for your environment.
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +
"Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);

// Open connection with the database.
objConn.Open();

// The code to follow uses a SQL SELECT command to display the data
from
the worksheet.

// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1",
objConn);

// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;

// Create new DataSet to hold information from the worksheet.
DataSet objDataset1 = new DataSet();

// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData");

// Bind data to DataGrid control.
DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
DataGrid1.DataBind();

// Clean up objects.
objConn.Close();
=====

We followed the directions, but when we Start it, the data in the XLS
file
does not populate in the DataGrid. Office 2003 and VS2003 on my set.
.Net 1.1 loaded (we have not migrated to 2.0 yet.) The ASPX is running
on
the local webserver and runs without error.

Does anyone know something obvious that we have not figured into it?