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......