asked on

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,

GuitarRich

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

GuitarRich

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.

ASKER CERTIFIED SOLUTION

GuitarRich

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dovberman

ASKER

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.

Dovberman

ASKER

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