excel导入到sqlserver错误

Question

我想把excel导入到sqlserver中,在sqlserver中运行
select * into asd FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\1234\13022.xls";User 
ID=;Password=;Extended properties=Excel 5.0')...biao3$
可以成功的将D:\1234\13022.xls下的sheet (biao3$) 导入到表asd中但是我在C#程序中通过
SqlConnection sqlcon = new SqlConnection("Server=agrs-wangyunfei;database=identity;uid=sa;pwd=11111111");
sqlcon.Open();
string aaa = "select * into asd FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=\"D:\\1234\\13022.xls\";User ID=;Password=;Extended properties=Excel 5.0')...biao3$";
 
SqlCommand cmd = new SqlCommand(aaa, sqlcon);
cmd.ExecuteNonQuery();
运行到cmd.ExecuteNonQuery();
就会出错,错误信息如下
未处理 System.Data.SqlClient.SqlException
  Message="链接服务器 \"(null)\" 的 OLE DB 访问接口 \"Microsoft.Jet.OLEDB.4.0\" 报错。提供程序未给出有关错误的任何信息。\r\n无法初始化链接服务器 \"(null)\" 的 OLE DB 访问接口 \"Microsoft.Jet.OLEDB.4.0\" 的数据源对象。"
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=16
  LineNumber=1
  Number=7399
  Procedure=""
  Server="agrs-wangyunfei"
  State=1
  StackTrace:
       在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       在 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       在 shujudaoru.Form1.createtable() 位置 C:\Documents and Settings\Administrator\桌面\c#\shujudaoru\shujudaoru\Form1.cs:行号 95
       在 shujudaoru.Form1.button1_Click(Object sender, EventArgs e) 位置 C:\Documents and Settings\Administrator\桌面\c#\shujudaoru\shujudaoru\Form1.cs:行号 28
       在 System.Windows.Forms.Control.OnClick(EventArgs e)
       在 System.Windows.Forms.Button.OnClick(EventArgs e)
       在 System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       在 System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       在 System.Windows.Forms.Control.WndProc(Message& m)
       在 System.Windows.Forms.ButtonBase.WndProc(Message& m)
       在 System.Windows.Forms.Button.WndProc(Message& m)
       在 System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       在 System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       在 System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       在 System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       在 System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       在 System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       在 System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       在 System.Windows.Forms.Application.Run(Form mainForm)
       在 shujudaoru.Program.Main() 位置 C:\Documents and Settings\Administrator\桌面\c#\shujudaoru\shujudaoru\Program.cs:行号 17
       在 System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       在 System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       在 Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       在 System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       在 System.Threading.ThreadHelper.ThreadStart()
各位大侠帮忙看一下,谢谢了

Answer 1

OledbConnectionoledb 
string sql="select * from [sheetname]"

Answer 2

你的建议给了我一点想法
我把sqlconnection改为了OledbConnection 
就是全部通过OledbConnection链接excel和sql server,以前是通过sqlconnection链接sql server,OledbConnection链接access,这样导入数据就会产生以上的冲突,我把代码改了一下:
  OleDbConnection con = new OleDbConnection("Provider=SQLOLEDB;data source=agrs-wangyunfei;initial catalog=identity;user id=sa;pwd=11111111");
            con.Open();
            string aaa = "select * into asd FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=\"D:\\1234\\13022.xls\";User ID=;Password=;Extended properties=Excel 5.0')...biao3$"; 
            OleDbCommand cmd=new OleDbCommand (aaa,con );
            cmd.ExecuteNonQuery ();
            MessageBox .Show ("ok");
这样就可以把D:\1234\13022.xls中的sheet biao3$转到sql server中的asd表中,结贴,第一次发帖,哈哈