February 20, 2009 at 5:33 am
db.ExecuteNonQuery(cmd);
where cmd is the stored procedure for insert. Insert query is working but when the control reaches the above statement, it is throwing the exception "Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query".
Please help 🙁
February 20, 2009 at 7:29 am
It means somewhere within that procedure something's trying to convert a sql_variant to varchar.
Can you post the procedure?
Why are you using SQL_variants anywhere?
How are you constructing the parameter list in your front end code? (C#?)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2009 at 12:53 am
This is our stored procedure
CREATE PROCEDURE [dbo].[usp_InsertProductBacklogDetails]
(
@UserStory varchar(150),
@Priority int,
@StoryPoints int,
@EstimatedSize int,
@PlannedSprint int,
@ClarityIndex decimal(2,1),
@status int
)
AS
BEGIN
INSERT INTO dbo.Product_Backlog(User_Story,Priority,Story_Points,Estimated_Size,Planned_Sprint,Clarity_Index,Status,Cr_By)
VALUES(@UserStory,@Priority,@StoryPoints,@EstimatedSize,@PlannedSprint,@ClarityIndex,@Status,1)
END
We are using the Enterprise library in data acess layer. We are using Hybrid dictionary to pass the parameters.
Front end code:
In Business Layer
Function to insert the values:
public Boolean InsertProductBackLog()
{
HybridDictionary param = new HybridDictionary();
objDataLayer = new DABase();
param.Add("UserStory",UserStory);
param.Add("Priority", Priority);
param.Add("StoryPoints", StoryPoints);
param.Add("EstimatedSize", EstimatedSize);
param.Add("PlannedSprint", PlannedSprint);
param.Add("ClarityIndex", ClarityIndex);
param.Add("Status", Status);
objDataLayer.Parameters = param;
//objDataLayer.Parameters.Add("createdby", 1);
objDataLayer.ProcedureName = "usp_InsertProductBacklogDetails";
objDataLayer.Execute();
return true;
}
Code in data access layer
setting the property to get the parameters:
public HybridDictionary Parameters
{
get
{
return parameters;
}
set
{
parameters = value;
}
}
Function for ExecuteNonQuery
public void Execute()
{
try
{
//the database is set in a seperate function called SetDatabase
db = SetDatabase();
//cmd execution
db.ExecuteNonQuery(cmd,parameters);--------------->we are getting the exception here 🙁
}
catch (SqlException ex)
{
throw ex;
}
}
Function to Set Database:
public Database SetDatabase()
{
db = DatabaseFactory.CreateDatabase("PMO_DBConnectionString");
cmd = db.GetStoredProcCommand(ProcedureName);
AddParameter(cmd, db);
return db;
}
Function to add parameters:
public void AddParameter(DbCommand cmd, Database db)
{
if (Parameters != null)
{
foreach (DictionaryEntry param in Parameters)
db.AddInParameter(cmd, param.Key.ToString(), DbType.Object, param.Value);
}
}
February 21, 2009 at 3:03 am
susaabraham (2/21/2009)
db.AddInParameter(cmd, param.Key.ToString(), DbType.Object, param.Value);
There's your problem. According to the data type mapping on MSDN (http://msdn.microsoft.com/en-us/library/cc716729.aspx), DBType.object maps to SQL varient. So you're trying to pass all the parameters as SQL_variants and when one won't convert, you will get this error.
If you could change the code so that the types are set correctly for the parameters, the problem should go away. Either add the type to the hybridDictionary or infer the correct type when adding the parameter
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2009 at 4:23 am
Actually our data access layer is a generic one and many other forms from the presentation layer is using the same data access layer.So different forms will have different parameters in the insert function.Because of this we cannot directly give the parameters and its type. That is why we are using Hybrid dictionary to set the parameters, as the count of parameters will be unknown in the datalayer.
You have mentioned about adding the type to the hybridDictionary. Can you please help us to do that....
do you have any sample with you
it is very urgent....please help 🙂
February 21, 2009 at 4:34 am
susaabraham (2/21/2009)
You have mentioned about adding the type to the hybridDictionary. Can you please help us to do that....do you have any sample with you
No samples, and C# is not an area I know well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2009 at 2:55 am
Hi,
We got the solution. We changed our code in DataAccess Layer.Correct code is below.
Thanks for your help:)
public void AddParameter(DbCommand cmd, Database db)
{
if (Parameters != null)
{
foreach (DictionaryEntry param in Parameters)
{
if (param.Value.GetType() == typeof(string))
{
db.AddInParameter(cmd, param.Key.ToString(), DbType.String, param.Value);
}
if (param.Value.GetType() == typeof(int))
{
db.AddInParameter(cmd, param.Key.ToString(), DbType.Int32, param.Value);
}
if (param.Value.GetType() == typeof(decimal))
{
db.AddInParameter(cmd, param.Key.ToString(), DbType.Decimal, param.Value);
}
}
}
}
February 22, 2009 at 10:16 am
Yup, that should work.
Just one thing. What happens if the parameter is not string, into or decimal? May I suggest you make that a switch statement that covers all the types you use, along with an else for when nothing matches.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2009 at 12:39 am
Thats true. we are planning to do that.
Thanks for your suggestion:)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply