把datagridview记录全部保存在Mysql问题和Mysql问题，datagridviewmysql

把datagridview记录全部保存在Mysql问题和Mysql问题，datagridviewmysql

1.

 Dim MyConnection As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=taipan; User=root;Password=ckjason9245699;Port=3306;Option=3;")

    Dim cmd As New Odbc.OdbcCommand

    Dim adapter As New Odbc.OdbcDataAdapter(cmd)

 

 

for i=1 to datagridview1.rows.count-1

 cmd.commandtext="insert into staff(staff_No)values('"Datagridview1.Rows(i).Cells("Staff_No").Value"')"

 cmd.connection=MyConnection

 

 MyConnection.Open()

  Dim tbl as new DataTable

 adapter.Fille(tbl)

 MyConnection.Close()

next

Msgbox("OK")

 

上面的代码是否可以把datagridview1的所有记录一次过保存在Mysql数据库？如有错误，请帮忙修改。

 

2.  如何把Excel文件所有资料导入到Mysql？

回答你的第二个问题， 以下这段代码可以将EXCEL中的记录读到一个datatable中， 然后就是Weidong的方法插入数据库：

    Public Sub ConnectOleDb(templatePath As String)
        Try
            Dim objExcel As New Excel.Application
            Dim objWorkBook As Excel.Workbook = objExcel.Workbooks.Open(templatePath)

            For Each objWorkSheets In objWorkBook.Worksheets
                'SheetList.Add(objWorkSheets.Name)
            Next

            Dim MyConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection( _
             "provider=Microsoft.Jet.OLEDB.4.0; " & _
              "data source=" & templatePath & "; " & _
              "Extended Properties=Excel 8.0;")

            Dim MyCommand As New OleDb.OleDbDataAdapter( _
              "select * from [" & objWorkBook.Worksheets(1).name & "$]", MyConnection)
            Dim DS As New System.Data.DataSet()

            MyCommand.Fill(DS)

            Dim tempdt As New DataTable
            For Each c As DataColumn In DS.Tables(0).Columns
                tempdt.Columns.Add(c.ColumnName, GetType(System.String))
            Next

            For Each r As DataRow In DS.Tables(0).Rows
                tempdt.Rows.Add(r.ItemArray)
            Next

            Me.DataGridView1.DataSource = tempdt

            MyConnection.Dispose()
            MyConnection.Close()
            MyCommand.Dispose()
            objWorkBook.Close(False)
            objExcel.Quit()
            objExcel = Nothing
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        'objWorkSheets = objWorkBook.Sheets(1)
    End Sub

希望有用。

Best regards,