通过VBA脚本从EXCEL向SQL数据库插入多个数值

Question 1

我只是想通过VBA脚本将5个单元格的数据插入到SQL server 08数据库的一个列中。

因此，基本上我有一个有4个列的表，我想一次插入多组数据到这些列中，这将把数据插入数据库，结果如下。

Server Name     Middleware  Version License 
TEST6           Testing     1       1
TEST6           Testing1    1       1
TEST6           Testing2    1       1
TEST6           Testing3    1       1
我知道下面的代码中的值是不正确的，但我在执行VBA脚本时得到了错误信息（在vba代码下面）。
Dim val1 As String, val2 As String, val3 As String, val4 As String
val1 = Range("B126").Value
val2 = Range("C126").Value
val3 = Range("C127").Value
val4 = Range("D126").Value
conn.Open sConnString
Dim item As String
item4 = "INSERT INTO [IndustrialComp].[dbo].[Middleware]("
item4 = item4 & "  [server_name],[middleware],[middlware],[version]"
item4 = item4 & "  )Values("
item4 = item4 & "  '" & val1 & "', '" & val2 & "', '" & val3 & "','" & val4 & "')"
conn.Execute item4
End Sub
Msg 264, Level 16, State 1, Line 1
在SET子句中，列名'middleware'被指定了一次以上。在同一个SET子句中，一个列不能被分配一个以上的值。修改SET子句，确保一个列只被更新一次。如果SET子句更新了一个视图的列，那么列名'middleware'可能会在视图定义中出现两次。

Question 2


          
           
            我相信你在INSERT语句中指定的列是重复的，因此不正确。
           
           item4 = item4 & "  [server_name],[middleware],[version],[license]"
你的SQL语句应该是这样的。
INSERT INTO [IndustrialComp].[dbo].[Middleware]([server_name],[middleware],[version],[license])
VALUES ('TEST6','Testing',1,1)
      ,('TEST6','Testing1',1,1)
      ,('TEST6','Testing2',1,1)
      ,('TEST6','Testing3',1,1)
因此，你必须为你想插入的每一行重复括号之间的块。
然而，你现在只有4个变量，在你的解决方案中持有4个不同的值，所以你将永远无法插入这4个不同的行，因为你只选择单元格B126、C126、C127和D126中的值。这很可能是你想插入的第一行？还是你想自己添加1,2,3到Testing ，然后重复其他的值？
请解释并相应地更新你的答案。

Question 3


          
           
            
             
              我假设数据是在Excel中。如果是这样的话，只要循环浏览这些行即可。另外，你的val1、val2等似乎与例子不一致。也许你是说val3是D126，val4是E126。我将会这样认为。下面是更正后的代码。
             
             Dim sSQL as string
Dim i as long
while ActiveSheet.Range("B126").offset(i,0).value <> "" 'stop when there is a blank cell
    i=i+1
    conn.Open sConnString
    sSQL = "INSERT INTO [IndustrialComp].[dbo].[Middleware]("
    sSQL = sSQL & "  [server_name],[middleware],[version],[license]"
    sSQL = sSQL & "  )Values ("
    sSQL = sSQL & "  '" & ActiveSheet.Range("B126").offset(i,0).Value & "', "
    sSQL = sSQL & "  '" & ActiveSheet.Range("C126").offset(i,0).Value & "', "
    sSQL = sSQL & "  '" & ActiveSheet.Range("D126").offset(i,0).Value & "', "
    sSQL = sSQL & "  '" & ActiveSheet.Range("E126").offset(i,0).Value & "' "
    sSQL = sSQL & ")"
    conn.Execute sSQL
代码没有经过测试，但可以编译。

Question 4


          
           
            
             
              
               如果你有多行数据，在一次调用存储过程中一次性插入所有的行会更有效率。  要做到这一点，你要把你的数据序列化为xml，然后调用一个存储过程，这个存储过程把xml作为一个字符串参数。
              
              
               AFAIK你不能序列化一个Dictionary，但你可以序列化一个List。
              
              
               所以我建议你在你的字典（List of Dictionaries）中循环，用适当的值填充一个List。  我倾向于使用结构体来做这件事，但简单的类也可以工作，例如。
              
              Public Class DBData
    Public pKey As Integer
    Public pValue As Double
    Public Sub New(key As Integer, val As Double)
        pKey = key
        pValue = val
    End Sub
    Public Sub New()
        pKey = 0
        pValue = 0.0
    End Sub
End Class
创建你的列表并填充它，例如
Dim myList As New List(Of DBData)
For Each kvPair In dict
    myList.Add(New DBData(kvPair.Key, kvPair.Value))
现在来看看序列化的神奇之处。
Dim sw As New StringWriter
Dim serializer As New XmlSerializer(GetType(List(Of DBData)))
Dim ns As New XmlSerializerNamespaces()
serializer.Serialize(sw, myList)
Dim xml As String
xml = sw.ToString
Dim pos As Integer
pos = xml.IndexOf("<Array")
xml = xml.Substring(pos)
注意，在这里我把刚刚创建的xml的头去掉了。  这是因为我从来没有让SQL Server在包含头的情况下工作过--你可能可以，我只是没有认真寻找。
我们像这样调用存储过程（显然要把连接字符串改成对你有用的）。
Using conn As New SqlConnection("Integrated Security=true; Initial Catalog=dbname; Data Source=servername")
    Using cmd As New SqlCommand("uspDBDataInsert", conn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim param As SqlParameter
        param = cmd.CreateParameter
        param.ParameterName = "@dbdata"
        param.DbType = DbType.String
        param.Value = xml
        cmd.Parameters.Add(param)
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
    End Using
End Using
至于SQL Server，所需的过程看起来像这样。
CREATE PROCEDURE [dbo].[uspDBDataInsert] 
    -- Add the parameters for the stored procedure here
    @dbdata varchar(MAX)
BEGIN try
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @idoc int
    --Create an internal representation of the XML document
    EXEC sp_xml_preparedocument @idoc OUTPUT, @dbdata
    insert into DBData
        id, rowvalue    
    select
        pKey,pValue
    FROM OPENXML (@idoc, '/ArrayOfDBData/DBData',2)
    WITH (
        pKey int,
        pValue float
    select @@ROWCOUNT
end try
begin catch
    declare @msg nvarchar(200)
    SELECT @msg = ('Error during insert of DBData...')
end catch
很明显，在这里你需要把ArrayOf改成你对类的称呼，当然还要使用你自己的表。
虽然我在这里展示的只是一个简单的键/值对，但你可以用完全相同的方式来填充一个多列的表。
有一点需要注意：你的类/结构必须是Public模块中的Public，否则序列化将无法工作。  另外，如果你为一个类提供你自己的参数构造器（就像我一样），你也必须提供一个无参数的构造器，否则序列化器也会反对。
我忘了写的是，为了让 VB 工作，你需要。