Joins on uniqueidentifier columns

I have recently introduced Microsoft'suser/roles facility by running aspnet_regsql.exe against my database so that Iwould be able to link activities with staff members.

In testing I was able to

1. Join 2 tables (Reservations and aspnet_Users) with the join fields being of type uniqueidentifier, using SQL Server Manager Studio Express diagram facility;
2. Create a record in aspnet_Users using Microsoft's Website Admin tool
3. Create a record in Reservations and paste in the contents of uniqueidentifier field in aspnet_Users using SQL Server Manager Studio Express

All the basic tests had been fine so I created a web pagewith a detailsview of the Reservations table and made a templated field (in theinsertitemtemplate), replaced the text box with a dropdown and did a bind ofthat dropdown to the relevant records in the aspnet_Users table i.e. UserNameand UserId columns with UserName being displayed. The source is as follows:

<asp:TemplateFieldHeaderText="Taken By"SortExpression="RES_Taken_By_Staff_ID">

<EditItemTemplate>

<asp:TextBoxID="TextBox5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList5"runat="server"DataSourceID="SDSStaff"DataTextField="UserName"

DataValueField="UserId">

</asp:DropDownList><br/>

<asp:SqlDataSourceID="SDSStaff"runat="server"ConnectionString="<%$ ConnectionStrings:ReservationsDBConnectionString%>"

SelectCommand="SELECT[UserId], [UserName] FROM [vw_aspnet_Users]"></asp:SqlDataSource>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

Everything looks OK, the correct data appears in the dropdownbut when I hit the INSERT button I receive the following failure message:

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

Description:An unhandled exceptionoccurred during the execution of the current web request. Please review thestack trace for more information about the error and where it originated in thecode.

Exception Details:System.Data.SqlClient.SqlException: Implicitconversion from data type sql_variant to uniqueidentifier is not allowed. Usethe CONVERT function to run this query.

I can't quite see what I should do so I would begrateful for any help.

HI

I hopeInserting with a SqlDataSource Using uniqueidentifier Parameters can help you.

|||

Hi,

Thank you for this. The references did not cover Template Fields where SqlDataSource does not have a type attribute so I'm still stymied!

|||

Me,too. There is clearly something strange about inserting a uniqueidentifier that is not your primary key column into SQL from ASP.net (like a foreign key).

I followed the same advice quoted in the article above. It worked for my select & update statements, but not for my insert statements.

I cannot insert a foreign key into a table from ASP.net at all. Please help if you found a fix or workaround for this problem.

|||

Hi,

Yes, very odd! I've reported this as a bug so maybe confirmation/correction may be available from MS

|||

My workaround for now is to convert the uniqueidentifier foreign key to an integer.

I managed to get it working, but it's not ideal

- Roger

|||

Please try delete all TemplateField filds in grid.

Bag in grid:

I haved

<asp:GridView ID="GridViewMain" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="FAQID" DataSourceID="SqlDataSourceMain"
EmptyDataText="There are no data records to display.">
<Columns>
<asp:BoundField DataField="LotName" HeaderText="LotName" SortExpression="LotName" />
<asp:TemplateField HeaderText="From User" SortExpression="fromUser">
<ItemTemplate>
<asp:HyperLink ID="LinkFromUser" runat="server" Text='<%# Bind("[fromUser]") %>'
NavigateUrl='<%# "~/UserManagment/UserView.aspx?Filter=" + Eval("[From]") %>'>
</asp:HyperLink>
</ItemTemplate>
<EditItemTemplate>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Warning !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
<EditItemTemplate>
</EditItemTemplate>

without EditItemTemplate i have error

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

sql