Мой код не может быть импортирован из excel в таблицы ms access
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Public Class Defult
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack() Then
Dim cnn As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("~/App_data/Data_List.accdb"))
Dim cmd As New OleDbCommand
cnn.Open()
cmd.Connection = cnn
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = " select '[Select Table Name]' as TABLE_NAME from dual union all select TABLE_NAME from tabs where table_name like 'FACT%' or table_name='Employee'"
Try
Dim dr As OleDbDataReader = cmd.ExecuteReader
ddList.DataSource = dr
ddList.DataValueField = "TABLE_NAME"
ddList.DataTextField = "TABLE_NAME"
ddList.DataBind()
dr.Close()
cmd.Dispose()
cnn.Close()
Catch ex As Exception
Response.Write(ex.Message)
End Try
End If
End Sub
Protected Sub btnCopy_Click(ByVal sender As Object, e As EventArgs) Handles btnCopy.Click
Dim xlpath As String
Dim usr As String = HttpContext.Current.User.Identity.Name
xlpath = Server.MapPath("~/ExcelFile/") & EXCEL_PATH.FileName
Try
Kill(xlpath)
Catch
End Try
EXCEL_PATH.PostedFile.SaveAs(xlpath)
Dim strConn As String = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " & xlpath & " ; Extended Properties = Excel 8.0 ;"
Dim cnnEXCEL As OleDbConnection = New OleDbConnection(strConn)
Dim cnn As New OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("~/App_data/Data_List.accdb"))
Dim cmd As New OleDbCommand
Dim sql As String
Dim i As Integer
Dim sql1 As String = ""
Dim iRow As Integer
iRow = 1
Try
cnnEXCEL.Open()
If EXCEL_PATH.FileName = "Employee.xlsx" Then
sql = "SELECT * FROM[Sheet1$]"
Else
sql = "SELECT * FROM [" & ddList.SelectedValue.ToString & "$]"
End If
Dim oleda As OleDbDataAdapter = New OleDbDataAdapter(sql, cnnEXCEL)
Dim ds As DataSet = New DataSet()
oleda.Fill(ds, "Data_list")
cnn.Open()
cmd.Connection = cnn
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = " truncate table " & ddList.SelectedValue.ToString
cmd.ExecuteNonQuery()
For Each dr As DataRow In ds.Tables("Data_List").Rows
sql = "insert into " & ddList.SelectedValue.ToString & "VALUES ("
sql1 = ""
For i = 0 To ds.Tables("tbl1").Columns.Count - 1
If InStr(dr(i).ToString, "/") > 0 Then
sql1 = sql1 + "'" + dr(i).ToString() + "',"
Else
sql1 = sql1 + "'" + dr(i).ToString() + "',"
End If
iRow += 1
Next
sql &= sql1.Substring(0, sql1.Length - 1) + ")"
cmd.Connection = cnn
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = sql
cmd.ExecuteNonQuery()
Next
lbMessage.Text = "Table Name: " & ddList.SelectedValue.ToString & ", Has: " & ds.Tables("tbl1").Rows.Count.ToString & " Record(s)"
Catch ex As Exception
lbMessage.Text = "Error: Upload Fail!! " & CStr(iRow) & ", Please check values not allows null/exceed columns"
Finally
cnnEXCEL.Close()
End Try
End Sub
End Class
What I have tried:
<big><big></big>My code has not an error but can not be imported.</big><big></big>