Click here to Skip to main content
12,349,752 members (27,833 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET SQL Server Data
Hi

I am designing a web application in ASP.NET using VB and would like to import data from Excel (.xls) to SQL Server 2008 using OpenDataSource function. I have designed a form, from where the user clicks the Browse button and locates the Excel file to be imported.

The code I have written is pasted below for your reference. The code works fine if the DATA SOURCE is hard coded and imports the data from Excel to SQL Server. But when I specify a variable name in DATA SOURCE it gave me the following error:

Error: The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Data1$". The table either does not exist or the current user does not have permissions on that table.

Since the user will be browsing for the Excel file which can be located anywhere on the machine, I want the DATA SOURCE value to be a variable. Any help regarding this will be greatly appreciated.

------------------------------------------------------------------------------------

NOTE:

fileimport is a FILEUPLOAD Control

filepath is a variable where the absolute path of the excel file to be imported will be stored

EXCEL FILE IMPORT CODE:
Protected Sub txtimport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles txtimport.Click
 
If fileimport.HasFile Then
Dim fileextension As String
Dim filepath As String
fileextension = System.IO.Path.GetExtension(fileimport.FileName)
filepath = Server.MapPath(fileimport.FileName)
 
If (fileextension = ".xls") Or (fileextension = ".XLS") Then
 
    Try
        Dim con As SqlConnection
        Dim cmd As SqlCommand
        Dim dtr As SqlDataReader
        Dim importquery As String
 
        con = New SqlConnection("Server=localhost;Integrated Security = true;Database=RTS")
        con.Open()
 
        importquery = "SELECT * INTO ABC FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=" & filepath & ";" & _
        "Extended Properties=Excel 8.0')...[Data1$]"
 
        cmd = New SqlCommand(importquery, con)
        dtr = cmd.ExecuteReader
 
        con.Close()
        dtr.Close()
 
    Catch ex As Exception
 
        lblmsg.Text = "Error: " & ex.Message.ToString()
 
    End Try
Else
    lblmsg.Text = "Wrong File Extension"
End If
 
Else
 
lblmsg.Text = "You have not selected a file"
 

End If
 
End Sub
------------------------------------------------------------------------------------

Kind Regards

Muffazal Tikiwala
Posted 14-Aug-12 18:25pm
Edited 27-Sep-12 9:39am
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 27 Sep 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100