Try
Dim sSourceConstr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath)
Dim sDestConstr As String = ConfigurationManager.ConnectionStrings("dbProject").ConnectionString
Dim sSourceConnection As New OleDbConnection(sSourceConstr)
Using sSourceConnection
Dim sql As String = String.Format("Select [ProofNo],[PartName],[Customer],[ProductID],[Thickness],[Width],[Length],[Length1],[TypeFilm],[MD],[TD],[FilmWidth],[CylinderNo],[CylinderJointNo],[DirectPrinting],[Printingcolor],[Mold],[FinishGood],[RollLength],[CoreZise],[Seaming],[TapeColor],[DirectPacking],[Other],[Responsible],[PrepareBy],[Remark] FROM [{0}]", "Sheet1$")
Dim command As New OleDbCommand(sql, sSourceConnection)
sSourceConnection.Open()
Using dr As OleDbDataReader = command.ExecuteReader()
Using bulkCopy As New SqlBulkCopy(sDestConstr)
bulkCopy.DestinationTableName = "ProductSpecification"
'You can mannualy set the column mapping by the following way.
'bulkCopy.ColumnMappings.Add("Employee ID", "Employee Code");
bulkCopy.WriteToServer(dr)
End Using
End Using
End Using
Catch ex As Exception
*******code upload excel ครับ
If FileUpload1.HasFile Then
Dim sPath As String = Server.MapPath("~/MyXls/" + FileUpload1.FileName)
FileUpload1.SaveAs(sPath)
ImporttoSQL(sPath)
End If
Server.Transfer("FrmSpecProduct.aspx")
Code (VB.NET)
********code insert excel ครับเป็นแบบ select copy นะครับ
Private Sub ImporttoSQL(sPath As String)
Try
Dim sSourceConstr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath)
Dim sDestConstr As String = ConfigurationManager.ConnectionStrings("DBProject").ConnectionString
Dim sSourceConnection As New OleDbConnection(sSourceConstr)
Using sSourceConnection
Dim sql As String = String.Format("Select [ProofNo],[PartName],[Customer],[ProductID],[Thickness],[Width],[Length],[Length1],[TypeFilm],[MD],[TD],[FilmWidth],[CylinderNo],[CylinderJointNo],[DirectPrinting],[Printingcolor],[Mold],[FinishGood],[RollLength],[CoreZise],[Seaming],[TapeColor],[DirectPacking],[Other],[Responsible],[PrepareBy],[Remark] FROM [{0}]", "Sheet1$")
Dim command As New OleDbCommand(sql, sSourceConnection)
sSourceConnection.Open()
Using dr As OleDbDataReader = command.ExecuteReader()
Using bulkCopy As New SqlBulkCopy(sDestConstr)
bulkCopy.DestinationTableName = "ProductSpecification"
bulkCopy.WriteToServer(dr)
End Using
End Using
End Using
Catch ex As Exception
End Try
End Sub
dim dt as datatable
dim cmd as string
dt=ข้อมูลของ excel
for each dr as datarow in dt.rows
cmd="insert into ProductSpecification(Col1,Col2,Col3) Values("
cmd+=.....
cmd+=.....
Conn.ExecuteNonQuery(cmd)
next
Dim oleExcel As New OleDbConnection
Dim oleExcelCmd As New OleDbCommand
Dim oleExcelWrt As New OleDbDataAdapter
Dim _Header As String = ""
Dim ExPath As String
dim TableName as string
_Header = "YES"
ExPath ="C:\1.xlsx"
TableName ="sheet1$"
oleExcel = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExPath & ";Extended Properties=""Excel 12.0 Xml;HDR=" & _Header & ";IMEX=1"";")
oleExcel.Open()
oleExcelWrt = New OleDbDataAdapter
oleExcelCmd.CommandText = "select * from [" & TableName & "]"
oleExcelCmd.Connection = oleExcel
oleExcelWrt.SelectCommand = oleExcelCmd
dt = New DataTable
oleExcelWrt.Fill(dt)
oleExcel.Close()
for each dr as datarow in dt.rows
cmd="insert into Table1(Col1,Col2,Col3) Values('"
cmd+= dr(0) + "','"
cmd+= dr(1) + "','"
cmd+= dr(2) + "' ) "
'Conn = ตัวต่อกับ MS SQL
Conn.ExecuteNonQuery(cmd)
next
Load balance : Server 01
