Hi Friends,
FYI, I was successfully done to transfer data from excel file to SQL server by using below link. Its work!!! Thanks bro.
http://www.aspsnippets.com/Articles/Using-SqlBulkCopy-to-import-Excel-SpreadSheet-data-into-SQL-Server-in-ASPNet-using-C-and-VBNet.aspx
But, Now i want to insert "Date_Load" on my table once the transaction was completed.
Condition:
On Excel Column :
1.Id
2.Name
3.Salary
On Table tblPersons :
1.PersonId
2.Name
3.Salary
4.Date_Load
May I know, how to auto insert "Date_Load" on table tblPersons when user upload the data.
I attach together with code that I try modified, but got error.
error msg = "The given ColumnName '2015-10-01 16:49:45' does not match up with any column in data source."
Thanks..I hope u got my point.
Protected Sub Upload(sender As Object, e As EventArgs) Handles Button1.Click
'Upload and save the file
Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(excelPath)
Dim connString As String = String.Empty
Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Select Case extension
Case ".xls"
'Excel 97-03
connString = ConfigurationManager.ConnectionStrings("Excel03ConStringload").ConnectionString
Exit Select
Case ".xlsx"
'Excel 07 or higher
connString = ConfigurationManager.ConnectionStrings("Excel07+ConStringload").ConnectionString
Exit Select
End Select
connString = String.Format(connString, excelPath)
Using excel_con As New OleDbConnection(connString)
excel_con.Open()
Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
Dim dtExcelData As New DataTable()
'[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), _
New DataColumn("Name", GetType(String)), _
New DataColumn("Salary", GetType(Decimal))})
Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1) + "]", excel_con)
oda.Fill(dtExcelData)
End Using
excel_con.Close()
Dim conString As String = ConfigurationManager.ConnectionStrings("APPHRMS_CS").ConnectionString
Using con As New SqlConnection(conString)
Using sqlBulkCopy As New SqlBulkCopy(con)
'Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tblPersons"
'[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "PersonId")
sqlBulkCopy.ColumnMappings.Add("Name", "Name")
sqlBulkCopy.ColumnMappings.Add("Salary", "Salary")
sqlBulkCopy.ColumnMappings.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "Date_Load")
con.Open()
sqlBulkCopy.WriteToServer(dtExcelData)
con.Close()
End Using
End Using
End Using
End Sub