Conversion error when converting varchar value "Blue" to int data type

Question

2

Hello! I am trying to create a stored procedure that returned a varchar value, and that is the value I need to display in the text box.

This is the stored procedure code:

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 the return value from the procedure in a text box:

 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 receive this message with the exception of:

"Conversion error when converting varchar" Blue color "to int data type."

What am I doing wrong? Thanks!

Answer 1

RETURN accepts an integer argument, so return @Name calls it

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

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

Although, it can be written in the same way, because @id_doc = doc.IDN = Items.ID :

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

If you want to use the OUTPUT parameter rather than the recordset, just delete the RETURN statement and leave the @Name destination

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

Answer 2

I think you pass "Blue" as the parameter value to id_doc .
And the call to the stored procedure may fail, because it takes int as the first input parameter