Click here to Skip to main content
15,949,741 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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>
Posted
Updated 17-Jul-18 23:43pm

1 solution

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900