我的代码无法从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 theExecuteNonQuery()
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.