Experts Exchange connects you with the people and services you need so you can get back to work.

Conversion failed when converting from a character string to uniqueidentifier.

I am trying to pass a parameter from ASP.NET to a SQL Server Stored procedure.

string strUserID = currentUser.ProviderUserKey.ToString();
Returns "ba275a68-9e77-4301-9d97-7cc7dfecda0d"

SqlCommand cmd = new SqlCommand("usp_addWatchList", conStockSelect);

cmd.Parameters.Add("@UserID", SqlDbType.VarChar).Value = strUserID;
cmd.ExecuteNonQuery();
Conversion failed when converting from a character string to uniqueidentifier.

---------
Stored procedure executes successfully from SQL Server Manager
ALTER PROCEDURE [dbo].[usp_addWatchList]
@SymbolID int,
@UserID varchar(50)

/*
exec usp_addWatchList
@SymbolID =329,
@UserID = 'BA275A68-9E77-4301-9D97-7CC7DFECDA0D'
*/

What am I missing?

Thanks,

Dovberman

Asked: 2009-05-07

Advertise Here

ASP.NET

1 Solution

LVL 12

GuitarRichCommented: 2009-05-07

when adding the parameter it should be passed as SqlDbType.UniqueIdentifier
Like this:
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = strUserID;

LVL 12

GuitarRichCommented: 2009-05-07

sorry - don't think I read it properly - the other problem you could be having is it not putting the value into the parameter right. I always add parameters like this:
cmd.Parameters.AddWithValue("@UserID", strUserID);
I've had problems in the past the other way where the value was not actually stored in the parameter object properly.

LVL 12

GuitarRichCommented: 2009-05-07

one final thing - it could be in the stored proc when you compare the table field to @UserID - if the table field is a UniqueIdentifier that will fail. Change the parameter in the stored proc to a UniqueIdentifier and add the parameter as System.UniqueIdentifier
Theres a few things to look at anyway :)

DovbermanAuthor Commented: 2009-05-07

I set everything to uniqueidentifier.
Error: Failed to convert parameter value from a String to a Guid.

Also tried cmd.Parameters.AddWithValue("@UserID", strUserID);

Error:Conversion failed when converting from a character string to uniqueidentifier.

DovbermanAuthor Commented: 2009-05-07

I set everything to uniqueidentifier.

AND
Also tried cmd.Parameters.AddWithValue("@UserID", strUserID);
AND changed the sp from this:
INSERT INTO WatchList
(UserID,SymbolID,PickDatePrice,PickDate,MarketID)
VALUES('@UserID',@SymbolID,23.45,'2009-04-23',3)

To this:
INSERT INTO WatchList
(UserID,SymbolID,PickDatePrice,PickDate,MarketID)
VALUES(@UserID,@SymbolID,23.45,'2009-04-23',3)

Now it works.
Thanks

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Conversion failed when converting from a character string to uniqueidentifier.

Join & Write a Comment Already a member? Login.

Featured Post