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

Error occurs when passing the UserId (GUID) as a parameter to a SqlDataSource.

Added on 6 Jul 2008

Scenarios:

Scenario Summary:

This may occur when binding a data control such as a repeater to a SqlDataSource that has the UserId, or other GUID value, as a property.

Scenario Details:

Trying to bind a SqlDataSource (that called a SELECT Stored Procedure) to repeater, I encountered this error. The code I was using follows:

    <asp:Repeater ID="Repeater1" runat="server"
        DataSourceID="SqlDataSource_Areas">
        <ItemTemplate>
            <%# Eval("Description") %>
        </ItemTemplate>
    </asp:Repeater>
    <asp:SqlDataSource ID="SqlDataSource_Areas" runat="server"
        ConnectionString="<%$ ConnectionStrings:connString %>"
        SelectCommand="app_Areas_ByUser_Select" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:Parameter DefaultValue="<%= Membership.GetUser().ProviderUserKey %>" Name="UserId" Type="Object" />
            <asp:Parameter DefaultValue="True" Name="Enabled" Type="Boolean" />
            <asp:Parameter DefaultValue="0" Name="ParentId" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

Added on 6 Jul 2008

Solution Summary:

Change the datatype to String and set the GUID parameter in the code-behind to workaround this bug.

Solution Details:

Simply remove the GUID parameter from the ASPX code and add the parameter in the Load event in the code-behind, changing the datatype to String, as follows:

    Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            SqlDataSource_Areas.SelectParameters.Add("UserId", TypeCode.String, Membership.GetUser().ProviderUserKey.ToString)
        End If
    End Sub

The revised ASPX code follows:

    <asp:Repeater ID="Repeater1" runat="server"
        DataSourceID="SqlDataSource_Areas">
        <ItemTemplate>
            <%# Eval("Description") %>
        </ItemTemplate>
    </asp:Repeater>
    <asp:SqlDataSource ID="SqlDataSource_Areas" runat="server"
        ConnectionString="<%$ ConnectionStrings:connString %>"
        SelectCommand="app_Areas_ByUser_Select" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:Parameter DefaultValue="True" Name="Enabled" Type="Boolean" />
            <asp:Parameter DefaultValue="0" Name="ParentId" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

Was this solution useful? Yes No

Added on 6 Jul 2008

Rating: