Web Venture 101

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

, posted: 26-Apr-2006 14:59

When someone registers or logs in, I store their UserId (a GUID) into their profile (as type="System.Guid"), so I can retrieve it later for use in queries (including stored procedures which take a uniqueidentifier as an input parameter).

When I tried to configure a datasource to use the profile parameter, I was getting a "Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query" response from the database.

Turns out (thanks to http://scottonwriting.net/sowblog/posts/4690.aspx) that the datatype of the parameter must be set to "Empty" (in the wizard), which results in the Type attribute in the asp:profileParameter being omitted completely.  For standard types, this would need to be set, but for GUIDs it should not be set.

Why they didn't just allow us to put in a type of "Guid" or "System.Guid" I don't know.

Other related posts:
Keys, Values, and NewValues when Inserting, Updating, Deleting with GridView, DetailsView, FormView and ObjectDataSource
Creating Typed Datasets - member names cannot be the same as their enclosing type
Finding controls inside ItemTemplates inside a FormView inside a LoginView

Comment by Tej, on 26-Jun-2009 21:59

Thanks. It solved my problem.

Comment by Speedy7, on 18-Jun-2010 05:32

Thanks for taking the time to post this -- it cleared up my problems in about two minutes of searching/reading.

Comment by Boilermaker1869, on 13-Jul-2010 02:42

Thanks for posting this, saved a ton of time researching.

Comment by Gufret, on 21-Feb-2011 02:50

Thanks for posting this, it saved another hour wondering how to do the Convert function.

Comment by Ken, on 21-Jun-2011 13:21

A quick fix after reading your post. Thanks so much!

Comment by New Dev, on 31-Dec-2011 23:52

Thanks so much , it solves my problem

Comment by id :), on 17-Apr-2012 01:01

Thanks so much!

Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

ptorrsmith's profile

Peter Torr Smith
New Zealand

I've been working on a web venture idea for the last year or so, so have now resigned from my job to go at it full-time.

Here's how I go, with business and technology challenges!!