Link to home
Start Free TrialLog in
Avatar of Abdu_Allah
Abdu_Allah

asked on

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

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

Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

could you let me know the structure of table too
Avatar of Abdu_Allah
Abdu_Allah

ASKER

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

problem is not between the table and storedprocedure,
upload whole your code consist of execute stored procedure here, i think the problem is there
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

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

I did what you suggest but the problem still persist.
ASKER CERTIFIED SOLUTION
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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;