执行非查询返回值
我想在表上执行搜索以查看记录是否存在。我不想在之后执行插入或更新。我已经做到了这一点，但不知何故我无法让它发挥作用。在我的 asp.net 页面上，我似乎无法获得任何返回值。错误是“输入字符串格式不正确”我确信这是显而易见的，但我现在似乎看不到它！
这是我的代码：
Dim con As New SqlConnection("connstring")
Dim cmd As New SqlCommand("checkname", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@d", SqlDbType.Int))
cmd.Parameters("@id").Value = TextBox1.Text
Dim para As New SqlParameter
para.Direction = ParameterDirection.ReturnValue
para.ParameterName = "returnvalue"
cmd.Parameters.Add(para)
con.Open()
cmd.ExecuteNonQuery()
Dim exists As Integer
exists = Convert.ToInt32(cmd.Parameters("returnvalue").Value)
If exists = 1 Then
Label1.Text = "You......"
ElseIf exists = 0 Then
Label1.Text = "You....."
End If
con.Close()
存储过程：
CREATE PROCEDURE checkname
-- Add the parameters for the stored procedure here
@id int
AS
--This means it exists, return it to ASP and tell us
-- SELECT 'already exists'
IF EXISTS(SELECT * FROM attendees WHERE id = @id)
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
I want to perform a search on a table to see if record exists. I do not want to perform insert or update after. I have done this already but somehow I cannot get this to work. On my asp.net page I cannot seem to get any value returned. The error is "input string not in correct format" I ma sure it is obvious but I cannot seem to see it now!
here is my code:
Dim con As New SqlConnection("connstring")
Dim cmd As New SqlCommand("checkname", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@d", SqlDbType.Int))
cmd.Parameters("@id").Value = TextBox1.Text
Dim para As New SqlParameter
para.Direction = ParameterDirection.ReturnValue
para.ParameterName = "returnvalue"
cmd.Parameters.Add(para)
con.Open()
cmd.ExecuteNonQuery()
Dim exists As Integer
exists = Convert.ToInt32(cmd.Parameters("returnvalue").Value)
If exists = 1 Then
Label1.Text = "You......"
ElseIf exists = 0 Then
Label1.Text = "You....."
End If
con.Close()
stored procedure:
CREATE PROCEDURE checkname
-- Add the parameters for the stored procedure here
@id int
AS
--This means it exists, return it to ASP and tell us
-- SELECT 'already exists'
IF EXISTS(SELECT * FROM attendees WHERE id = @id)
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您需要确保传递的是整数。
（从技术上讲，当
在 .NET 中定义参数
代码。）
You need to ensure that you are passing an integer.
(Technically you don't need the "@" character when
defining the parameters in the .NET
code.)
您已将过程参数声明为 @d 而不是 @id。此外，返回参数不能是输入参数。返回值应该是退出代码。您很可能想要创建一个输出参数并在存储过程中将其设置为 1 或零。
编辑：澄清一下，返回值通常被视为正确执行的指标。零通常意味着成功，任何其他数值通常被视为错误代码。这就是为什么我建议添加输出参数而不是添加返回值参数。
You have declared your procedure parameter as @d instead of @id. Also a return parameter cannot be an input parameter. The return value should be an exit code. You most likely want to create an output parameter and set that to 1 or zero inside of your stored procedure.
Edit: to clarify, the return value is generally regarded as an indicator of correct execution. Zero usually means success, where any other numeric value is generally regarded as an error code. That is why I recommended adding an output parameter instead of adding a return value parameter.
ExecuteNonQuery返回受影响的行数。因此，您在存储过程中设置的返回值将被丢弃，并且ExecuteNonQuery方法不会返回。ExecuteNonQueryreturns the number of rows affected. Therefore the return values that you set in your stored procedure are thrown away and will not be returned by theExecuteNonQuerymethod.ExecuteNonQuery 用于插入/删除/更新操作。不适用于 SELECT，您需要 ExecuteScalar 或 ExecuteReader 方法。此链接将帮助您了解如何使用输出参数：http://aspdotnet-suresh.blogspot.com/2010/10/introduction-here-i-will-explain-how-to.html
ExecuteNonQuery is used to Insert / Delete / Update operations. Not for SELECT, you need either ExecuteScalar or ExecuteReader methods. This link will help you to know how to use output parameters : http://aspdotnet-suresh.blogspot.com/2010/10/introduction-here-i-will-explain-how-to.html