I have a web site that is using ASP.NET Membership. When a user has logged in they can register their products. Then on the "myAccount" page the users registered products will be displayed. I am currently displaying the registered products in a GridView but i cannot filter the products to show only the logged in users products?

I am using a GridView and a SqlDataSource. What i have tried is below but it hasnt worked.

Data Source:

<asp:SqlDataSource ID="SqlDSmyProducts" runat="server" ConnectionString="<%$ ConnectionStrings:myioDBConString %>"
             SelectCommand="SELECT DISTINCT Products.Name, UsersProducts.SerialNo, UsersProducts.VersionNo, UsersProducts.PurchasedDate, UsersProducts.PurchasedFrom, UsersProducts.Other, UsersProducts.VehicleMake, UsersProducts.VehicleModel, UsersProducts.VehicleYear, UsersProducts.DateAdded FROM UsersProducts INNER JOIN Products ON UsersProducts.ProductId = Products.ProductId WHERE (UserProducts.UserID = @UserID) ORDER BY UsersProducts.DateAdded DESC">
             <SelectParameters>
           <asp:Parameter Name="UserID" Type="String" />
           </SelectParameters>

 </asp:SqlDataSource>

Behind Source Code (myProfile.aspx.vb)

 Protected Sub SqlDSmyProducts_Selecting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDSmyProducts.Selecting
        SqlDSmyProducts.SelectParameters.Item("UserID").DefaultValue = Membership.GetUser(True).ProviderUserKey.ToString()

    End Sub

Can anyone help me with this?

When viewing the page using the above code the GridView does not display at all.

Many Thanks 

I've not got time to really look into this at the moment, but my first thought is that the UserID property isn't being set correctly.

You should also check out the tutorial videos, I'm sure I remember one of them having just this example...

Please try this one i shoudl help.

Dim gString As String = Membership.GetUser.ProviderUserKey()
                Dim gUser As Guid = New Guid(gString)

SqlDSmyProducts.SelectParameters.Item("UserID").DefaultValue = gUser

regards,

Maybe a different method, but rty lokoing at this video...

http://www.asp.net/learn/videos/video-47.aspx

Hope this helps!

Hi,

    that is because of the nature of the UserID...it is of type System.Guid and you have to first convert it to proper GUID...See the below code for it 

Dim guidString As String = Membership.GetUser.ProviderUserKey
        Dim guidStringUser As Guid = New Guid(guidString)
        Command.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier)
        Command.Parameters("@UserID").Value = guidStringUser

Hi this way you have to first get the proper type and instantiate Guid type....enjoy and it will def work...actually it won't show any data but it is bcse of UserID

 All the best,

try this to populate the gridview...

      SqlConnection strConn =new SqlConnection("<your connection string>");
      strConn.open();
      SqlParameter UserID = new SqlParameter(@UserID,<parameter value>);(sample:textbox1.text)
      System.Data.DataSet ds = new System.Data.DataSet();
      OleDbDataAdapter da = new OleDbDataAdapter(SELECT DISTINCT Products.Name, UsersProducts.SerialNo, UsersProducts.VersionNo, UsersProducts.PurchasedDate, UsersProducts.PurchasedFrom, UsersProducts.Other, UsersProducts.VehicleMake, UsersProducts.VehicleModel, UsersProducts.VehicleYear, UsersProducts.DateAdded FROM UsersProducts INNER JOIN Products ON UsersProducts.ProductId = Products.ProductId WHERE (UserProducts.UserID = @UserID) ORDER BY UsersProducts.DateAdded DESC,strConn )
        da.Fill(ds);
        GridView1.DataSource = ds.Tables[0].DefaultView;
        GridView1.DataBind();

Hi

 thanks for your reply.

When i paste this code into the behind code (aspx.vb) file i get the following Errors appears:

Error 1 'Parameters' is not a member of 'String'. C:\Documents and Settings\brianyoung\My Documents\Visual Studio 2005\WebSites\MyAccount\myProfile.aspx.vb      11     9 

Error 2 Name 'SqlDbType' is not declared. C:\Documents and Settings\brianyoung\My Documents\Visual Studio 2005\WebSites\MyAccount\myProfile.aspx.vb    11     43 

Error 3 'Parameters' is not a member of 'String'. C:\Documents and Settings\brianyoung\My Documents\Visual Studio 2005\WebSites\MyAccount\myProfile.aspx.vb   12    9 

The code for the SQL data source is:

<asp:SqlDataSource ID="SqlDSmyProducts" runat="server" ConnectionString="<%$ ConnectionStrings:myioDBConString %>"

SelectCommand="SELECT DISTINCT Products.Name, UsersProducts.SerialNo, UsersProducts.VersionNo, UsersProducts.PurchasedDate, UsersProducts.PurchasedFrom, UsersProducts.Other, UsersProducts.VehicleMake, UsersProducts.VehicleModel, UsersProducts.VehicleYear, UsersProducts.DateAdded FROM UsersProducts INNER JOIN Products ON UsersProducts.ProductId = Products.ProductId WHERE (UserProducts.UserID = @UserID) ORDER BY UsersProducts.DateAdded DESC">

<SelectParameters>

<asp:Parameter Name="UserID" Type="String" />

</SelectParameters>

</asp:SqlDataSource>

The code for the vb file:

Protected Sub SqlDSmyProducts_Selecting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDSmyProducts.Selecting

Dim guidStringUser As Guid = New Guid(guidString)

End Sub

End Class

what do these errors mean?

How can i solve this?

Many thanks

 Hi Just try the following code

 SqlSmyProducts.SelectParameters.Item("UserID").DefaultValue = guidStringUser

and then do

gridview.databind()

You can debug your application if this doesn't work out....Your only problem is with the UserID field because you are passing Guid type...

So do this one

Cheers, 

I have tried this and get the following error:

Error 2 Value of type 'System.Guid' cannot be converted to 'String'. 

Here is my vb code (myProfile.aspx.vb)

Partial Class MyAccount_myProfile
    Inherits System.Web.UI.Page

    Protected Sub SqlDSmyProducts_Selecting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDSmyProducts.Selecting
        Dim guidString As String = Membership.GetUser.ProviderUserKey
        Dim guidStringUser As Guid = New Guid(guidString)

        SqlDSmyProducts.SelectParameters.Item("UserID").DefaultValue = guidStringUser

        GridView1.DataBind()


    End Sub

End Class

Here is my data source code 

 
<asp:SqlDataSource ID="SqlDSmyProducts" runat="server" ConnectionString="<%$ ConnectionStrings:myioDBConString %>"
             SelectCommand="SELECT DISTINCT Products.Name, UsersProducts.SerialNo, UsersProducts.VersionNo, UsersProducts.PurchasedDate, UsersProducts.PurchasedFrom, UsersProducts.Other, UsersProducts.VehicleMake, UsersProducts.VehicleModel, UsersProducts.VehicleYear, UsersProducts.DateAdded FROM UsersProducts INNER JOIN Products ON UsersProducts.ProductId = Products.ProductId WHERE (UserProducts.UserID = @UserID) ORDER BY UsersProducts.DateAdded DESC">
             <SelectParameters>
           <asp:Parameter Name="UserID" Type="String" />
           </SelectParameters>
         </asp:SqlDataSource>
 
Any ideas?? 

Hi Oh Ho,

    try this and it will def run...

Protected Sub SqlDSmyProducts_Selecting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDSmyProducts.Selecting
       
        Dim guidStringUser As Guid = New Guid(Membership.GetUser.ProviderUserKey)

        SqlDSmyProducts.SelectParameters.Item("UserID").DefaultValue = guidStringUser

        GridView1.DataBind()

End Sub

This should work...and do let me know if it worked

Enjoy, 

 Hi Nimesh, Thank you very much for you help but i am still geting two errors.

Error    1    Overload resolution failed because no accessible 'New' can be called without a narrowing conversion:
    'Public Sub New(g As String)': Argument matching parameter 'g' narrows from 'Object' to 'String'.          Line  5    38    C:\...\my-io.com\
Error    2    Value of type 'System.Guid' cannot be converted to 'String'.        Line 7    72    C:\...\my-io.com\
 

Compiler Error Message: BC30519: Overload resolution failed because no accessible 'New' can be called without a narrowing conversion:
 

The code for the aspx.vb file:

1    Partial Class MyAccount_myProfile
2        Inherits System.Web.UI.Page
3        Protected Sub SqlDSmyProducts_Selecting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDSmyProducts.Selecting
4    
5            Dim guidStringUser As Guid = New Guid(Membership.GetUser.ProviderUserKey)
6    
7            SqlDSmyProducts.SelectParameters.Item("UserID").DefaultValue = guidStringUser
8    
9            GridView1.DataBind()
10   
11       End Sub

The SqlDataSource code:

<asp:SqlDataSource ID="SqlDSmyProducts" runat="server" OnSelecting="SqlDSmyProducts_Selecting1" ConnectionString="<%$ ConnectionStrings:myioDBConString %>"
             SelectCommand="SELECT DISTINCT Products.Name, UsersProducts.SerialNo, UsersProducts.VersionNo, UsersProducts.PurchasedDate, UsersProducts.PurchasedFrom, UsersProducts.Other, UsersProducts.VehicleMake, UsersProducts.VehicleModel, UsersProducts.VehicleYear, UsersProducts.DateAdded FROM UsersProducts INNER JOIN Products ON UsersProducts.ProductId = Products.ProductId WHERE (UserProducts.UserID = @UserID) ORDER BY UsersProducts.DateAdded DESC">
             <SelectParameters>
                <asp:Parameter Name="UserID" Type="String" />
           </SelectParameters>
</asp:SqlDataSource>

Sorry for this but your help will be greatky appreciated.  

Many Thanks

Younger

Hi,

    I am sure that the reason is passing of type Guid...you can try out from following options

line no. 7

7            SqlDSmyProducts.SelectParameters.Item("UserID").DefaultValue = Membership.GetUser.ProviderUserKey

OR else I am pasting my following code and its working properly and I got the same kind of erorrs that you are going through....just check out this working code of mine

 
Dim myConnStr As String = SiteTools.GetConn()
        Dim myConn As New SqlClient.SqlConnection(myConnStr)
        myConn.Open()
        Dim codeAL As New ArrayList

        Dim found As Boolean = False

        Dim dbCommand As New SqlClient.SqlCommand
        Dim ReaderId As SqlClient.SqlDataReader

        Dim guidString As String = Profile.UserDetails.UserID.ToString
        Dim guidStringUser As Guid = New Guid(guidString)

        Dim Sql As String = "SELECT DISTINCT tblPortfolioRecords.Code_ID FROM tblPortfolioName INNER JOIN" & " "
        Sql &= "tblPortfolioRecords ON tblPortfolioName.PortfolioID = tblPortfolioRecords.PortfolioID" & " "
        Sql &= "WHERE (tblPortfolioName.UserID = @UserID)"

        dbCommand = New SqlClient.SqlCommand(Sql, myConn)

        dbCommand.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier)
        dbCommand.Parameters("@UserID").Value = guidStringUser

        ReaderId = dbCommand.ExecuteReader()

just see the conversion of type..

Enjoy, 

Hello all,

thank you very much for all of your help. I have found a ideal solution for getting the logged on userID.

If anyone needs this example the web site i got it from is below:

http://aspnet.4guysfromrolla.com/articles/110106-1.aspx#postadlink

Many Thanks for everyones help.