Thursday, May 30, 2013

Error Converting Data Type Int to Nvarchar

Error: Error converting data type int to nvarchar

This error occurs when a stored procedure which returns a value is used.

e.g
CREATE PROCEDURE [dbo].[spr_SP1]
@ColA INT,
@AutoID INT OUTPUT
AS
BEGIN
 INSERT INTO TableA ( ColA )
 VALUES ( @ColA )

 SELECT @AutoID = SCOPE_IDENTITY();
END

When adding the parameter to the command on the code, usually the code used is:
command.Parameters.AddWithValue("@AutoID", System.DBNull.Value)
command.Parameters("@AutoID").Size = 4
command.Parameters("@AutoID").Direction = ParameterDirection.Output

However, this portion of code would possibly cause error during deployment. To avoid the error, use the code below instead:

Dim param = New System.Data.SqlClient.SqlParameter()
param.ParameterName = "@AutoID"
param.Direction = ParameterDirection.Output
param.Size = 8
command.Parameters.Add(param)
Share:

2 comments:

  1. Great article, Thanks for your great information, the content is quiet interesting. I will be waiting for your next post.

    ReplyDelete
  2. I am extremely impressed along with your writing abilities and also with the format in your blog. Anyway stay up to the excellent high quality writing, it's rare to find a nice weblog like this one these days.

    ReplyDelete

About

Related Posts

Tableau Server Unable to Start due to Blocked Ports

Problem: Sometimes you might need to use different (from the default port) port on Tableau. By default, SSL is using port 443. For my case...

About Me

My photo
Is an ordinary unNoticed man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing. I like English and Chinese language, and know a little about them. Hence, for me, most things can best be expressed in Chinese, some in English, and the rest in others ^^

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Follow by Email