SQl error - Syntax error converting the nvarchar value ....

[jaimalchohan]

Error:

Syntax error converting the nvarchar value 'Jaimal Chohan' to a column of data type int.

SQL Stored Proc:

CREATE PROCEDURE proc_get_full_name
(
@user_id bigint
)
AS
Declare @forename nVarChar(50)
Declare @surname nVarChar(50)
Declare @fullname nVarChar(101)

SELECT @forename = forename, @surname = surname FROM tbl_users WHERE user_id = @user_id

SET @fullname = @forename + ' ' + @surname

RETURN @fullname

asp.net/vb.net Code

conGetName = New SqlConnection(ConfigurationManager.AppSettings("co nnectionString"))

cmdGetName = New SqlCommand("proc_get_full_name", conGetName)
cmdGetName.CommandType = CommandType.StoredProcedure
cmdGetName.Parameters.Add("@user_id", user_id)

paramReturnvalue = cmdGetName.Parameters.Add("RETURN_VALUE", SqlDbType.nvarchar)
paramReturnvalue.Direction = ParameterDirection.ReturnValue

conGetName.Open()
cmdGetName.ExecuteNonQuery()
fullname = cmdGetName.Parameters("RETURN_VALUE").Value
conGetName.Close()

Response.Write(fullname)

Hmm, I am really confused as to this, even trying to just return @forename returns the same error. Its a pretty simple stored proc, hmmm.

[Staff: Warren Ashcroft]

Try usng a CAST or CONVERT on:

SET @fullname = @forename + ' ' + @surname

[jaimalchohan]

Well, I tried it like this

SET @fullname = CAST(@forename As nVarChar(50)) + CAST(' ' AS nVarChar(1)) + CAST(@surname AS nvarchar(50))

never had to use CAST or CONVERT before

anyway, it didn;t work, still returning the exact same error

Also tried

RETURN CAST(@forename + ' ' + @surname AS nVarchar(101))

with the same error being returned.

Line 59: conGetName.Open()
Line 60: cmdGetName.ExecuteNonQuery()
Line 61: fullname = cmdGetName.Parameters("RETURN_VALUE").Value
Line 62: conGetName.Close()

Ill probably have to get rid of the storedProc becuase its stopping development

[Staff: Warren Ashcroft]

I think it has something to do with the SPROC not knowing what type of data it will be returning...

What you are doing would be more suited inside a User Defined Function...

[jaimalchohan]

I think it has something to do with the SPROC not knowing what type of data it will be returning...

What you are doing would be more suited inside a User Defined Function...

LOL, I can;t belive that I am such an idiot

Rule #1 - look in a book, I didn't

Anyway, thanks to asp.net Unleashed by Sams (which I most definalty going to call the only guide to asp.net worth buying) I found out the Return only returns an Integer type, and also that I learned how to use Output parameters correctly (which I tried sometime last week, unsucessfully, following a 'guide' on the net)

Thanks for the help Warren!

[RFH Reseller: Sol]

It's a shame I just read this, as I spotted that mistake. Anyway, I guess you are ok now that you

[jaimalchohan]

It's a shame I just read this, as I spotted that mistake. Anyway, I guess you are ok now that you

Hehe, well you only learn though mistakes

[Staff: Warren Ashcroft]

No problem

[RFH Customer: Jon Freeman]

Out of my league, not having been a .net user but one thing I remain glad of was the first computer lanuguage that made any sense at all to me (unless spaghetti on line number basic) was Borland's Turbo Pascal. It was quite strict with data types so that was one thing I think I learned. I remain an amatuer/hobbiest/try my best but think it forced me into learning some things.

[jaimalchohan]

Out of my league, not having been a .net user but one thing I remain glad of was the first computer lanuguage that made any sense at all to me (unless spaghetti on line number basic) was Borland's Turbo Pascal. It was quite strict with data types so that was one thing I think I learned. I remain an amatuer/hobbiest/try my best but think it forced me into learning some things.

I was pretty good with Pascal, back in 2000 did my A'Level project as a payroll system, but then I went to Uni. They tried to teach me Java based OOP using some pathetic program called BlueJ, I just got bored with it (and the rest of Uni for that matter, I mean c'mon who really needs to know about probablity, linear algrbra, Chernobyl happened, how to describe a diamond etc etc etc). So I quite Uni (after getting a HND) and started to Teach My Self