VBA ADO SQL too few parameters

Question

I asked this question before but I am still stuck and unable to put parameters inside a SQL query.

Public Function testInsert(tableName As String, dType As String, role As String, FiscalYear As String) Dim cmd As ADODB.Command Dim conn As ADODB.connection Dim prm As ADODB.Parameter Dim connectionString As String Dim SQL As String Set conn = New ADODB.connection Set cmd = New ADODB.Command DBPath = "path\to\file.xlsm" connectionString = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath &";HDR=Yes';" SQL = "INSERT INTO [" & tableName & "$] ([Year], [Type], [role]) VALUES (p1, p2, p3)" 'Open connection conn.Open connectionString 'Set command text cmd.CommandText = SQL cmd.CommandType = adCmdText 'Set connection cmd.ActiveConnection = conn 'Set paramters in SQL query Set prm = cmd.CreateParameter("p1", adInteger, adParamInput) cmd.Parameters.Append prm cmd.Parameters("p1").Value = 1 Set prm = cmd.CreateParameter("p2", adInteger, adParamInput) cmd.Parameters.Append prm cmd.Parameters("p2").Value = 2 Set prm = cmd.CreateParameter("p3", adInteger, adParamInput) cmd.Parameters.Append prm cmd.Parameters("p3").Value = 3 cmd.Execute conn.Close End Function

I am using VBA inside of excel-2010 and I am running the query against an external excel file on my computer. I get a too few paramaters expecting three error.

Please note this code works if the values are string literals so the issue is with the parameters.

Mathieu Guindon · Accepted Answer · 2018-07-27 15:18:37Z

You didn't follow the reply from yesterday :( You didn't need that Prm variable, but if you used it, then you should use one for the parameter.

Public Function testInsert(tableName As String, dType As String, role As String, FiscalYear As String) Dim cmd As ADODB.Command Dim conn As ADODB.connection Dim prm1 As ADODB.Parameter Dim prm2 As ADODB.Parameter Dim prm3 As ADODB.Parameter Dim connectionString As String Dim SQL As String Set conn = New ADODB.connection Set cmd = New ADODB.Command DBPath = "path\to\file.xlsm" connectionString = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath &";HDR=Yes';" SQL = "INSERT INTO [" & tableName & "$] ([Year], [Type], [role]) VALUES (?, ?, ?)" 'Open connection conn.Open connectionString 'Set command text cmd.CommandText = SQL cmd.CommandType = adCmdText 'Set connection cmd.ActiveConnection = conn 'Set paramters in SQL query Set prm1 = cmd.CreateParameter("@p1", adInteger, adParamInput) cmd.Parameters.Append prm1 cmd.Parameters("@p1").Value = 1 Set prm2 = cmd.CreateParameter("@p2", adInteger, adParamInput) cmd.Parameters.Append prm2 cmd.Parameters("@p2").Value = 2 Set prm3 = cmd.CreateParameter("@p3", adInteger, adParamInput) cmd.Parameters.Append prm3 cmd.Parameters("@p3").Value = 3

You didn't need those prm1, prm2, prm3 at all:

'Set paramters in SQL query cmd.Parameters.Append cmd.CreateParameter("@p1", adInteger, adParamInput) cmd.Parameters.Append cmd.CreateParameter("@p2", adInteger, adParamInput) cmd.Parameters.Append cmd.CreateParameter("@p3", adInteger, adParamInput) cmd.Parameters("@p1").Value = 1 cmd.Parameters("@p2").Value = 2 cmd.Parameters("@p3").Value = 3

Thats exactly what I did and I got the same error expecting three paramaters
Sure but I can't use real data and it will have to be tomorrow on my personal computer if that is ok?
@User, no it wouldn't work this Saturday. But if you like I can make it today between (3 PM - 7 PM EST).

Mathieu Guindon · Accepted Answer · 2018-07-27 15:18:25Z

Name Parameter with '@' at begining like @p1

Public Function testInsert(tableName As String, dType As String, role As String, FiscalYear As String) Dim cmd As ADODB.Command Dim conn As ADODB.connection Dim prm As ADODB.Parameter Dim connectionString As String Dim SQL As String Set conn = New ADODB.connection Set cmd = New ADODB.Command DBPath = "path\to\file.xlsm" connectionString = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath &";HDR=Yes';" SQL = "INSERT INTO [" & tableName & "$] ([Year], [Type], [role]) VALUES (@p1, @p2, @p3)" 'Open connection conn.Open connectionString 'Set command text cmd.CommandText = SQL cmd.CommandType = adCmdText 'Set connection cmd.ActiveConnection = conn 'Set paramters in SQL query Set prm = cmd.CreateParameter("@p1", adInteger, adParamInput) cmd.Parameters.Append prm cmd.Parameters("@p1").Value = 1 Set prm = cmd.CreateParameter("@p2", adInteger, adParamInput) cmd.Parameters.Append prm cmd.Parameters("@p2").Value = 2 Set prm = cmd.CreateParameter("@p3", adInteger, adParamInput) cmd.Parameters.Append prm cmd.Parameters("@p3").Value = 3 cmd.Execute conn.Close End Function

Collectives™ on Stack Overflow

VBA ADO SQL too few parameters

2 Answers 2

4 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Related