EXCEL直导入提起超时已到期，数据少就可以了， 数据一多就不行了， 怎么改比较好呀

[TERRYYUAN_2017]

Dim fileName As String
fileName = EXCELFILENAME.Text
Dim sConnectionString As String = "Provider=Microsoft.Ace.OleDb.12.0;Data Source='" & fileName & "';Extended Properties=Excel 12.0;"
''Dim sConnectionString As String = "Provider=Microsoft.Ace.OleDb.16.0;Data Source='" & fileName & "';Extended Properties=Excel 16.0;"
''Dim sConnectionString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
Dim oleDbConnection As OleDbConnection = New OleDbConnection(sConnectionString)
oleDbConnection.Open()
'获取excel表
Dim dataTable As DataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim tableName As String = dataTable.Rows(0)(2).ToString().Trim()
tableName = "[" & tableName.Replace(" ' ", " ") & "]"


' ''建立EXCEL连接，读入数据
'Dim strConn As String = "Provider=Microsoft.Ace.OleDb.12.0;Data Source='" & fileName & "';Extended Properties=Excel 12.0;"
''
'利用SQL语句从Excel文件里获取数据
Dim query As String = "SELECT 请购单号,业务类型,日期,请购部门,请购人,款式编码,款名,颜色,尺码,数量,金额,需求日期,建议订货日期,供应商编号,制单人, 仓库编码 FROM " & tableName
Dim dataset As DataSet = New DataSet()
Dim oleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)
oleAdapter.Fill(dataset, "TY_INPUVOUCH")

'SQL数据库连接
Dim sqlcon As SqlClient.SqlConnection = New SqlClient.SqlConnection(IMcn)
sqlcon.Open()
''先清除里面的数据
Dim CLEARDATA As DataTable = New DataTable()
Dim sqlDACLEAR As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("truncate table TY_INPUVOUCH ", sqlcon)
Dim sqlCBCLEAR As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDACLEAR)
sqlDACLEAR.Fill(CLEARDATA)


'从excel文件获得数据后，插入记录到SQL Server的数据表
Dim dataTable1 As DataTable = New DataTable()
Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("SELECT 请购单号,业务类型,日期,请购部门,请购人 ,款式编码,款名,颜色,尺码,数量,金额,需求日期,建议订货日期,供应商编号,制单人, 仓库编码 from TY_INPUVOUCH WITH(TABLOCKX) ", sqlcon)

Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)

sqlDA1.Fill(dataTable1)
Dim dataRow11 As DataRow

For Each dataRow11 In dataset.Tables("TY_INPUVOUCH").Rows


'sql里数据dataRow1

Dim dataRow1 As DataRow = dataTable1.NewRow()
dataRow1("请购单号") = dataRow11("请购单号")
dataRow1("业务类型") = dataRow11("业务类型")
dataRow1("日期") = dataRow11("日期")
dataRow1("请购部门") = dataRow11("请购部门")
dataRow1("请购人") = dataRow11("请购人")
dataRow1("款式编码") = dataRow11("款式编码")
dataRow1("款名") = dataRow11("款名")
dataRow1("颜色") = dataRow11("颜色")
dataRow1("尺码") = dataRow11("尺码")
dataRow1("数量") = dataRow11("数量")
dataRow1("金额") = dataRow11("金额")
dataRow1("需求日期") = dataRow11("需求日期")
dataRow1("建议订货日期") = dataRow11("建议订货日期")
dataRow1("供应商编号") = dataRow11("供应商编号")
dataRow1("制单人") = dataRow11("制单人")
dataRow1("仓库编码") = dataRow11("仓库编码")
dataTable1.Rows.Add(dataRow1)

Next

Try


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

sqlDA1.Update(dataTable1) '' 导入EXCEL数据

[脆皮大雪糕]

sql server的数据导入导出工具了解一下。 不知道你的数据到底有多少，我这成天折腾七八百万的数据没有问题。

[赵4老师]

oleDbConnection.Open()
'改为
oleDbConnection.CommandTimeout=600 '10分钟
oleDbConnection.Open()