SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function...


Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

Author
Message
susaabraham
susaabraham
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 8
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 :-(
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86836 Visits: 45257
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


susaabraham
susaabraham
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 8
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);
}
}
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86836 Visits: 45257
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


susaabraham
susaabraham
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 8
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 Smile
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86836 Visits: 45257
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


susaabraham
susaabraham
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 8
Hi,

We got the solution. We changed our code in DataAccess Layer.Correct code is below.
Thanks for your helpSmile



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);

}



}
}
}
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86836 Visits: 45257
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


susaabraham
susaabraham
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 8
Thats true. we are planning to do that.

Thanks for your suggestionSmile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search