VBA ADO SQL参数太少

Question

我以前问过这个问题，但我仍然卡住了，无法将参数放入SQL查询中。

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

我在excel-2010中使用VBA，并且在我的计算机上对外部excel文件运行查询。我得到的参数太少，预期会有三个错误。

请注意，如果值是字符串文字，则此代码可以工作，因此问题出在参数上。

Answer 1

您没有遵循昨天的回复:(您不需要这个Prm变量，但是如果您使用了它，那么您应该为参数使用一个变量。

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

你根本不需要那些prm1，prm2，prm3：

'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

Answer 2

名称参数在开头带有'@‘，如@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