Upload Excel in to SQL databse -VB.NET
Question
Answers
All replies
-
User283571144 posted
Hi shsu,
I have an excel sheet with 5 columns which are 5 fields in the database registration table. when i upoad that excel using File upload, how can i insert in to the registration table all the user details in the excel sheet .Appreciate your helpAccording to your description, I suggest you could firstly store the excel into your server, then you could read the data from the excel using OleDb library and insert it into database.
More details, you could refer to below codes:
My test table:
CREATE TABLE [dbo].[Employee]( [id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL, [City] [varchar](50) NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]Excel:
Aspx:
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="ExcelTest.aspx.vb" Inherits="VBidentityTrue.ExcelTest" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button ID="Button1" runat="server" Text="Upload" OnClick="Button1_Click"/> </div> </form> </body> </html>Code-behind:
Notice: Since I use Microsoft.Jet.OLEDB.4.0, it could only read the xls excel. If you want to use xlxs, you should use oledb.12.0.
Imports System.Data.OleDb Imports System.Data.SqlClient Imports System.IO Public Class ExcelTest Inherits System.Web.UI.Page Private Econ As OleDbConnection Private con As SqlConnection Private constr, Query, sqlconn As String Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load End Sub Private Sub ExcelConn(ByVal FilePath As String) constr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;';" Econ = New OleDbConnection(constr) End Sub Protected Sub Button1_Click(sender As Object, e As EventArgs) FileUpload1.SaveAs(Server.MapPath("excel") + FileUpload1.PostedFile.FileName) InsertExcelRecords(Server.MapPath("excel") + FileUpload1.PostedFile.FileName) End Sub Private Sub InsertExcelRecords(ByVal FilePath As String) ExcelConn(FilePath) Query = String.Format("select * from [{0}$]", "Sheet1") Dim Ecom As OleDbCommand = New OleDbCommand(Query, Econ) Econ.Open() Dim ds As DataSet = New DataSet() Dim oda As OleDbDataAdapter = New OleDbDataAdapter(Query, Econ) Econ.Close() oda.Fill(ds) Dim Exceldt As DataTable = ds.Tables(0) connection() Dim objbulk As SqlBulkCopy = New SqlBulkCopy(con) objbulk.DestinationTableName = "Employee" objbulk.ColumnMappings.Add("Name", "Name") objbulk.ColumnMappings.Add("City", "City") con.Open() objbulk.WriteToServer(Exceldt) con.Close() End Sub Private Sub connection() sqlconn = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-VBidentityTrue-201805070401328;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" con = New SqlConnection(sqlconn) End Sub End ClassResult:
Best Regards,
Brando
-
User283571144 posted
Hi shsu,
I am using vs 2017 . The given path's format is not supported. error message is showing. for(FileUpload1.SaveAs(Server.MapPath("excel") + FileUpload1.PostedFile.FileName) )
Could you please tell me how you solved the path not found error?
According to the OleDbException , it means the excel is read-only or you don't have the permission to access it.
I suggest you could firstly open the excel's path and make sure you could update and delete it.
Like below:
Best Regards,
Brando
