返回值，指示通过ADO / VBA更新SQL Server存储过程的成功/失败 [英] Return value indicating update success/failure of SQL Server stored procedure via ADO/VBA

问题描述

我有一个SQL Server 2008存储过程，用于更新表中的值。我希望存储过程返回一个整数值，该值指示更新成功（返回0）或失败（返回错误号）。通过ADO和VBA实现此目标的最佳方法是什么？这里我的一些代码以简化形式执行更新...我只是不确定如何获取存储过程的返回值

I have a SQL Server 2008 stored procedure that updates values in a table. I would like to have the stored procedure return an integer value indicating that the update was successful (return 0) or not (returns error number). What would be the best way to accomplish this via ADO and VBA? Here some of my code in simplified form that performs the update ... I am just not sure how to get back the return value of the stored procedure

Public Function DoUpdate(employeeID as integer, firstName as string, lastname as string) as integer

  Dim cnn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim activeConnectionString As String

  activeConnectionString = GetActiveConnectionString()

  Set cnn = New ADODB.Connection
  cnn.ConnectionString = activeConnectionString
  cnn.CursorLocation = adUseClient
  cnn.Open

  Set cmd = New ADODB.Command
  cmd.ActiveConnection = cnn
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "uspUpdateEmployeeName"
  cmd.NamedParameters = True

  cmd.Parameters("@EmployeeID").Value = employeeID 
  cmd.Parameters("@FirstName").Value = firstName
  cmd.Parameters("@LastName").Value = lastName

  cmd.Execute

  'Unsure of how to get back return value here
  'DoUpdate = returnValue

  Set cnn = Nothing

End Function

推荐答案

如果使用

Dim lngRecs As Long

cmd.Execute lngRecs

lngRecs应该包含受影响的记录。

lngRecs should contain records affected.

这篇关于返回值，指示通过ADO / VBA更新SQL Server存储过程的成功/失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！