locked
ExecuteSqlRaw Error in version 3.1 , The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects. RRS feed

  • Question

  • User-1355965324 posted

    When I use the ExecuteSqlRaw method to  insert the record into sqlserver table , not working in . targetframe work 3.1.  But it is working in version 2.0  using  ExecuteSqlCommand, Please can you advise me how can I use ExecuteSqlRaw   in the version 3.1 to insert the record in a user table after passing argument

    public int Update(long id,GoUserModel user)
            {
                int userId = 0;
                if(user.UserPassword == null)
                {
                    user.UserPassword= "";
                }
                var param = new SqlParameter[] { };
                try
                {
    
                    param = new SqlParameter[] {
                            new SqlParameter() {
                                ParameterName = "@UserID",
                                SqlDbType =  System.Data.SqlDbType.Int,
                                Size = 100,
                                Direction = System.Data.ParameterDirection.InputOutput,
                                Value = id
                            },
                            new SqlParameter() {
                                ParameterName = "@DisplayName",
                                SqlDbType =  System.Data.SqlDbType.NVarChar,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = user.DisplayName
                            },
                            new SqlParameter() {
                                ParameterName = "@UserName",
                                SqlDbType =  System.Data.SqlDbType.VarChar,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = user.UserName
                            },
                            new SqlParameter() {
                                ParameterName = "@Password",
                                SqlDbType =  System.Data.SqlDbType.VarChar,
                                Size=user.UserPassword.Length,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = user.UserPassword.Trim()
                            }  ,
                            new SqlParameter() {
                                ParameterName = "@RoleID",
                                SqlDbType =  System.Data.SqlDbType.Int,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = user.RoleID
                            } ,
                            new SqlParameter() {
                                ParameterName = "@Email",
                                SqlDbType =  System.Data.SqlDbType.VarChar,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = user.Email
                            } ,                                     
                           
                       
                        new SqlParameter() {
                                ParameterName = "@IsActive",
                                SqlDbType =  System.Data.SqlDbType.VarChar,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = user.IsActive
                            } ,
                        new SqlParameter() {
                                ParameterName = "@CreatedBy",
                                SqlDbType =  System.Data.SqlDbType.VarChar,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = user.CreatedBy
                            } ,
                            new SqlParameter() {
                                ParameterName = "@InsertUpdateMode",
                                SqlDbType =  System.Data.SqlDbType.Int,
                                Direction = System.Data.ParameterDirection.Input,
                                Value = 1
                            }
    
                    };
                    
                  
                    userId = dbContext.Database.ExecuteSqlRaw("dbo.InsertUpdateUserDetails @UserID OUTPUT, " +
                        "@DisplayName," +
                        "@UserName," +
                        "@Password," +
                        "@RoleID," +
                        "@Email," +                   
                        "@IsActive," +
                        "@CreatedBy," +
                        "@InsertUpdateMode",
    
                        param);
                }
                catch (Exception ex)
                {
    
                }
             return Convert.ToInt32(param[0].Value);
            }  
    
    
    
    
    

    My stored procedure is given below

    ALTER  PROCEDURE [dbo].[InsertUpdateUserDetails](
    @UserID INT OUTPUT
      ,@DisplayName nvarchar(max)
               ,@UserName nvarchar(50)
               ,@Password varchar(50)
               ,@RoleID int
               ,@Email  nvarchar(max)  
               ,@IsActive bit
               ,@CreatedBy int
               ,@InsertUpdateMode INT
       )
    AS
    BEGIN
    
    SET NOCOUNT ON;
    if (@Password = '')
    begin
    	SET @Password = NULL;
    end
    
        IF(@InsertUpdateMode = 0)  -- 1 -INSERT
        BEGIN
        
    INSERT INTO [dbo].[User]
               (DisplayName
      ,[email]
               ,[UserName]
               ,[Password]
               ,[RoleID]
    
               ,[IsActive]
               ,[CreatedBy]
               ,[CreatedDateAndTime])
         VALUES
               (@DisplayName
      ,''
      ,''
      ,@Email
               ,@UserName
      , HASHBYTES('MD5',LTRIM(RTRIM(@Password)))        
               ,@RoleID  
               ,@IsActive 
               ,@CreatedBy
               ,GETDATE())
           SET @UserID = SCOPE_IDENTITY();
        END    
      
          
    END

    Saturday, January 2, 2021 9:35 AM

Answers

  • User1686398519 posted

    Hi polachan, 

    You need to change using System.Data.SqlClient to using Microsoft.Data.SqlClient.

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 4, 2021 6:54 AM