使用VB.NET导入Excel数据到数据库时ADODB相关报错如何解决

你当前报错的核心原因是代码中读取Excel数据到DataTable的逻辑写反，加上多处语法、逻辑错误，导致后续操作空的/格式错误的DataTable时触发类型不匹配报错。

需修改的错误点 #

• 第一处：Excel数据赋值逻辑错误
  你原来的代码执行dta.Fill(dts, "[Sheet1$]")后，已经把Excel的Sheet1数据写入了DataSet的[Sheet1$]表中，但你后续执行了dts.Tables.Add(table)，这是把提前new的空DataTable添加到DataSet里，完全没有拿到Excel的数据。需要把这行替换为table = dts.Tables("[Sheet1$]")，直接读取DataSet里存储的Excel数据。
• 第二处：循环遍历逻辑错误

1. DataTable的行索引默认从0开始，不需要跳过第0行也不需要减2，除非Excel首行是无效表头数据才需要调整起始索引
2. DataRow没有Cells属性，Cells是DataGridView控件的属性，读取DataTable行数据直接用table.Rows(i)(列索引/列名)即可

• 第三处：SQL语法错误

1. 查询BookID的SQL多写了多余的1，原WHERE 1BookID改为`WHERE `BookID
2. UPDATE语句中BookTitle字段后多了多余的单引号，WHERE条件写漏了字段匹配，原WHERE @BookID改为WHERE BookID = @BookID

• 第四处：参数未赋值问题
  你当前定义的三个SQL语句都没有给占位参数赋值，直接传入save_or_update_excel方法会报错，需要在循环内将Excel行的每个字段值对应赋值给参数。

修正后代码 #

Dim conn As OleDbConnection
Dim dta As OleDbDataAdapter
Dim dts As DataSet
Dim excel As String
Dim openFileDialog As New OpenFileDialog
Dim table As New DataTable

openFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|XLS Files (*.xls)|*.xls|CSV Files (*.csv)|*.csv|All Files (*.*)|*.*"

If (openFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
    Dim fi As New IO.FileInfo(openFileDialog.FileName)
    excel = fi.FullName
    ' 连接字符串添加HDR=Yes指定首行是表头，IMEX=1避免混合类型数据读取错误
    conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";")
    dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
    dts = New DataSet
    dta.Fill(dts, "[Sheet1$]")
    ' 修复：直接取DataSet里读取到的Excel表赋值给table
    table = dts.Tables("[Sheet1$]")
    conn.Close()
    
    ' 遍历Excel所有行，如果首行是表头不需要数据就改成i从1开始
    For i As Integer = 0 To table.Rows.Count - 1
        Dim bookId = table.Rows(i)(0).ToString() ' 假设第一列是BookID
        ' 修复SQL语法错误
        sql = "SELECT * FROM `tblbooks` WHERE `BookID` = '" & bookId & "'"
        sqladd = "INSERT INTO `tblbooks`(`AccessionNo`, `BookTitle`, `BookDesc`, `Author`, `PublishDate`, `BookPublisher`, `CategoryId`, `BookPrice`, `BookQuantity`, `Status`, `BookType`, `DeweyDecimal`, `OverAllQty`, `Donate`, `Remark`) VALUES (@AccessionNo,@BookTitle,@BookDesc,@Author,@PublishDate,@BookPublisher,@CategoryId,@BookPrice,@BookQuantity,@Status,@BookType,@DeweyDecimal,@OverAllQty,@Donate,@Remark)"
        sqledit = "UPDATE `tblbooks` SET `AccessionNo`=@AccessionNo,`BookTitle`=@BookTitle,`BookDesc`=@BookDesc,`Author`=@Author,`PublishDate`=@PublishDate,`BookPublisher`=@BookPublisher,`CategoryId`=@CategoryId,`BookPrice`=@BookPrice,`BookQuantity`=@BookQuantity,`Status`=@Status,`BookType`=@BookType,`DeweyDecimal`=@DeweyDecimal,`OverAllQty`=@OverAllQty,`Donate`=@Donate,`Remark`=@Remark WHERE `BookID` = @BookID"
        ' 注意：需要在save_or_update_excel方法里，把当前行的各个字段值赋值给对应的SQL参数
        save_or_update_excel(sql, sqladd, sqledit, table.Rows(i))
    Next
    sql = "SELECT * FROM `tblcategory`"
    reloadDtg(sql, dtgList)
End If
End Sub

如果你的save_or_update_excel方法内部有尝试将DataTable转换为ADODB.RecordSet的逻辑，现在table已经是正确读取了Excel数据的ADO.NET DataTable，不需要额外转换，修改方法内的转换逻辑直接用当前的DataTable操作即可解决原报错。

内容的提问来源于stack exchange，提问作者Chi Pham