Paste the data from the downloaded Excel document into the database using ASP.net (Vb.net)

Question

I am working on a web application, I want to know if it is possible to insert data field information into an Excel file in my SqlServer database after loading it using Asp.net (VB.net) ??

Thanks x

Answer 1

Try this code in VB.NET:

Protected Sub btnExport(ByVal sender As Object, ByVal e As System.EventArgs) Dim TheFile As FileInfo = New FileInfo(MapPath(".") & "\" & "filename.xls") ' Connection String to Excel Workbook 2010 (xlsx) ' Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~\directory\filename.xlsx") + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;""" ' Connection String to Excel Workbook 2003 (xls) Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~\directory\filename.xls") + ";Extended Properties=""Excel 8.0;HDR=YES;""" ' Create Connection to Excel Workbook Using connection As New OleDbConnection(excelConnectionString) Dim command As New OleDbCommand("Select * FROM [sheetname$] ", connection) connection.Open() ' Create DbDataReader to Data Worksheet Using dr As DbDataReader = command.ExecuteReader() ' SQL Server Connection String Const sqlConnectionString As String = "Data Source=server; Initial Catalog=database; Persist Security Info=True;User ID=userid;Password=password" ' Bulk Copy to SQL Server Using bulkCopy As New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName = "SqlServerTableName" bulkCopy.WriteToServer(dr) End Using End Using End Using End Sub 

Extra tip: try installing IIS to run 32-bit applications.

Answer 2

Yes it is possible. Here's a bird's eye view of the possible main steps:

• After loading - save the Excel file under a temporary unique name
• Open OLEDB connection with the downloaded file
• Read it (either through OleDbDataReader - if it's a huge file, so you don't need to load it into memory right away or into the DataTable if it's small)
• Run your queries, Stored procedures to update SqlServer DB with the received data
• Delete uploaded file

Answer 3

try it

 public void export(string excelfilepath) { string ssqltable = "tdatamigrationtable"; string myexceldataquery = "select student,rollno,course from [sheet1$]"; try { //create our connection strings string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + ";extended properties=" + "\"excel 8.0;hdr=yes;\""; string ssqlconnectionstring = "server=mydatabaseservername;user id=dbuserid;password=dbuserpassword;database=databasename;connection reset=false"; //series of commands to bulk copy data from the excel file into our sql table oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring); oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn); oledbconn.open(); oledbdatareader dr = oledbcmd.executereader(); sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring); bulkcopy.destinationtablename = ssqltable; while (dr.read()) { bulkcopy.writetoserver(dr); } oledbconn.close(); } catch (exception ex) { //handle exception } } 

Answer 4

 Imports System.Data.Common Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim fname As String Using ofd As New OpenFileDialog If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then fname = ofd.FileName End If End Using Dim olecon As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=" & fname & ";Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;""" Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(olecon) dBaseConnection.Open() SSQL = "select [LOT],[IMAGE],[STYLENO],[VENDOR] from [Sheet1$]" Dim cmd As New OleDbCommand(SSQL, dBaseConnection) Dim da As New OleDbDataAdapter(cmd) Dim ds As New DataSet da.Fill(ds) Using dr As DbDataReader = cmd.ExecuteReader If SHCONNECTION.State = ConnectionState.Closed Then Call SHconn(MCONNECTIONSTRING) End If Using bulkCopy As New SqlBulkCopy(MCONNECTIONSTRING) bulkCopy.DestinationTableName = "DBimage" bulkCopy.WriteToServer(ds) End Using End Using End Sub End Class