浏览Excel然后在Datagridview中查看，然后使用Vb.net保存在MySQL中

Question

好日子，我有一个程序，使用户浏览excel文件后，excel文件将显示并打开在datagridview，到目前为止浏览和打开工作正常，但是我不能保存在datagridview的价值观，我想将它保存到MYSQL Workbench

有人可以帮助我的感谢，我应该使用单独的按钮或只有一个按钮

这里是代码：浏览并打开Excel文件：

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 
     Dim conn As OleDb.OleDbConnection 

     Dim dta As OleDbDataAdapter 

     Dim dts As DataSet 
     Dim excel As String 
     Dim OpenFileDialog As New OpenFileDialog 

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

     If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then 

      Dim fi As New FileInfo(OpenFileDialog.FileName) 
      Dim FileName As String = OpenFileDialog.FileName 
      excel = fi.FullName 
      conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;") 
      dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn) 
      dts = New DataSet 
      dta.Fill(dts, "[Sheet1$]") 
      DataGridView1.DataSource = dts 
      DataGridView1.DataMember = "[Sheet1$]" 
      conn.Close() 
     End If 
    End Sub 

，这里是进口到数据库的代码，但是它不保存它是在不同的按钮

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click 


    For Each column As DataGridViewRow In DataGridView1.Columns 
      Dim con As MySqlConnection = New MySqlConnection 
      con.ConnectionString = "server = localhost; username = root;password=rootpassword; database=bussiness_violation" 
      Using cmd As New MySqlCommand("INSERT INTO `bussiness_violation`.`tbl_recordbusiness` VALUES('@ACCTNO', '@PERMITNO', '@LAST_NAME', '@FIRST_NAME', '@MIDDLE_NAME', '@COMMNAME', '@COMMADDR ','@PLATENO ', '@STATCODE', '@FORYEAR', '@ORDATE', '@ORNO', '@SERIES', '@ISSUANCEDATE', '@EXPIRATIONDATE', '@TotalAmt')", con) 
       cmd.Parameters.AddWithValue("@ACCTNO", column.Cells("ACCTNO").Value) 
       cmd.Parameters.AddWithValue("@PERMITNO", column.Cells("PERMITNO").Value) 
       cmd.Parameters.AddWithValue("@LAST_NAME", column.Cells("LAST_NAME").Value) 
       cmd.Parameters.AddWithValue("@FIRST_NAME", column.Cells("FIRST_NAME").Value) 
       cmd.Parameters.AddWithValue("@MIDDLE_NAME", column.Cells("MIDDLE_NAME").Value) 
       cmd.Parameters.AddWithValue("@COMMNAME", column.Cells("COMMNAME").Value) 
       cmd.Parameters.AddWithValue("@COMMADDR", column.Cells("COMMADDR").Value) 
       cmd.Parameters.AddWithValue("@PLATENO", column.Cells("PLATENO").Value) 
       cmd.Parameters.AddWithValue("@STATCODE", column.Cells("STATCODE").Value) 
       cmd.Parameters.AddWithValue("@FORYEAR", column.Cells("FORYEAR").Value) 
       cmd.Parameters.AddWithValue("@ORDATE", column.Cells("ORDATE").Value) 
       cmd.Parameters.AddWithValue("@ORNO", column.Cells("ORNO").Value) 
       cmd.Parameters.AddWithValue("@SERIES", column.Cells("SERIES").Value) 
       cmd.Parameters.AddWithValue("@ISSUANCEDATE", column.Cells("ISSUANCEDATE").Value) 
       cmd.Parameters.AddWithValue("@EXPIRATIONDATE", column.Cells("EXPIRATIONDATE").Value) 
       cmd.Parameters.AddWithValue("@TotalAmt", column.Cells("TotalAmt").Value) 


       con.Open() 
       cmd.ExecuteNonQuery() 
       con.Close() 
      End Using 


     Next 
     MessageBox.Show("Records inserted.") 


End Sub 

End Class 

这里是接口Test GUI for Browse and Import

Answer 1

什么错误，你得到的地方吗？它是一个VB错误或SQL错误？

备注：保存到数据库的代码是错误的，对于每一列，你都实例化一个新的MySqlConnection。把这部分放在你的每个上面

Dim con As MySqlConnection = New MySqlConnection 
con.ConnectionString = "..." 

For Each column As DataGridViewRow In DataGridView1.Columns 
    'Your code here 

Answer 2

你没有提到你得到了什么错误。我已更新您的代码以显示错误消息。假设其“Microsoft.ACE.OLEDB.XX.0提供程序未在本地计算机上注册”错误“。您需要根据系统上的安装将目标CPU更新为x64或x86。请参阅。

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click 
    Dim conn As OleDb.OleDbConnection = Nothing 

    Dim dta As OleDbDataAdapter 

    Dim dts As DataSet 
    Dim excel As String 
    Dim OpenFileDialog As New OpenFileDialog 
    Dim isOpened As Boolean 

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

     If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then 

      Dim fi As New FileInfo(OpenFileDialog.FileName) 
      Dim FileName As String = OpenFileDialog.FileName 
      excel = fi.FullName 
      conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;") 
      isOpened = True 
      dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn) 
      dts = New DataSet 
      dta.Fill(dts, "[Sheet1$]") 
      DataGridView1.DataSource = dts 
      DataGridView1.DataMember = "[Sheet1$]" 

     End If 
    Catch ex As Exception 
     MessageBox.Show(ex.Message) 
    Finally 
     If isOpened Then 
      conn.Close() 
     End If 
    End Try 
End Sub