Inserting multiple values into a SQL database from

Question

Just trying to insert data from 5 cells via a VBA script, into a column on an SQL server 08 database.

So basically I have 1 table with 4 columns, I want to insert multiple sets of data into the columns at once which would insert data into the DB with the below result..

Server Name     Middleware  Version License 
TEST6           Testing     1       1
TEST6           Testing1    1       1
TEST6           Testing2    1       1
TEST6           Testing3    1       1

I know the values are not correct on the below code, but I get the error message (below the vba code) when the VBA script is executed.

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 The column name 'middleware' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'middleware' may appear twice in the view definition.

Answer 1

I am assuming the data is in Excel. If so, just loop through the rows. Also, it's seems that your val1, val2 etc. don't match the example. Maybe you meant val3 to be D126 and val4 to be E126. I will assume that. Here is the corrected code:

Dim sSQL as string
Dim i as long

i=0
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
wend

Code not tested but it compiles.

Answer 2

I believe the columns you specify in your INSERT statement are duplicated and therefore not correct. Try:

item4 = item4 & "  [server_name],[middleware],[version],[license]"

Update: Your SQL statement should look like this:

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)

So you have to repeat the block between parenthesis for every row you want to insert.

However, you now only have 4 variables that hold 4 different values in your solution, so you will never be able to insert those 4 different rows because you only select values in cells B126, C126, C127 and D126. That will likely be the first row that you want to insert? Or do you want to add the 1,2,3 to Testing yourself and repeat the other values? Please explain and update your answer accordingly.

Answer 3

If you have multiple rows of data, it is much more efficient to insert all the rows in one go in a single call to a stored procedure. To do this you serialize your data into xml and then call a stored procedure which takes xml as a string parameter.

AFAIK you cannot serialize a Dictionary, but you can serialize a List.

So I suggest you loop through your Dictionary (List of Dictionaries) filling a List with appropriate values. I tend to use Structures for this, but simple classes work fine as well, e.g.:

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

Create your List and fill it e.g:

Dim myList As New List(Of DBData)
For Each kvPair In dict
    myList.Add(New DBData(kvPair.Key, kvPair.Value))
Next

Now for the bit of magic to serialize:

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)

Notice here I am stripping off the header of the xml that has just been created. That is because I have never been able to get SQL Server to work if the header was included - you probably can, I've just never looked hard enough.

We call the stored procedure like this (obviously change the connection string to one that works for you):

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

As to SQL Server the procedure required looks like this:

CREATE PROCEDURE [dbo].[uspDBDataInsert] 
    -- Add the parameters for the stored procedure here
    @dbdata varchar(MAX)

AS

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

Obviously here you will need to change the ArrayOf to whatever you call your class and of course use your own table.

Although I have shown here just a simple key/value pair, you can populate a multi-column table in exactly the same way.

One point to note: your class/structure must be Public in a Public module, otherwise serialize will not work. Also if you provide your own parameter constructor for a class (as I do), you must also provide a parameterless one, otherwise again the serializer will object.

HTH

Addendum

What I forgot to put, for the VB to work you need:

Imports System.IO
Imports System.Xml.Serialization
Imports System.Data.SqlClient