Not able to insert new record by using SP

Question

User-1909855381 posted

Hello Asp.NET Team,

I am not able to insert a new record with getting run time error:  "Getting Error: “Error converting data type nvarchar to numeric""

UserRequest Model Class

--------------------------

 public class UserRequest
    {
        [Required]
        public decimal numRoleId { get; set; }
        [Required]
        public string vcFirstName { get; set; }
        [Required]
        public string vcLastName { get; set; }
        [Required]
        public string vcUserName { get; set; }
        [Required]
        public string vcEmail { get; set; }
        [Required]
        public string vcPassword { get; set; }
        [Required]
        public string vcMobile { get; set; }
        public string vcImageUrl { get; set; }
        [Required]
        public decimal? numCreatedBy { get; set; }
        [Required]
        public DateTime? dtmCreatedOn { get; set; }
        [Required]
        public decimal? numUpdatedBy { get; set; }
        [Required]
        public DateTime? dtmUpdatedOn { get; set; }

        public bool bitActive { get; set; }

    }

UserService Class

----------------------

        public long AddUser(UserRequest model)
        {
            try
            {
                var result = _context.Database.ExecuteSqlCommand("EXECUTE SP_UserRegistration @numRoleId,@vcFIrstName,@vcLastName,@vcMobile,@vcUserName,@vcPassword,@vcImageUrl,@numCreatedBy,@dtmCreatedOn,@numUpdatedBy,@dtmUpdatedOn,@bitActive",
           new SqlParameter("@numRoleId", model.numRoleId),
           new SqlParameter("@vcFirstName", model.vcFirstName),
           new SqlParameter("@vcLastName", model.vcLastName),
           new SqlParameter("@vcUserName", model.vcUserName),
           new SqlParameter("@vcPassword", model.vcPassword),
           new SqlParameter("@vcEmail", model.vcEmail),
           new SqlParameter("@vcMobile", model.vcMobile),
           new SqlParameter("@vcImageUrl", model.vcImageUrl),
           new SqlParameter("@numCreatedBy", model.numCreatedBy),
           new SqlParameter("@dtmCreatedOn", model.dtmCreatedOn),
           new SqlParameter("@numUpdatedBy", model.numUpdatedBy),
           new SqlParameter("@dtmUpdatedOn", model.dtmUpdatedOn),
           new SqlParameter("@bitActive", model.bitActive)
           );



                return result;
            }
            catch (Exception)
            {

                throw;
            }
        }

SQL Procedure

---------------

ALTER PROCEDURE [dbo].[SP_UserRegistration] 
@numUserId as numeric(18,0) = 0,
@numRoleId as numeric(18,0) = 0,
@vcFirstName as varchar(50) = NULL,
@vcLastName as varchar(50) = NULL,
@vcUserName as varchar(20) = NULL,
@vcPassword as varchar(50)= NULL,
@vcEmail as varchar(50) = NULL,
@vcMobile as varchar(50) = NULL,
@vcImageUrl	as varchar(200) = NULL,
@numCreatedBy as numeric(18,0) = NULL,
@dtmCreatedOn as datetime = NULL,
@numUpdatedBy as numeric(18,0) = NULL,
@dtmUpdatedOn as datetime NULL,
@bitActive	bit,
As
Begin

//Query 
End

Please help how to fix that issue .

Thank you

Answer 1

User1120430333 posted
A nvarchar data can't be numeric data. It has to be converted over to be numeric. Do you even know what data or parm is causing the problem?

Answer 2

User-1909855381 posted

Which param data need to be converted to numeric.

Answer 3

User475983607 posted

Which param data need to be converted to numeric.

That's up to you to figure out.  Unfortunately, you did not share all the information.  I assume there is a bug in the stored procedure.  Perhaps the values do not line up with correct column or you you've got some other TSQL code that cases the issue.

Honestly, it's a lot easier for you to find the bug since you can reproduce the error and you have all the code.

Answer 4

User1120430333 posted
I would assume that the error message would have given who was the culprit that caused the error.

Answer 5

User-1909855381 posted

Here is the procedure.

ALTER PROCEDURE [dbo].[SP_UserRegistration] 
@numUserId as numeric(18,0) = 0,
@numRoleId as numeric(18,0) = 0,
@vcFirstName as varchar(50) = '',
@vcLastName as varchar(50) = '',
@vcUserName as varchar(20) = '',
@vcPassword as varchar(50)= '',
@vcEmail as varchar(50) = '',
@vcMobile as varchar(50) = '',
@vcImageUrl	as varchar(200) = '',
@numCreatedBy as numeric(18,0) = 0,
@dtmCreatedOn as datetime = NULL,
@numUpdatedBy as numeric(18,0) = 0,
@dtmUpdatedOn as datetime =NULL,
@bitActive	bit = 0,
@VC_OUTPUT AS VARCHAR(300)= NULL OUTPUT
AS
BEGIN
	
		IF(@numUserId = 0)
		BEGIN
			IF((SELECT COUNT(vcUserName) FROM tblUser WHERE vcUserName = @vcUserName AND bitActive= @bitActive) >0)
			BEGIN 
			 --Data Not Exist
				SET @VC_OUTPUT='EXIST'
			END
			ELSE
			BEGIN
			INSERT INTO tblUser( 
						numRoleId,
						vcFirstName,
						vcLastName,
						vcUserName,
						vcPassword,
						vcEmail,
						vcMobile,
						vcImageUrl,
						numCreatedBy,
						dtmCreatedOn,
						numUpdatedBy,
						dtmUpdatedOn,
						bitActive)

			VALUES(
						@numRoleId,
						@vcFirstName,
						@vcLastName,
						@vcUserName,
						@vcPassword,
						@vcEmail,
						@vcMobile,
						@vcImageUrl,
						@numCreatedBy,
						@dtmCreatedOn,
						@numUpdatedBy,
						@dtmUpdatedOn,
						@bitActive)
     SET @VC_OUTPUT='ADDED'
			END
		END
END

Answer 6

User475983607 posted

You have to realize that you did not provide the most important information... the table schema. 

Anyway, learn how to debug code.  You are in the best position to find the bug.  I would start by testing the stored procedure in SSMS to try to reproduce the issue.  If you want community debugging assistance then you'll need to provide enough sample code so a forum member can run the code and produce the same results.   We need the DDL.

Answer 7

User-474980206 posted

we also need the table schema to compare the schema datatypes to sp datatypes. as you defaulted all the values, test:

exec SP_UserRegistration

 it should add a row with the defaults

note: its a bad practice to prefix an stored procedure name with sp_ as this has special meaning (system proc)

Answer 8

User-1909855381 posted

Thank you for the suggestion , I will avoid the prefix sp_. can I use sp with the name of the procedure like spInsertUser .

this is my table schema

Answer 9

User475983607 posted

The code has a questionable design.  There is a @numUserId parameter that is never sent to the proc but default to 0.  There is also a numUerId "NOT NULL" field in the table.  Unfortunately you still have not provide the DDL.   Is numUserId an Identity column?  Are you trying to craft an INSERT/UPDATE stored procedure?   

I'm wondering if the code shown is incomplete or not producing the error.  

Answer 10

User-1909855381 posted

Yes, I am trying to create a store procedure that can be performed insert/update operation, and numUser is an identity column.

when i try to execute that SP in SQL server side it is working fine means i can able to insert the record and i am using executesqlcommand() method to perform row SQL that time am getting run time error i'e Error converting data type nvarchar to numeric.

Answer 11

User-474980206 posted

simple coding error. you pass the sp parameters by position (a very bad practice), and drop the first parameter

"EXECUTE SP_UserRegistration @numRoleId,@vcFIrstName,@vcLastName,@vcMobile,@vcUserName,@vcPassword,@vcImageUrl,@numCreatedBy,@dtmCreatedOn,@numUpdatedBy,@dtmUpdatedOn,@bitActive"

but the proc parameter list is

ALTER PROCEDURE [dbo].[SP_UserRegistration] 
@numUserId as numeric(18,0) = 0,
@numRoleId as numeric(18,0) = 0,
@vcFirstName as varchar(50) = NULL,
@vcLastName as varchar(50) = NULL,
@vcUserName as varchar(20) = NULL,
@vcPassword as varchar(50)= NULL,
@vcEmail as varchar(50) = NULL,
@vcMobile as varchar(50) = NULL,
@vcImageUrl	as varchar(200) = NULL,
@numCreatedBy as numeric(18,0) = NULL,
@dtmCreatedOn as datetime = NULL,
@numUpdatedBy as numeric(18,0) = NULL,
@dtmUpdatedOn as datetime NULL,
@bitActive	bit,

so @vcFirstName value is used for the @numRoleId parameter value. and so on.