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:
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
could you let me know the structure of table too
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
problem is not between the table and storedprocedure,
upload whole your code consist of execute stored procedure here, i think the problem is there
upload whole your code consist of execute stored procedure here, i think the problem is there
ASKER
Ok, here is how I execute the stored procedure:
AssignParameterValues(arPa rms, arrStrValues);
Base.ExecuteNonQuery(Conne ctionStrin g, "USP_SaveSoftware", arParms);
The following is the body of AssignParameterValues function.
AssignParameterValues(arPa
Base.ExecuteNonQuery(Conne
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];
}
}
}
OK, so problem is in : AssignParameterValues(arPa rms, 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
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
ASKER
I did what you suggest but the problem still persist.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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();//Manufa cturer_ID;
arrStrValues[5] = Name;
arrStrValues[6] = new Guid().ToString();//Procur ment_ID;
arrStrValues[7] = Serial_number;
arrStrValues[8] = Supported; //should be bool; no need of ToString()
arrStrValues[9] = Tag;
arrStrValues[10] = ID;
@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();//Manufa
arrStrValues[5] = Name;
arrStrValues[6] = new Guid().ToString();//Procur
arrStrValues[7] = Serial_number;
arrStrValues[8] = Supported; //should be bool; no need of ToString()
arrStrValues[9] = Tag;
arrStrValues[10] = ID;