将varchar值'Blue color'转换为数据类型int时转换失败

Hi! I am trying to create stored procedure that gone return varchar value, and that value I need to display in textbox.

嗨！我正在尝试创建返回varchar值的存储过程，以及我需要在文本框中显示的值。

This is the code for stored procedure:

这是存储过程的代码：

Create PROCEDURE Status @id_doc int, @Name varchar(50) OUTPUT
AS
select  @Name =items.name    
from Doc,Items where @id_doc=IDN and doc.IDN=Items.ID 
return @Name

This is the code in vb.net where i need to display returned value from procedure in textbox:

这是vb.net中的代码，我需要在textbox中显示来自procedure的返回值：

Public Sub Current()
    Dim dtc As New Data.DataTable
    Dim dr As SqlClient.SqlDataReader
    Dim da As New SqlClient.SqlDataAdapter
    Dim cmd As New SqlClient.SqlCommand
    Dim id_doc As Integer
    Dim dset As New DataSet
    Dim recordset As DataRow
    Dim Name As String

    Try
        id_doc = idDocExplorer
        cmd.Connection = pubCon
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Status"
        cmd.Parameters.Add("@id_doc", id_doc)
        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50)
        cmd.Parameters("@Name ").Direction = ParameterDirection.Output
        cmd.ExecuteScalar()

        Name = cmd.Parameters("@Name").Value
       TextBox1.text=Name
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        dtc = Nothing
        dr = Nothing
        da = Nothing
    End Try
End Sub

When I try to execute this code I get this message in exception:

当我尝试执行此代码时，我在异常中收到此消息：

"Conversion failed when converting the varchar value 'Blue color' to data type int."

“将varchar值'蓝色'转换为数据类型int时转换失败。”

What I am doing wrong? Thanks!

我做错了什么？谢谢！

2 个解决方案

#1

RETURN takes an integer argument only so return @Name is causing it

RETURN只接受一个整数参数，因此返回@Name会导致它

Use this instead to return a record set (with a proper JOIN too)

使用它来代替返回一个记录集（也有一个正确的JOIN）

select
    items.name    
from
    Doc
    JOIN
    Items ON doc.IDN = Items.ID 
where
    @id_doc = IDN

Although, it can be written like this too because @id_doc = doc.IDN = Items.ID:

虽然，它也可以这样写，因为@id_doc = doc.IDN = Items.ID：

select
    items.name    
from
    Items
where
    Items.ID = @id_doc

If you want to use an OUTPUT parameter rather than a recordset, then simply remove the RETURN statement and leave the assign to @Name

如果要使用OUTPUT参数而不是记录集，则只需删除RETURN语句并将赋值保留为@Name

ALTER PROCEDURE Status @id_doc int, @Name varchar(50) OUTPUT
AS
select @Name = items.name from Items where Items.ID = @id_doc
GO

#2

-1

I think you are passing "Blue Color" as a parameter value under id_doc.
And the stored procedure call could be failing because it accepts an int type as 1st input parameter

我认为你将“蓝色”作为id_doc下的参数值传递。并且存储过程调用可能失败，因为它接受int类型作为第一个输入参数

#1