VBA Excel SQL Server INSERT query

Question

I'm having trouble with an SQL Server INSERT Query.

First, here's what I am trying to achieve:

I have a list of product in an excel worksheet that I would like to export into a MS SQL Server.

Here is the code I tried:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim Client As String

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

//Worksheets("Config").cells(1, "B").Value contains the connection string
cn.Open Worksheets("Config").Cells(1, "B").Value

cmd.ActiveConnection = cn
cmd.CommandType = adCmdText

If Worksheets("Configuration").Cells(2, "B").Value = 1 Then
    cmd.CommandText = "SELECT * FROM Cardex WHERE NbCardex = ?"
ElseIf Worksheets("Configuration").Cells(2, "B").Value = 0 Then
    cmd.CommandText = "SELECT * FROM Cardex WHERE Name = ?"
End If

cmd.Prepared = True
cmd.Parameters.Append cmd.CreateParameter("Client", adVarChar, adParamInput, 250, 
                                 UCase(Worksheets("Exporter").Cells(2, "B").Value))

Set rs = cmd.Execute

If Not rs.EOF Then
    Client = rs.Fields("NoCardex").Value
End If

rs.Close

//The code work until it reach this point, The next lines I am not sure what is wrong
//Hell I'm not even sure what i am doing.
//Problem is, nothing is added into the table
Dim cell As Range
For Each cell In Worksheets("Exporter").Range("Liste")
  If cell.Row > 4 And Not cell.Value = vbNullString Then
    Set cmd = New ADODB.Command

    cmd.ActiveConnection = cn
     cmd.CommandType = adCmdText
     cmd.CommandText = "INSERT INTO NewTable(Dte, NbTr, NbClient, NbTr2, NbLine, NbProd, 
                         Desc, Qty, Cost, Prc, FlImp, FLDone) 
                        VALUES(?Date, ?Trans, ?Client, ?Projet, ?Ligne, ?Prod, ?Desc, 
                         ?Qte, ?Cout, ?Vend, ?Imp, 0)"
     cmd.Prepared = True

     cmd.Parameters.Append cmd.CreateParameter("Date", adVarChar, adParamInput, 8, Worksheets("Exporter").Cells(1, "E").Value)
     cmd.Parameters.Append cmd.CreateParameter("Trans", adVarChar, adParamInput, 15, Worksheets("Exporter").Cells(1, "B").Value)
     cmd.Parameters.Append cmd.CreateParameter("Client", adVarChar, adParamInput, 15, Client)
        cmd.Parameters.Append cmd.CreateParameter("Projet", adVarChar, adParamInput, 20, Worksheets("Exporter").Cells(2, "E").Value)

     cmd.Parameters.Append cmd.CreateParameter("Ligne", adInteger, adParamInput, , (cell.Row - 4))
     cmd.Parameters.Append cmd.CreateParameter("Prod", adVarChar, adParamInput, 30, cell.Value)
     cmd.Parameters.Append cmd.CreateParameter("Desc", adVarChar, adParamInput, 8000, Worksheets("Exporter").Cells(cell.Row, "B").Value)
     cmd.Parameters.Append cmd.CreateParameter("Qte", adCurrency, adParamInput, , Worksheets("Exporter").Cells(cell.Row, "C").Value)
     cmd.Parameters.Append cmd.CreateParameter("Cout", adCurrency, adParamInput, , Worksheets("Exporter").Cells(cell.Row, "D").Value)
     cmd.Parameters.Append cmd.CreateParameter("Vend", adCurrency, adParamInput, , Worksheets("Exporter").Cells(cell.Row, "E").Value)
     cmd.Parameters.Append cmd.CreateParameter("Imp", adSmallInt, adParamInput, , Worksheets("Exporter").Cells(cell.Row, "F").Value)

     cmd.Execute()
  End If
Next

cn.Close

I get an error that says:

Execution error '-2147217900 (80040e14)':
The Scalar variable "@P1Date" must be declare.

Can anyone help me?

PS I know VBA comment aren't done with "//" but the "'" make the code less easier to read on here so I switch them out

I am a bit stumped. You are certain that the is no reference to P1Date anywhere in your code? — Anthony Horne
– Anthony Horne, Commented May 20, 2014 at 14:31
Long shot but Date is often a reserved word - try using something else, or enclose it in [] wherever you use it as a parameter name. — Rory
– Rory, Commented May 20, 2014 at 15:07
@AnthonyHorne yes my guess is that the "?" in "?Date" get switch for "@P1" witch would be parameter number 1 and that it get squash in with the reste thus the result "@P1Date" but I dont know how to correcte this or why its doing this — Sebastien
– Sebastien, Commented May 21, 2014 at 12:13
@Rory I dont think this is the problem "?Date" is my parameter name inside my query it should get swith out by this line cmd.Parameters.Append cmd.CreateParameter("Date", adVarChar, adParamInput, 8, Worksheets("Exporter").Cells(1, "E").Value) — Sebastien
– Sebastien, Commented May 21, 2014 at 12:16
I know - couldn't hurt to at least test though using a different name. ;) — Rory
– Rory, Commented May 21, 2014 at 12:46

Rory · Accepted Answer · 2014-05-21 14:14:18Z

2

I don't think named parameters will work. Try using:

cmd.CommandText = "INSERT INTO NewTable(Dte, NbTr, NbClient, NbTr2, NbLine, NbProd, 
                     Desc, Qty, Cost, Prc, FlImp, FLDone) 
                    VALUES(?, ?, ?, ?, ?, ?, ?, 
                     ?, ?, ?, ?, 0)"

and make sure that the parameters you append later are in the correct order. ;)

answered May 21, 2014 at 14:14

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Excel SQL Server INSERT query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related