Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query. Expand / Collapse
Author
Message
Posted Friday, February 20, 2009 5:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 1, 2009 10:48 PM
Points: 5, 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 :-(
Post #661206
Posted Friday, February 20, 2009 7:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #661288
Posted Saturday, February 21, 2009 12:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 1, 2009 10:48 PM
Points: 5, 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);
}
}





Post #661979
Posted Saturday, February 21, 2009 3:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #661994
Posted Saturday, February 21, 2009 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 1, 2009 10:48 PM
Points: 5, 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 :)

Post #662010
Posted Saturday, February 21, 2009 4:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #662012
Posted Sunday, February 22, 2009 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 1, 2009 10:48 PM
Points: 5, Visits: 8
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);

}



}
}
}
Post #662158
Posted Sunday, February 22, 2009 10:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #662211
Posted Monday, February 23, 2009 12:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 1, 2009 10:48 PM
Points: 5, Visits: 8
Thats true. we are planning to do that.

Thanks for your suggestion:)
Post #662387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse