門外漢的筆記

當以Excel當資料庫使用SqlClient類別連結時，透過SqlConnection連結出現錯誤訊息"System.ArgumentException: '不支援關鍵字: 'provider'。' "

解決的方式是改使用OLEDB類別

使用SqlClient類別會出現錯誤

using System.Data;
using System.Data.SqlClient;

DataTable tableSql = new DataTable();
 string connectionString= @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\menber.xlsx;" + "Extended Properties=\"EXCEL 12.0;HDR=YES\""; ;
string strSQL = " Select * From [sheet1$] ";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

使用OLEDB類別可正常連結

using System.Data;
using System.Data.OleDb;

DataTable tableSQL = new DataTable();
String connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\menber.xlsx;" + "Extended Properties=\"EXCEL 12.0;HDR=YES\""; 
OleDbConnection connection = new OleDbConnection(connectionString);
 String strSQL = " Select * From [sheet1$] ";
using (OleDbDataAdapter dr = new OleDbDataAdapter(strSQL, connection))
{
     connection.Open();
     dr.Fill(tableSQL);  //將所有資料填充至tableSql
}
connection.Close();
GridView1.DataSource = tableSQL;
GridView1.DataBind();
Session["tempDT"] = tableSQL; //儲存 

(EXCEL版本:2007)