Failed to convert parameter value from a String to a Boolean.

Abdu_Allah
Abdu_Allah used Ask the Experts™
on
Hi, When I try to execute sql procedure I got error says: "Failed to convert parameter value from a String to a Boolean."

I debuged and the boolean values that I send is in the right format.

the following is my code and its stored procedure:
arParms[0] = new SqlParameter("@Index", SqlDbType.NVarChar);
            arParms[1] = new SqlParameter("@Active", SqlDbType.Bit);
            arParms[2] = new SqlParameter("@Description", SqlDbType.NVarChar);
            arParms[3] = new SqlParameter("@Licensed", SqlDbType.Bit);
            arParms[4] = new SqlParameter("@Manufacturer_ID", SqlDbType.NVarChar);
            arParms[5] = new SqlParameter("@Name", SqlDbType.NVarChar);
            arParms[6] = new SqlParameter("@Procurement_ID", SqlDbType.NVarChar);
            arParms[7] = new SqlParameter("@Serial_number", SqlDbType.NVarChar);
            arParms[8] = new SqlParameter("@Supported", SqlDbType.Bit);
            arParms[9] = new SqlParameter("@Tag", SqlDbType.NVarChar);
            arParms[10] = new SqlParameter("@ID", SqlDbType.NVarChar);
            arParms[10].Direction = ParameterDirection.InputOutput;

            string[] arrStrValues = new string[11];
            arrStrValues[0] = Index;
            arrStrValues[1] = Active.ToString();
            arrStrValues[2] = Description;
            arrStrValues[3] = Licensed.ToString();
            arrStrValues[4] = new Guid().ToString();//Manufacturer_ID;
            arrStrValues[5] = Name;
            arrStrValues[6] = new Guid().ToString();//Procurment_ID;
            arrStrValues[7] = Serial_number;
            arrStrValues[8] = Supported.ToString();
            arrStrValues[9] = Tag;
            arrStrValues[10] = ID;


******The stored procedure:
ALTER PROCEDURE [dbo].[USP_SaveSoftware] 
	-- Add the parameters for the stored procedure here
           @Index nvarchar(200)
           ,@Name  nvarchar(50)
           ,@Tag  nvarchar(50)
           ,@Description   nvarchar(350)
           ,@Manufacturer_ID nvarchar(200)
           ,@Serial_Number nvarchar(50)
           ,@Procurement_ID nvarchar(200)
           ,@Supported bit
           ,@Licensed bit
           ,@Active bit
           ,@ID nvarchar(200) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT @ID = newid();
    
    INSERT INTO [Element].[dbo].[Software]
           ([ID]
           ,[Index]
           ,[Name]
           ,[Tag]
           ,[Description]
           ,[Manufacturer_ID]
           ,[Serial_Number]
           ,[Procurement_ID]
           ,[Supported]
           ,[Licensed]
           ,[Active])
     VALUES
           (@ID
           ,@Index
           ,@Name
           ,@Tag
           ,@Description
           ,Cast(@Manufacturer_ID AS uniqueidentifier)
           ,@Serial_Number
           ,Cast(@Procurement_ID AS uniqueidentifier)
           ,@Supported
           ,@Licensed
           ,@Active)
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
could you let me know the structure of table too

Author

Commented:
Here is the script of the table:
 

USE [Element]
GO

/****** Object:  Table [dbo].[Software]    Script Date: 01/31/2010 07:11:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Software](
	[ID] [uniqueidentifier] NOT NULL,
	[Index] [nvarchar](10) NULL,
	[Name] [nvarchar](50) NULL,
	[Tag] [nvarchar](50) NULL,
	[Description] [nvarchar](350) NULL,
	[Manufacturer_ID] [uniqueidentifier] NULL,
	[Serial_Number] [nvarchar](50) NULL,
	[Procurement_ID] [uniqueidentifier] NULL,
	[Supported] [bit] NOT NULL,
	[Licensed] [bit] NOT NULL,
	[Active] [bit] NOT NULL,
 CONSTRAINT [PK_Software] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Software] ADD  CONSTRAINT [DF_Software_Supported]  DEFAULT ((0)) FOR [Supported]
GO

ALTER TABLE [dbo].[Software] ADD  CONSTRAINT [DF_Software_Licensed]  DEFAULT ((0)) FOR [Licensed]
GO

ALTER TABLE [dbo].[Software] ADD  CONSTRAINT [DF_Software_Active]  DEFAULT ((0)) FOR [Active]
GO

Open in new window

Reza RadConsultant, Trainer

Commented:
problem is not between the table and storedprocedure,
upload whole your code consist of execute stored procedure here, i think the problem is there
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Author

Commented:
Ok, here is how I execute the stored procedure:

AssignParameterValues(arParms, arrStrValues);
Base.ExecuteNonQuery(ConnectionString, "USP_SaveSoftware", arParms);
 
The following is the body of AssignParameterValues function.

 private void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                // Do nothing if we get no data
                return;
            }

            // We must have the same number of values as we pave parameters to put them in
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }

            // Iterate through the SqlParameters, assigning the values from the corresponding position in the 
            // value array
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {

                // If the current array value derives from IDbDataParameter, then assign its Value property
                if (parameterValues[i] is IDbDataParameter)
                {
                    IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                    if (paramInstance.Value == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {

                        commandParameters[i].Value = paramInstance.Value;
                    }
                }
                else if (parameterValues[i] == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = parameterValues[i];
                }
            }
        }

Open in new window

Reza RadConsultant, Trainer

Commented:
OK, so problem is in : AssignParameterValues(arParms, arrStrValues);
you assign a string array to parameter values,
so you will get error when you try to assign string value for your bit columns

try to use object array instead of string array

Author

Commented:
I did what you suggest but the problem still persist.
Consultant, Trainer
Commented:
so , the other way is to change you stored procedure to accept varchar values instead of bit,
then when you insert into table use convert function, i changed the stored procedure for you
and i  changed these lines too:

arParms[0] = new SqlParameter("@Index", SqlDbType.NVarChar);
            arParms[1] = new SqlParameter("@Active", SqlDbType.Bit);
            arParms[2] = new SqlParameter("@Description", SqlDbType.NVarChar);
           arParms[3] = new SqlParameter("@Licensed", SqlDbType.VarChar);
            arParms[4] = new SqlParameter("@Manufacturer_ID", SqlDbType.NVarChar);
            arParms[5] = new SqlParameter("@Name", SqlDbType.NVarChar);
            arParms[6] = new SqlParameter("@Procurement_ID", SqlDbType.NVarChar);
            arParms[7] = new SqlParameter("@Serial_number", SqlDbType.NVarChar);
           arParms[8] = new SqlParameter("@Supported", SqlDbType.VarChar);
            arParms[9] = new SqlParameter("@Tag", SqlDbType.NVarChar);
            arParms[10] = new SqlParameter("@ID", SqlDbType.NVarChar);
            arParms[10].Direction = ParameterDirection.InputOutput;




ALTER PROCEDURE [dbo].[USP_SaveSoftware] 
        -- Add the parameters for the stored procedure here
           @Index nvarchar(200)
           ,@Name  nvarchar(50)
           ,@Tag  nvarchar(50)
           ,@Description   nvarchar(350)
           ,@Manufacturer_ID nvarchar(200)
           ,@Serial_Number nvarchar(50)
           ,@Procurement_ID nvarchar(200)
           ,@Supported varchar(10)
           ,@Licensed varchar(10)
           ,@Active bit
           ,@ID nvarchar(200) OUTPUT
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        
        SELECT @ID = newid();
    
    INSERT INTO [Element].[dbo].[Software]
           ([ID]
           ,[Index]
           ,[Name]
           ,[Tag]
           ,[Description]
           ,[Manufacturer_ID]
           ,[Serial_Number]
           ,[Procurement_ID]
           ,[Supported]
           ,[Licensed]
           ,[Active])
     VALUES
           (@ID
           ,@Index
           ,@Name
           ,@Tag
           ,@Description
           ,Cast(@Manufacturer_ID AS uniqueidentifier)
           ,@Serial_Number
           ,Cast(@Procurement_ID AS uniqueidentifier)
           ,convert(bit,@Supported)
           ,convert(bit,@Licensed)
           ,@Active)
END

Open in new window

Greg GambleProgrammer

Commented:
You could try adding the DataType to the properties on AssignParameterValues()..
Hi,
@reza_rad :
 try to use object array instead of string array

yes, reza_rad said you should use only object array.while assigning ,you should assign bool value only (not string) as like :

  object[] arrStrValues = new object[11];
            arrStrValues[0] = Index;
            arrStrValues[1] = Active; //should be bool; no need of ToString()
            arrStrValues[2] = Description;
            arrStrValues[3] = Licensed; //should be bool; no need of ToString()
            arrStrValues[4] = new Guid().ToString();//Manufacturer_ID;
            arrStrValues[5] = Name;
            arrStrValues[6] = new Guid().ToString();//Procurment_ID;
            arrStrValues[7] = Serial_number;
            arrStrValues[8] = Supported; //should be bool; no need of ToString()
            arrStrValues[9] = Tag;
            arrStrValues[10] = ID;


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial