Today I ran into a pretty ugly bug in ASP.NET 2.0.50727 (i.e. .NET 2.0 RTM). Imagine you’re using the freebie .NET 2.0 Membership stuff and you would like to list all Roles that the currently-logged-in user is associated with and you’d like to use a GridView to do so. So imagine you create a stored procedure with the query in it and pointing the SqlDataSource to this stored procedure and you’d like to pass that user’s GUID to the SP to display this list. Here would be a simple SP to do something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE PROCEDURE MyProc @UserID UNIQUEIDENTIFIER AS BEGIN
 
SELECT
	RoleName
FROM
	vw_aspnet_UsersInRoles UserRoles
INNER JOIN
	vw_aspnet_Roles Roles
ON
	UserRoles.RoleId=Roles.RoleId
WHERE
	UserRoles.UserId=@UserID
END

Sounds simple enough, right? So we would have our ASPX page setup with the following code (assuming the GUID is in a “LoggedInUserID” session variable):

1
2
3
4
5
6
7
8

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnString%>"
 SelectCommand="MyProc" SelectCommandType="StoredProcedure">
 <selectparameters>
  <asp:SessionParameter Name="UserID" SessionField="LoggedInUserID" Type="Object" />
 </selectparameters>
</asp:SqlDataSource>

That is the code that the Visual Studio 2005 IDE generates for you when you go through the point-and-click interface. The problem with this is that you get the following SQL Error:

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

Obviously something is wrong with the datatype .NET is sending to SQL Server. Note that the IDE told us to use “Object” type which, indeed, makes sense that SQL Server interprets that as sql_variant. So what type should we specify in our ASPX file? Our options, according to Intellisense, are: Boolean, Byte, Char, DateTime, DBNull, Decimal, Double, Empty, Int16, Int32, Int64, Object, SByte, Single, String, UInt16, UInt32, and UInt64. The only things in that list that aren’t obvious that we can rule out without thought or trial are Char, Empty, Object, and String. So let’s look at our remaining options…

Char
This is talking about a C# char data type, not a SQL char field which means only 1 character can be stored in this data type. This won’t work.

Empty
For some reason, this actually works! But only in this case. I’ll revisit this case later.

Object
We saw above that this case throws an error.

String
This case throws a similar error but instead of converting from sql_variant, it’s erroring when implicitly converting from nvarchar.

So the answer we have so far is to use the Empty type for this parameter, right? It appears so but when I tried this in a more complex example, it definitely didn’t work. Change the Type=String and modifying the SP such that it accepted a varchar as that parameter and later explicitly converting it to UNIQUEIDENTIFIER, this made the more complex example work.

So the first thing I asked myself was, “What is this ‘Empty’ data type anyways?” Well, that’s something I didn’t know so I began to look it up. Unfortunately, I couldn’t find much of anything stating what this was. As such, I’m currently stuck on why this is happening this way.