none
把datagridview记录全部保存在Mysql问题和Mysql问题 RRS feed

  • 问题

  • 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?


    • 已编辑 ckjason 2011年9月7日 6:16
    2011年9月7日 6:14

答案

  • 回答你的第二个问题, 以下这段代码可以将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,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • 已标记为答案 ckjason 2011年9月12日 4:22
    2011年9月11日 11:58
    版主

全部回复

  • 回答你第一个问题:

    假设你连接什么的没有任何问题,那你应该考虑简化做法

    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

    cmd.Connection = MyConnection

    Dim adapter As New Odbc.OdbcDataAdapter(cmd)

    cmd.CommandText="select * from 数据表名称"

    Dim builder As New Odbc.OdbcCommandBuilder(adapter)     '自动生成增删改语句,表必须有主键

     Dim dt As New DataTable()

    adapter.Fill(dt)

    For i As Integer =1 to datagridview1.rows.count-1

     dt.Rows.Add(Datagridview1.Rows(i).Cells("Staff_No").Value.ToString())

    Next

    adapter.Update(dt)   '批量插入

    Msgbox("OK")

    PS:建议你最好有可能最好用专门适合.NET的MySql代替Odbc,可以到此处,http://dev.mysql.com/downloads/connector/net/,下载。


    如果你有其它意见或私下交流,请直接发送maledong_work@foxmail.com;或者讨论(Talk)
    If you do not have QQ, please open the page and download it and click the image to talk or leave message for me.
    下载MSDN桌面工具(Vista,Win7)
    下载Technet桌面小工具(Vista,Win7)
    慈善点击,点击此处
    2011年9月8日 5:49
    版主
  • 回答你的第二个问题, 以下这段代码可以将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,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • 已标记为答案 ckjason 2011年9月12日 4:22
    2011年9月11日 11:58
    版主