I am having a devil of a time getting an output value from SQL Server.
我有一段时间从SQL服务器获取输出值。
Here is my stored procedure:
以下是我的存储过程:
ALTER PROCEDURE [dbo].[Insert_UnknownCustomer_Quote_Document]
-- Add the parameters for the stored procedure here
@NewDocumentFileName nvarchar(100),
@NewDocumentWordCount int,
@NewQuoteAmount money,
@NewQuoteNumber int OUTPUT = 0
AS
DECLARE @Today datetime
SELECT @Today = GETDATE()
BEGIN TRANSACTION
BEGIN TRY
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO dbo.Customers(DateAdded)
VALUES (@Today)
INSERT INTO dbo.Quotes(CustomerID, QuoteAmount, QuoteDate)
VALUES (@@IDENTITY, @NewQuoteAmount, @Today)
SELECT @NewQuoteNumber = @@IDENTITY
INSERT INTO dbo.DocumentFiles(QuoteNumber, DocumentFileName, DocumentFileWordCount)
VALUES (@NewQuoteNumber, @NewDocumentFileName, @NewDocumentWordCount)
-- Return quote number
RETURN @NewQuoteNumber
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'Transaction rolled back.'
END CATCH
And here is my C#:
这是我的c#:
SqlParameter returnQuoteNumber = new SqlParameter("@NewQuoteNumber", SqlDbType.Int);
returnQuoteNumber.Direction = ParameterDirection.ReturnValue;
newSQLCommand.Parameters.Add(returnQuoteNumber);
Here is the error I am receiving now:
这是我现在收到的错误:
Procedure or function 'Insert_UnknownCustomer_Quote_Document' expects parameter '@NewQuoteNumber', which was not supplied.
I have tried taking @NewQuoteNumber out of the beginning and placing it after the AS with a DECLARE but that produces an error, too.
我试过把@NewQuoteNumber从开头去掉,然后在AS后面加上一个声明,但这也会产生错误。
3 个解决方案
#1
4
you want ParameterDirection.Output
not ParameterDirection.ReturnValue
你想要ParameterDirection。输出不ParameterDirection.ReturnValue
Also take it out of the return part, return should be used to return a status not a value
也从返回部分中取出它,返回应该用来返回一个状态而不是一个值
And if you do use return, I would do it after the transaction is committed not before
如果你用return的话,我会在事务提交之后,而不是之前
#2
1
SqlCommand.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection .Output ;
<SqlCommand>.ExecuteNonQuery();
int NewQuoteNumber = int.Parse(SqlCommand.Parameters["@NewQuoteNumber"].Value .ToString ());
now you can use this value into your code .
现在可以将这个值应用到代码中。
#3
0
this line:
这条线:
int NewQuoteNumber = int.Parse(SqlCommand.Parameters["@NewQuoteNumber"].Value .ToString ());<br />
should be:
应该是:
int NewQuoteNumber = int.Parse(SqlCommand.Parameters["NewQuoteNumber"].Value .ToString ());
#1
4
you want ParameterDirection.Output
not ParameterDirection.ReturnValue
你想要ParameterDirection。输出不ParameterDirection.ReturnValue
Also take it out of the return part, return should be used to return a status not a value
也从返回部分中取出它,返回应该用来返回一个状态而不是一个值
And if you do use return, I would do it after the transaction is committed not before
如果你用return的话,我会在事务提交之后,而不是之前
#2
1
SqlCommand.Parameters.Add("@NewQuoteNumber", SqlDbType.Int).Direction = ParameterDirection .Output ;
<SqlCommand>.ExecuteNonQuery();
int NewQuoteNumber = int.Parse(SqlCommand.Parameters["@NewQuoteNumber"].Value .ToString ());
now you can use this value into your code .
现在可以将这个值应用到代码中。
#3
0
this line:
这条线:
int NewQuoteNumber = int.Parse(SqlCommand.Parameters["@NewQuoteNumber"].Value .ToString ());<br />
should be:
应该是:
int NewQuoteNumber = int.Parse(SqlCommand.Parameters["NewQuoteNumber"].Value .ToString ());