Hi all,
I have setup my database that have 3 columns:
1. Primary Key
2. UserId (UniqueIdentifier, Guid I guess) - I set it up so I can insert the value from the Membership table (UserId) for some relationship.
3. Another Foreign key with just an int.
I tried to build a DAL, ran a test and received this error: "Failed to convert parameter value from a String to a Guid." Before I setup my UserId to be UniqueIdentifier and let it be just an Int, I don't have any problem retrieving data. Here is the SELECT query that I built with the DataSet:
SELECT aspnet_Users.UserId, t_music.MUSIC_TITLE
FROM t_user_viewed
JOIN aspnet_Users ON aspnet_Users.UserId = t_user_viewed.UserId
JOIN t_music ON t_music.MUSIC_PK = t_user_viewed.MUSIC_PK_FK
WHERE aspnet_Users.UserId = @.UserId
Any help would be greatly appreciated,
Kenny.
You need to post definition for @.UserId parameter.
|||Can you please elaborates? I'm new to asp.net as well as ms-sql.
Thank you,
Kenny.
Sounds like you need to cast the string value to GUID on the @.UserId when you call the select function and pass the @.UserId value.
ctype(StringValueGUID, GUID)
Burl
|||Where do I cast the string value? In the DAL (dataset) or where I have my ObjectDataSource doing the select? Sorry for my lack of knowledge on asp.net and mssql.
Thanks,
Kenny.
|||How are you passing the GUID to the ObjectDataSource for the select? Wherever that value comes from, its in a string type and needs to be cast as a GUID type. Can you post some code?
Burl
|||I've created a Dataset and setup my table, then I've created a BLL that have this code to get the fields:
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}
In my ObjectDataSource, I chose the above method (GetSongsByUserPK) and have the parameter source as a session. Here is the code for the ObjectDataSource:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>
The SELECT query is the same as my first post... is there anything else I need to do?
Thank you very, very much.
Kenny.
Well,
I've made some minor adjustment to my code and it's working now, however, the SELECT statement that I wrote in the DataSet didn't return the proper table. Here are the changes:
1. In my DataSet, I have setup the table called t_user_viewed that will store the primary key of t_music database as foreign key, and the UserId from the aspet_users as another foreign key. Here is the select statement:
SELECT *
FROM t_music a
JOIN t_user_viewed b on a.MUSIC_PK = b.MUSIC_PK_FK
JOIN aspnet_Users c ON b.UserID = c.UserId
WHERE b.UserID = @.UserId
So when I run this code, and enter the UserId, it should return the information from table "t_music" (with all information such as song title, artist, etc...) instead.
2. This is the code for the SongsBLL.cs class, to get the above select statement:
private t_user_viewedTableAdapter _t_user_viewed = null;
protected t_user_viewedTableAdapter Adapter2
{
get
{
if (_t_user_viewed == null)
_t_user_viewed = new t_user_viewedTableAdapter();
return _t_user_viewed;
}
}
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
public NotesNhac.t_user_viewedDataTable GetSongsByUserPK(Guid UserId)
{
return Adapter2.GetSongsByUserPK(UserId);
}
3. Here is the ObjectDataSource that was generated by VS:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetSongsByUserPK"
TypeName="SongsBBL" OldValuesParameterFormatString="original_{0}">
<SelectParameters>
<asp:SessionParameter Name="UserId" SessionField="UserId" Type="Object" />
</SelectParameters>
</asp:ObjectDataSource>
4. Here is the GridView that was generated automatically when I select the DataSourceID to the above ObjectDataSource:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="VIEWED_PK"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:BoundField DataField="VIEWED_PK" HeaderText="VIEWED_PK" InsertVisible="False"
ReadOnly="True" SortExpression="VIEWED_PK" />
<asp:BoundField DataField="MUSIC_PK_FK" HeaderText="MUSIC_PK_FK" SortExpression="MUSIC_PK_FK" />
<asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" />
</Columns>
</asp:GridView>
Apparently, the GridView & the ObjectDataSource only pick up the t_music_viewed table columns, instead of the "t_music" columns that I specified in my Select statement.
Any idea?
Thank you very much,
Kenny.
I've solved it by building my SELECT query in the "t_music" table instead.
THANK YOU!
Kenny.
I've solved it by building my SELECT query in the "t_music" table instead.
THANK YOU!
Kenny.
