System.Data.OleDb.OleDbException: Sheet1 is not a valid name

Last Reply on Mar 01, 2013 09:57 PM By Mudassar

Posted on Mar 01, 2013 09:56 PM

Sir,

working on .vb with SQL 2008

getting an error while exporting an Excel to SQL. sir this code is working perfectly if export to access


Error:


System.Data.OleDb.OleDbException: 'Sheet1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at webadmin_utisystem.saveexcel2SQL() in D:\websites\nice(26-1-13)\webadmin\utisystem.aspx.vb:line 60



For costr.config:

                 Server=SUMEET-PC;Database=exporttoexceldb;Trusted_connection=Yes;

 

 

Here's the code below:

 

Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb
 

Partial Class webadmin_utisystem
    Inherits System.Web.UI.Page

    Function constr() As String
        Dim s1 As New IO.StreamReader(Server.MapPath("constr.config"))
        Dim c As String = s1.ReadToEnd
        s1.Close()
        Return c
    End Function

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
         
        saveexcel2SQL()
         
    End Sub

    Sub saveexcel2SQL()
        Try
            If FileUpload1.FileName <> Nothing Then
                Dim l As String = IO.Path.GetExtension(FileUpload1.FileName).ToLower
                If l = ".xls" Or l = ".xlsx" Then
                    Dim strFileName As String = DateTime.Now.ToString("ddMMyyyy_HHmmss") & l
                    Dim path As String = String.Concat(Server.MapPath("data/product/" & strFileName))
                    FileUpload1.SaveAs(path)
                    Dim excelConnectionString As String = ""

                    Dim strNewPath As String = Server.MapPath("data/product/" & strFileName)

                    ' Connection String to Excel Workbook
                    If l = ".xls" Then
                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strNewPath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
                    ElseIf l = ".xlsx" Then
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strNewPath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
                    End If
                    
                    Dim connection As New OleDbConnection()
                    connection.ConnectionString = excelConnectionString

                    Dim query As String = "SELECT * FROM [Sheet1$]"

                    Dim command As New OleDbCommand(query, connection)
                    'connection.Open()

                    'Open connection
                    If connection.State = ConnectionState.Closed Then connection.Open()
                    'Create the command object
                    Dim cmd As New OleDbCommand()
                    Dim da As New OleDbDataAdapter
                    Dim ds As DataSet
                    cmd = New OleDbCommand(query, connection)
                    da = New OleDbDataAdapter(cmd)
                    ds = New DataSet()
                    da.Fill(ds)
                    Dim dr As Integer = ds.Tables(0).Rows.Count
                     
                    connection.Close()
                    connection.Dispose()

                    '----For saving to database
                    Dim i As Integer
                    'Dim con1 As New OleDbConnection(con)
                    Dim con23 As New SqlConnection(constr)
                    For i = 0 To dr
                        If i < dr Then
                            Dim cmd1 As New SqlCommand("Insert into details(tagno,itemname,grwt,mrp)" & _
                            "values(@GemId,@GemName,@GemWeight,@Gemamount)", con23)
                            cmd1.Parameters.AddWithValue("@GemId", ds.Tables(0).Rows(i).Item(0).ToString)
                            cmd1.Parameters.AddWithValue("@GemName", ds.Tables(0).Rows(i).Item(1).ToString)
                            cmd1.Parameters.AddWithValue("@GemWeight", ds.Tables(0).Rows(i).Item(2).ToString)
                            cmd1.Parameters.AddWithValue("@Gemamount", ds.Tables(0).Rows(i).Item(3).ToString)
                            con23.Open()
                            cmd1.ExecuteNonQuery()
                            con23.Close()
                        End If
                    Next

                    Label1.Text = "Data saved successfully! Total Records:" & dr
                     
                Else
                    Label1.Text = "Invalid file format"
                End If
            End If
        Catch ex As Exception
            Label2.Text = ex.ToString
        End Try
    End Sub
 

 Any suggestions would be helpfull......

Posted on Mar 01, 2013 09:57 PM

This means that the Excel that you are connecting does not contain Sheet1

 

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html