Clash Royale CLAN TAG

VBA ADO SQL too few parameters

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 = "pathtofile.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.

2 Answers
2

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 = "pathtofile.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


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 = "pathtofile.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 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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.