I'm trying to get a string (nvarchar) from a stored procedure in my VB.net program. When I run the SP in SQL Studio, it's fine, but when I try to run it in the program I get the error:
Error converting data type nvarchar to int.
Here's my SP:
ALTER PROCEDURE cusVerifyAssignment
@assignment_number int,
@compass_or_anodes char(1),
@return nvarchar(1000) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @compass_or_anodes = 'C'
BEGIN
SET @return =
(SELECT CONVERT(nvarchar(1000), p.display_name + '|' + ISNULL(xro.release_type, '') + '|' + a.comments + '|' + a.assignment_id) as blah
FROM Workplace_base.dbo.people p
INNER JOIN Workplace_base.dbo.assignments a ON p.people_id = a.people_id
LEFT OUTER JOIN Workplace_base.dbo.xro_assignment_release_types xro ON a.release_type_id = xro.release_type_id
WHERE assignment_num = @assignment_number)
END
ELSE
BEGIN
SET @return =
(SELECT CONVERT(nvarchar(1000), p.display_name + '|' + ISNULL(xro.release_type, '') + '|' + a.comments + '|' + a.assignment_id) as blah
FROM Anodes_Compass.dbo.people p
INNER JOIN Anodes_Compass.dbo.assignments a ON p.people_id = a.people_id
LEFT OUTER JOIN Anodes_Compass.dbo.xro_assignment_release_types xro ON a.release_type_id = xro.release_type_id
WHERE assignment_num = @assignment_number)
END
END
GO
Here's my vb code:
Dim paramNumber As New OleDbParameter("@assignment_number", SqlDbType.Int)
Dim paramDB As New OleDbParameter("@compass_or_anodes", SqlDbType.Char)
Dim paramReturn As New OleDbParameter("@return", SqlDbType.NVarChar)
Dim reader As OleDbDataReader
paramNumber.Value = assignmentNumber
paramDB.Value = "C"
paramReturn.Direction = ParameterDirection.Output
cmd = New OleDbCommand("cusVerifyAssignment", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(paramNumber)
cmd.Parameters.Add(paramDB)
cmd.Parameters.Add(paramReturn)
con.Open()
reader = cmd.ExecuteReader()
con.Close()
Return reader.GetString(0)
I've tried using executeNonQuery too, but that got me the same thing. Can anyone offer any suggestins?
Thanks
