存储过程插入和更新语句工作，但行受到影响是-1

Question

我正在开发一个可以通过VB程序添加赞助商的程序。当我在SQL中测试用于添加和更新赞助商的存储过程时，它们正在工作，但由于受影响的行正在返回-1，所以两者都抛出了一个错误。不过，当我查看数据库时，插入/更新确实有效。有人能帮我弄清楚为什么受影响的行会返回-1(如果插入有效的话(吗？

它为两个存储过程都带回了-1。以下是添加赞助商的代码：

SQL:

GO
CREATE PROCEDURE uspAddSponsor
@intSponsorID AS INTEGER OUTPUT
,@strFirstName AS VARCHAR(50)
,@strLastName AS VARCHAR(50)
,@strStreetAddress AS VARCHAR(50)
,@strCity AS VARCHAR(50)
,@strState AS VARCHAR(50)
,@strZip AS VARCHAR(50)
,@strPhoneNumber AS VARCHAR(50)
,@strEmail AS VARCHAR(50)
AS 
SET NOCOUNT ON      --Report only errors
SET XACT_ABORT ON   --Terminate and rollback transaction on error
BEGIN TRANSACTION
INSERT INTO TSponsors WITH (TABLOCKX) (strFirstName, strLastName, strStreetAddress, strCity, strState, strZip, strPhoneNumber, strEmail)
VALUES               (@strFirstName, @strLastName, @strStreetAddress, @strCity, @strState, @strZip, @strPhoneNumber, @strEmail)
SELECT @intSponsorID = MAX(intSponsorID) FROM TSponsors
COMMIT TRANSACTION
GO

VB代码：

Private Sub AddSponsor(ByVal strFirstName As String, ByVal strLastName As String, ByVal strAddress As String, ByVal strCity As String, ByVal strState As String, ByVal strZip As String, ByVal strPhoneNumber As String, ByVal strEmail As String)
Dim intRowsAffected As Integer
Dim cmdAddSponsor As New OleDb.OleDbCommand()
Dim intPKID As Integer
Try
'Open DB
If OpenDatabaseConnectionSQLServer() = False Then
' No, warn the user ...
MessageBox.Show(Me, "Database connection error." & vbNewLine &
"The application will now close.",
Me.Text + " Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
' and close the form/application
Me.Close()
End If
'Text to call stored procedure
cmdAddSponsor.CommandText = "EXECUTE uspAddSponsor '" & intPKID & "','" & strFirstName & "','" & strLastName & "','" & strAddress & "','" & strCity & "','" & strState & "','" & strZip & "','" & strPhoneNumber & "','" & strEmail & "'"
cmdAddSponsor.CommandType = CommandType.StoredProcedure
'Call stored procedure which will insert the record
cmdAddSponsor = New OleDb.OleDbCommand(cmdAddSponsor.CommandText, m_conAdministrator)
'This return is the # of rows affected
intRowsAffected = cmdAddSponsor.ExecuteNonQuery()
'Close DB
CloseDatabaseConnection()
'Let user know what happened
If intRowsAffected > 0 Then
MessageBox.Show("Sponsor successfully added")
Else
MessageBox.Show("Sponsor not added. Error")
End If
Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Close()
End Try
End Sub

有什么想法吗？

Answer 1

根据官方文件

当在连接上设置SET NOCOUNT ON时(在执行命令之前或作为执行命令的一部分，或作为执行该命令启动的触发器的一部分(，受单个语句影响的行将停止对该方法返回的受影响行数做出贡献。

我建议使用SP的返回值来指示SP是否工作，因为这是它的预期用途，因为您实际上并没有将受影响的行数用于其他任何事情。