从存储过程获取结果然后传递给WPF文本框(Get result from Stored Procedure then pass to WPF TextBox)

从存储过程获取结果然后传递给WPF文本框(Get result from Stored Procedure then pass to WPF TextBox)

我有这个存储过程执行得很好：

ALTER PROCEDURE [dbo].[SP_GENERATETICKET] @RESULT AS VARCHAR(255) OUTPUT AS BEGIN DECLARE @D AS DATETIME = GETDATE() DECLARE @LASTTICKET AS VARCHAR(50) ; DECLARE @NUM AS VARCHAR(50); SET @LASTTICKET=(SELECT TOP 1 TICKETNO FROM tblTicket WHERE MONTH(ENTRYVEHICLE ) = MONTH(@D ) ORDER BY TICKETNO DESC); IF (CONVERT(VARCHAR(10),YEAR(@D),110) <> CONVERT(VARCHAR(10),YEAR(GETDATE()),110))--IF YEAR IS DIFFERENT, RESET SERIES BEGIN SET @NUM = '1' END ELSE BEGIN IF ISNULL(@LASTTICKET,'1') = '1'--IF MONTH IS DIFFERENT, RESET SERIES BEGIN SET @NUM ='1' END ELSE BEGIN SET @NUM = CAST(CAST (RIGHT(@LASTTICKET,5) AS INT) + 1 AS VARCHAR) END END SET @RESULT = RIGHT(CONVERT(VARCHAR(10),@D,101),2) + '-' + LEFT(CONVERT(VARCHAR(10),@D,101),2) + '-' + RIGHT('0000'+CAST(@NUM AS VARCHAR),5) SELECT @RESULT END

我有这个代码，但它返回null

SqlConnection con = new SqlConnection(gsql.connectionString); SqlCommand command = new SqlCommand("SP_GENERATETICKET", con); try { con.Open(); command.CommandType = CommandType.StoredProcedure; SqlParameter result = command.Parameters.Add("@RESULT", SqlDbType.VarChar); result.Direction = ParameterDirection.Output; txtTicket.Text = (string)command.Parameters["@RESULT"].Value; } catch(Exception ex) { MessageBox.Show(ex.Message); }

我该怎么办？

I have this stored procedure that executes fine:

ALTER PROCEDURE [dbo].[SP_GENERATETICKET] @RESULT AS VARCHAR(255) OUTPUT AS BEGIN DECLARE @D AS DATETIME = GETDATE() DECLARE @LASTTICKET AS VARCHAR(50) ; DECLARE @NUM AS VARCHAR(50); SET @LASTTICKET=(SELECT TOP 1 TICKETNO FROM tblTicket WHERE MONTH(ENTRYVEHICLE ) = MONTH(@D ) ORDER BY TICKETNO DESC); IF (CONVERT(VARCHAR(10),YEAR(@D),110) <> CONVERT(VARCHAR(10),YEAR(GETDATE()),110))--IF YEAR IS DIFFERENT, RESET SERIES BEGIN SET @NUM = '1' END ELSE BEGIN IF ISNULL(@LASTTICKET,'1') = '1'--IF MONTH IS DIFFERENT, RESET SERIES BEGIN SET @NUM ='1' END ELSE BEGIN SET @NUM = CAST(CAST (RIGHT(@LASTTICKET,5) AS INT) + 1 AS VARCHAR) END END SET @RESULT = RIGHT(CONVERT(VARCHAR(10),@D,101),2) + '-' + LEFT(CONVERT(VARCHAR(10),@D,101),2) + '-' + RIGHT('0000'+CAST(@NUM AS VARCHAR),5) SELECT @RESULT END

I have this Code but it returns null

SqlConnection con = new SqlConnection(gsql.connectionString); SqlCommand command = new SqlCommand("SP_GENERATETICKET", con); try { con.Open(); command.CommandType = CommandType.StoredProcedure; SqlParameter result = command.Parameters.Add("@RESULT", SqlDbType.VarChar); result.Direction = ParameterDirection.Output; txtTicket.Text = (string)command.Parameters["@RESULT"].Value; } catch(Exception ex) { MessageBox.Show(ex.Message); }

What should I do?

最满意答案

有问题，你忘了执行命令

command.ExecuteNonQuery()

不执行命令你想要返回结果。

你也忘了为你的varchar参数添加大小，所以添加东西线

result.Size = 8000;

在下面的代码中使用udpated

---

你的代码将会是，你需要使用using来为你处理事情

using(SqlConnection con = new SqlConnection(gsql.connectionString)) { using(SqlCommand command = new SqlCommand("SP_GENERATETICKET", con)) { try { con.Open(); command.CommandType = CommandType.StoredProcedure; SqlParameter result = command.Parameters.Add("@RESULT", SqlDbType.VarChar); result.Size = 8000;//add parameter size result.Direction = ParameterDirection.Output; command.ExecuteNonQuery();//this line need to be added txtTicket.Text = (string)command.Parameters["@RESULT"].Value; } catch(Exception ex) { MessageBox.Show(ex.Message); } } }

there problem is there , you forgot to execute command

command.ExecuteNonQuery()

without executing command you want get result back.

also you forgot to add size for you varchar paramter , so add thing line

result.Size = 8000;

udpated in below code

---

you code will be , you need to use using to dispose things for you

using(SqlConnection con = new SqlConnection(gsql.connectionString)) { using(SqlCommand command = new SqlCommand("SP_GENERATETICKET", con)) { try { con.Open(); command.CommandType = CommandType.StoredProcedure; SqlParameter result = command.Parameters.Add("@RESULT", SqlDbType.VarChar); result.Size = 8000;//add parameter size result.Direction = ParameterDirection.Output; command.ExecuteNonQuery();//this line need to be added txtTicket.Text = (string)command.Parameters["@RESULT"].Value; } catch(Exception ex) { MessageBox.Show(ex.Message); } } }