我的代码无法从excel到ms访问表导入数据

问题描述:

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>


Else
sql =SELECT * FROM [& ddList.SelectedValue.ToString&
" 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)。行
sql =insert into& ddList.SelectedValue.ToString& VALUES(
sql1 =
For i = 0 to ds.Tables(tbl1)。Columns.Count - 1
如果是InStr(dr(i).ToString, /)> 0然后
sql1 = sql1 +'+ dr(i).ToString()+',
否则
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()

下一个
lbMessage.Text =表名:& ddList.SelectedValue.ToString& ,有:& ds.Tables(tbl1)。Rows.Count.ToString& Record(s)

Catch ex As Exception
lbMessage.Text =错误:上传失败!!& CStr(iRow)& ,请检查值不允许空/超出列
最后
cnnEXCEL.Close()

结束尝试


结束次
结束等级

我尝试过:

< big>< big>< / big>我的代码已包含不是错误,但无法导入。< / big>< big>< / big>
" 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>


我们无权访问您的Excel文件你的数据库。因此,通过在不知道这些文件的组织(表格和列)的情况下查看代码来找到可能的问题,我们需要做更多的工作。



但是你有检查发生了什么的工具:调试器。



用它来检查每行代码发生了什么。或者打印出中间值。



你应该做的第一件事就是检查 ExecuteNonQuery()的返回值>调用以了解受影响的行数。检查生成的SQL命令可能也很有帮助。两者都应该有助于在SQL语句中找到逻辑错误。
We have no access to your Excel file and your database. So it is much more work for us to find possible problems by just seeing your code without knowing about the organisation (tables and columns) of those files.

But you have the tool to check what is going on: the debugger.

Use it to check what is happening at each line of code. Alternatively print out intermediate values.

The first thing you should do is checking the return values of the ExecuteNonQuery() calls to know how many rows are affected. It might be also helpful to inspect the generated SQL commands. Both should help finding logic errors in the SQL statements.