none
关于EXCEL导入到ACCESS的问题,实在没法搞下去了,恳请达人相助! RRS feed

  • 问题

  • 代码如下:思路是用两个dataset 把第一个mydataset1中的表用SQL语句添加到第二个mydataset2中的表,我不知怎样写SQL语句.如果谁有现成的代码完成这个功能更好,请贴出来.谢谢
    Dim fileName As String
            Me.OpenFileDialog1.Filter = "Excle文件(*.xls)|*.xls"
            If Me.OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                fileName = Me.OpenFileDialog1.FileName
                'Use below code to retrieve data from an Excel file.
                'Code to open the file here.
                Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"

                'Now using the OledbDataAdapter you can query the excel sheet.
                Dim myDataset1 As New DataSet
                Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
                'da.TableMappings.Add("Table", "ExcelTest")
                da.Fill(myDataset1)
                Dim sql As String
                Dim connStr As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\test.mdb"
                Dim cn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(connstr)
                Dim myDataset2 As New DataSet
                Dim db As New OleDb.OleDbDataAdapter("select * from per_info", connStr)
                db.Fill(myDataset2)

              不知如何写了这里请大家帮忙!
            End If
    2009年6月8日 8:56

答案

  • Hi 虎子,

    我想你现在如何从Excel中读出数据应该没问题了吧,现在问题是如何写insert语句来插入到MS Access中去吧。我找到一个类可以自动对DataTable生成Sql语句。
    Public Class GenerateSQL
        Public Shared Function BuildInsertSQL(ByVal table As DataTable) As String
            Dim sql As StringBuilder = New StringBuilder("INSERT INTO " + table.TableName + " (")
            Dim values As StringBuilder = New StringBuilder("VALUES (")
            Dim bFirst As Boolean = True
            Dim bIdentity As Boolean = False
            Dim identityType As String = Nothing

            Dim column As DataColumn
            For Each column In table.Columns
                If column.AutoIncrement Then
                   bIdentity = True
                    Select Case column.DataType.Name
                        Case "Int16"
                            identityType = "smallint"
                            Exit For
                        Case "SByte"
                            identityType = "tinyint"
                            Exit For
                        Case "Int64"
                            identityType = "bigint"
                            Exit For
                        Case "Decimal"
                            identityType = "decimal"
                            Exit For
                        Case Else
                            identityType = "int"
                            Exit For
                    End Select
                Else
                    If (bFirst) Then
                        bFirst = False
                    Else
                        sql.Append(", ")
                        values.Append(", ")
                        sql.Append(column.ColumnName)
                        values.Append("")
                        values.Append(column.ColumnName)
                    End If
                End If
            Next
            sql.Append(") ")
            sql.Append(values.ToString())
            sql.Append(")")

            If (bIdentity) Then
                sql.Append("; SELECT CAST(scope_identity() AS ")
                sql.Append(identityType)
                sql.Append(")")
            End If
            Return sql.ToString()
        End Function
    End Class

    这个类的源代码出处是Codeproject.
    http://www.codeproject.com/KB/database/GenerateSQL.aspx

    Sincerely,
    Kira Qian


    Please mark the replies as answers if they help and unmark if they don't.
    2009年6月9日 8:30

全部回复

  • 一行行加
    dim dr2 as DataRow

    for each dr2 in myDataset2.Tables(0).Rows

    dim dr1 as DataRow
    dr1=myDataset1.Tables(0).NewRow
    dr1("FieldName")=dr2("FieldName")

    myDataset1.Tables(0).Rows.Add(dr1)

    next
    http://feiyun0112.cnblogs.com/
    2009年6月9日 2:22
    版主
  • Hi 虎子,

    我想你现在如何从Excel中读出数据应该没问题了吧,现在问题是如何写insert语句来插入到MS Access中去吧。我找到一个类可以自动对DataTable生成Sql语句。
    Public Class GenerateSQL
        Public Shared Function BuildInsertSQL(ByVal table As DataTable) As String
            Dim sql As StringBuilder = New StringBuilder("INSERT INTO " + table.TableName + " (")
            Dim values As StringBuilder = New StringBuilder("VALUES (")
            Dim bFirst As Boolean = True
            Dim bIdentity As Boolean = False
            Dim identityType As String = Nothing

            Dim column As DataColumn
            For Each column In table.Columns
                If column.AutoIncrement Then
                   bIdentity = True
                    Select Case column.DataType.Name
                        Case "Int16"
                            identityType = "smallint"
                            Exit For
                        Case "SByte"
                            identityType = "tinyint"
                            Exit For
                        Case "Int64"
                            identityType = "bigint"
                            Exit For
                        Case "Decimal"
                            identityType = "decimal"
                            Exit For
                        Case Else
                            identityType = "int"
                            Exit For
                    End Select
                Else
                    If (bFirst) Then
                        bFirst = False
                    Else
                        sql.Append(", ")
                        values.Append(", ")
                        sql.Append(column.ColumnName)
                        values.Append("")
                        values.Append(column.ColumnName)
                    End If
                End If
            Next
            sql.Append(") ")
            sql.Append(values.ToString())
            sql.Append(")")

            If (bIdentity) Then
                sql.Append("; SELECT CAST(scope_identity() AS ")
                sql.Append(identityType)
                sql.Append(")")
            End If
            Return sql.ToString()
        End Function
    End Class

    这个类的源代码出处是Codeproject.
    http://www.codeproject.com/KB/database/GenerateSQL.aspx

    Sincerely,
    Kira Qian


    Please mark the replies as answers if they help and unmark if they don't.
    2009年6月9日 8:30