ADODB Connection to upload data to SQL using bind variables

Question

I'm trying to upload some data into a SQL database from a spreadsheet. I'm Using the code from here as a base, had to make a few mods in order to get it to work.

Sub newtest()

Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim cs As String
Dim C1, C2, C3, C4 As String
Dim RowNo As Long
    Dim wbk As Workbook
    Dim strFile As Variant
    Dim shtname As String
    ChDir "xxxxxx"
    strFile = Application.GetOpenFilename
    If strFile = False Then
    Exit Sub
    Else: End If

    Application.ScreenUpdating = False


Set conn = New ADODB.Connection

cs = "Provider=SQLOLEDB;Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxx; Password=xxxxxx; "

conn.ConnectionString = cs

conn.Open


' *** Open workbooks first ***
Set wbk = Workbooks.Open(strFile)
shtname = ActiveWorkbook.Worksheets(1).Name
lastrow = wbk.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets(shtname)

cmd.ActiveConnection = conn
cmd.CommandText = "insert into EcommerceXRefMapping_C_Copy values (@EquivalentPartNumber_C, @Manufacturer_C, @CatamacPartNumber_C, @Notes_C)"
cmd.NamedParameters = True

 'Skip the header row
        RowNo = 2

        'Loop until empty cell in Col 1
        Do Until .Cells(RowNo, 1) = ""
            i = i + 1
            C1 = .Cells(RowNo, 1)
            C2 = .Cells(RowNo, 2)
            C3 = .Cells(RowNo, 3)
            C4 = .Cells(RowNo, 4)

            'Generate and execute sql statement to import the excel rows to SQL Server table

            cmd.Parameters.Append cmd.CreateParameter("@EquivalentPartNumber_C", adVarChar, adParamInput, 256, C1)
            cmd.Parameters.Append cmd.CreateParameter("@Manufacturer_C", adVarChar, adParamInput, 256, C2)
            cmd.Parameters.Append cmd.CreateParameter("@CatamacPartNumber_C", adVarChar, adParamInput, 256, C3)
            cmd.Parameters.Append cmd.CreateParameter("@Notes_C", adVarChar, adParamInput, 256, C4)

            cmd.Execute

            RowNo = RowNo + 1
        Loop

End With

        wbk.Close
        Application.ScreenUpdating = True

End Sub

I'm getting an error when trying to execute

cmd.execute

The error message is

Run-time error'-2147217800 (80040e14)':
Must declare the scalar variable "@EquivalentPartNumber_C".

Any ideas how to fix? I thought that the declaring of the variables was done with the "@" character? Sorry, I'm quite new to VBA so please bear with me.

Thanks!

Kazimierz Jawor · Accepted Answer · 2016-02-19 08:15:58Z

3

In my opinion you mix notation for stored procedure and text type of command but...

you can omit @ when passing parameters
you should add cmd.CommandType = adCmdText right after your cmdCommandText line
I would change SQL query into:

"insert into EcommerceXRefMapping_C_Copy values (?, ?, ?, ?)"

and next pass parameters in this way (where correct order is important):

cmd.Parameters(0) = c1
cmd.Parameters(1) = c2
cmd.Parameters(2) = c3
cmd.Parameters(3) = c4

edited Feb 19, 2016 at 8:15

answered Feb 19, 2016 at 6:58

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

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

4 Comments

Brendan Gooden Over a year ago

Cmd.Type gives error "Method or data member not found" ?? If I omit that, i get the error "Column name or number of supplied values does not match table definition" ?

Brendan Gooden Over a year ago

Should mention that there are other columns in this table, it is only these 4 columns that I am wanting to insert data into.

Kazimierz Jawor Over a year ago

so, in fact you have wrong sql statement. See this link and change as required

Kazimierz Jawor Over a year ago

one more thing, instead of cmd.Type = adCmdText you should use cmd.CommandType = adCmdText

Collectives™ on Stack Overflow

ADODB Connection to upload data to SQL using bind variables

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related