Select SQL records that match userid of user logged in?

[shawn.bordeaux]

Basically I am just trying to figure out how I can display customer records for the user who is logged in. I am sure this is very simple just not 100% sure how. I have a form that the user submits new customers and it stores the userid for the user who is logged in so I can reference when we want to display the records they are responsible for. This has been an easy task for me using asp classic and I just need the right way to do it using .NET.

Something like

SELECT *
FROM customers
WHERE userid = @userid
ORDER BY submit_date

I have an insert form view that inserts data including the userID of the logged in user.Using this VB code in my code behind page;

Protected Sub jras_intranet_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles jras_intranet.Inserting
        e.Command.Parameters("@userid").Value = Membership.GetUser.ProviderUserKey

    End Sub

Here is the datasource code I am using that I need to verify with a WHERE statement userid = userid somehow;

<asp:ObjectDataSource ID="ObjectDataSource2" runat="server" 
        DeleteMethod="Delete" InsertMethod="Insert" 
        OldValuesParameterFormatString="original_{0}" SelectMethod="GetDataCustomerEntries" 
        TypeName="customer_entryTableAdapters.customer_entryTableAdapter" 
        UpdateMethod="Update">
        <SelectParameters>
        
        </SelectParameters>
        <DeleteParameters>
            <asp:Parameter Name="Original_cus_id" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="first_name" Type="String" />
            <asp:Parameter Name="last_name" Type="String" />
            <asp:Parameter Name="cell_phone" Type="String" />
            <asp:Parameter Name="other_phone" Type="String" />
            <asp:Parameter Name="status" Type="String" />
            <asp:Parameter Name="paid" Type="String" />
            <asp:Parameter Name="date_submited" Type="DateTime" />
            <asp:Parameter Name="notes" Type="String" />
            <asp:Parameter Name="email" Type="String" />
            <asp:Parameter Name="location" Type="String" />
            <asp:Parameter Name="appointment_date" Type="String" />
            <asp:Parameter Name="Original_cus_id" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="first_name" Type="String" />
            <asp:Parameter Name="last_name" Type="String" />
            <asp:Parameter Name="cell_phone" Type="String" />
            <asp:Parameter Name="other_phone" Type="String" />
            <asp:Parameter Name="status" Type="String" />
            <asp:Parameter Name="paid" Type="String" />
            <asp:Parameter Name="date_submited" Type="DateTime" />
            <asp:Parameter Name="notes" Type="String" />
            <asp:Parameter Name="email" Type="String" />
            <asp:Parameter Name="location" Type="String" />
            <asp:Parameter Name="appointment_date" Type="String" />
        </InsertParameters>
    </asp:ObjectDataSource>

Your help is greatly appreciated!
Best regards,
Shawn

[sknake]

You should be able to right click on the SQL Table Adapter in the designer and add a new Fill query, FillByUserId(@UserId) which will define the parameter for you.

[shawn.bordeaux]

I see where I can add another SQL select query. What would I click on to add a fill query? Thanks!!!!

[sknake]

I'm sorry, i meant another Select query. It defaults the name of the Select queries to "Fill", hence my confusion.

Just add another select query and you will be set.

[shawn.bordeaux]

Okay I understand now. I did that and basically changed my select statment to reflect;

WHERE userid=@UserID

Then i renamed the method to FillByUserID.

I then changed by datasource

SelectMethod="FillByUserID"

However when I load the page it I recieve an error that states;
ObjectDataSource 'ObjectDataSource2' could not find a non-generic method 'FillByUserID' that has no parameters.
Basically I think I need to find a way to define what UserID is for the logged in User?

Thank you!

[sknake]

You should be calling the tableAdapter.Fill() method in code to populate the dataset? In that case you would .FillByUserId(). I'm not sure how you are calling the adapter here, could you post more information?

You probably need to do more work in the designer, from the code you pasted earlier:

<SelectParameters>
        
        </SelectParameters>

It seems like the UserId parameter should be in there for the select statement.

[shawn.bordeaux]

<SelectParameters>
       <asp:Parameter Name="UserID" />
       </SelectParameters>

[sknake]

edit your message and stick that in [code] wrappers

[shawn.bordeaux]

edit your message and stick that in [code] wrappers

Sorry about that. Jumped the gun and used quick reply without thinking about the CODE tags. Thanks!

[shawn.bordeaux]

Okay, I've edited it 3 times and used the code tags yet it still is displaying funky for me. Basically it is the same as up top I just added

<SelectParameters>
       <asp:Parameter Name="UserID" />
       </SelectParameters>

I am not calling the adaptor at all in my code behind file for selecting info. I have a SUB for inserting a value which is;

Protected Sub jras_intranet_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles jras_intranet.Inserting
        e.Command.Parameters("@bd_id").Value = Membership.GetUser.ProviderUserKey

    End Sub

Thank you for the assistance!

[shawn.bordeaux]

I added this in my code behind file;

Protected Sub ObjectDataSource2_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
        e.Command.Parameters("@UserID").Value = Membership.GetUser().ProviderUserKey.ToString()
    End Sub

And this is how I call it in design

<asp:ObjectDataSource ID="ObjectDataSource2" runat="server" 
        DeleteMethod="Delete" InsertMethod="Insert" 
        OldValuesParameterFormatString="original_{0}" SelectMethod="GetDataBy" 
        TypeName="customer_entryTableAdapters.customer_entryTableAdapter" 
        UpdateMethod="Update">
        <SelectParameters>
       <asp:Parameter Name="UserID" />
       </SelectParameters>
        <DeleteParameters>
            <asp:Parameter Name="Original_cus_id" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="first_name" Type="String" />
            <asp:Parameter Name="last_name" Type="String" />
            <asp:Parameter Name="cell_phone" Type="String" />
            <asp:Parameter Name="other_phone" Type="String" />
            <asp:Parameter Name="status" Type="String" />
            <asp:Parameter Name="paid" Type="String" />
            <asp:Parameter Name="date_submited" Type="DateTime" />
            <asp:Parameter Name="notes" Type="String" />
            <asp:Parameter Name="email" Type="String" />
            <asp:Parameter Name="location" Type="String" />
            <asp:Parameter Name="appointment_date" Type="String" />
            <asp:Parameter Name="Original_cus_id" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="first_name" Type="String" />
            <asp:Parameter Name="last_name" Type="String" />
            <asp:Parameter Name="cell_phone" Type="String" />
            <asp:Parameter Name="other_phone" Type="String" />
            <asp:Parameter Name="status" Type="String" />
            <asp:Parameter Name="paid" Type="String" />
            <asp:Parameter Name="date_submited" Type="DateTime" />
            <asp:Parameter Name="notes" Type="String" />
            <asp:Parameter Name="email" Type="String" />
            <asp:Parameter Name="location" Type="String" />
            <asp:Parameter Name="appointment_date" Type="String" />
        </InsertParameters>
    </asp:ObjectDataSource>

And finally my gridview;

<asp:GridView ID="GridView1" 
        runat="server" 
        AllowPaging="True" 
        AllowSorting="True" 
        DataKeyNames="cus_id"
        DataSourceID="ObjectDataSource2" 
        CellPadding="4" ForeColor="#333333"
        CssClass="gridview" 
        PageSize="5" 
        AutoGenerateColumns="False" 
        Font-Size="Medium" >
           <PagerSettings FirstPageText="First" LastPageText="Last" 
               Mode="NumericFirstLast" />
        <RowStyle BackColor="#EFF3FB" />
        <Columns>
       
            <asp:CommandField ShowEditButton="True" />
            
               <asp:TemplateField HeaderText="Date Submited" 
                SortExpression="appointment_date">
             <ItemTemplate><%#Eval("date_submited", "{0:M/dd/yyyy}")%>
             </ItemTemplate>
             </asp:TemplateField>  
     <asp:TemplateField SortExpression="first_name" HeaderText="First Name">
    <ItemTemplate><%#Eval("first_name")%></ItemTemplate>
    <EditItemTemplate><asp:Label id="first_name" Font-Bold=true runat="server" Text='<%# Eval("first_name") %>' />
    </EditItemTemplate>  
      </asp:TemplateField> 
   
   <asp:TemplateField SortExpression="last_name" HeaderText="Last Name">
   <ItemTemplate><%#Eval("last_name")%></ItemTemplate>
    <EditItemTemplate><asp:Label id="last_name" Font-Bold=true runat="server" Text='<%# Eval("last_name") %>' />
    </EditItemTemplate>  
       </asp:TemplateField> 

            
            <asp:BoundField DataField="cell_phone" HeaderText="Cell#" 
                SortExpression="cell_phone" />

     <asp:TemplateField SortExpression="email" HeaderText="Email">
    <ItemTemplate><%#Eval("email")%></ItemTemplate>
    <EditItemTemplate><asp:TextBox id="email" runat="server" Columns="20" Text='<%# Bind("email") %>'></asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateField>   

            <asp:BoundField DataField="status" HeaderText="Status"
                SortExpression="status" ReadOnly=true />
            
             <asp:BoundField DataField="paid" HeaderText="Paid"
                SortExpression="paid" ReadOnly=true />                                                 
                
            <asp:TemplateField HeaderText="Appointment Date" 
                SortExpression="appointment_date">
             <ItemTemplate><%#Eval("appointment_date", "{0:M/dd/yyyy}")%>
             </ItemTemplate>
             <EditItemTemplate>
             <asp:TextBox runat="server" id="textbox1" Text='<%# Bind("appointment_date") %>'></asp:TextBox>
             <cc1:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="textbox1" PopupButtonID="textbox1"></cc1:CalendarExtender>
             </EditItemTemplate>
             </asp:TemplateField>
             <asp:BoundField DataField="notes" HeaderStyle-Width="15px" HeaderText="Notes"
                SortExpression="Notes" >

<HeaderStyle Width="15px"></HeaderStyle>
            </asp:BoundField>

           </Columns>
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#507CD1" Font-Bold="True" ForeColor="#333333" 
               Wrap="False" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#99CCFF" Wrap="True" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>

This seems very simple to do and it is driving me crazy. I thought about just setting the UserID to a session variable and pulling it that way but I would prefer to do it using the correct asp.net parameters.

Thank you once again!

[jimib]

How is the user logged in? If you are using the asp.net login control you can refer to his user id as user.identity.name
hope it helps

[shawn.bordeaux]

The user is logged in using the User Control. I've messed with it a bit and now no longer have an error however no results are displaying, i tested the query in the table adaptor using a valid userid that is stored in the aspnet_users table and it displayed the correct results. Here is the sub in my code behind file;

Protected Sub ObjectDataSource2_Selecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
        e.Command.Parameters("@UserID").Value = User.Identity.Name.ToString()
    End Sub

This is the select query from the table adaptor

SELECT appointment_date, bd_id, cell_phone, cus_id, date_submited, email, first_name, last_name, location, notes, other_phone, paid, status 
FROM dbo.customer_entry 
WHERE (bd_id = @UserID) 
ORDER BY date_submited

I have fill a data table selected and it is named FillByUserID
I have return a data table selected and it is named GetDataByUserID
Here is the code for my data source in my design page;

<asp:ObjectDataSource ID="ObjectDataSource2" runat="server" 
        DeleteMethod="Delete" InsertMethod="Insert" 
        OldValuesParameterFormatString="original_{0}" SelectMethod="FillByUserID" 
        TypeName="customer_entryTableAdapters.customer_entryTableAdapter" 
        UpdateMethod="Update">
        <SelectParameters>
       <asp:Parameter Name="UserID" />
       </SelectParameters>
        <DeleteParameters>
            <asp:Parameter Name="Original_cus_id" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="first_name" Type="String" />
            <asp:Parameter Name="last_name" Type="String" />
            <asp:Parameter Name="cell_phone" Type="String" />
            <asp:Parameter Name="other_phone" Type="String" />
            <asp:Parameter Name="status" Type="String" />
            <asp:Parameter Name="paid" Type="String" />
            <asp:Parameter Name="date_submited" Type="DateTime" />
            <asp:Parameter Name="notes" Type="String" />
            <asp:Parameter Name="email" Type="String" />
            <asp:Parameter Name="location" Type="String" />
            <asp:Parameter Name="appointment_date" Type="String" />
            <asp:Parameter Name="Original_cus_id" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="first_name" Type="String" />
            <asp:Parameter Name="last_name" Type="String" />
            <asp:Parameter Name="cell_phone" Type="String" />
            <asp:Parameter Name="other_phone" Type="String" />
            <asp:Parameter Name="status" Type="String" />
            <asp:Parameter Name="paid" Type="String" />
            <asp:Parameter Name="date_submited" Type="DateTime" />
            <asp:Parameter Name="notes" Type="String" />
            <asp:Parameter Name="email" Type="String" />
            <asp:Parameter Name="location" Type="String" />
            <asp:Parameter Name="appointment_date" Type="String" />
        </InsertParameters>
    </asp:ObjectDataSource>

And last here is my gridview from the design page.

<asp:GridView ID="GridView1" 
        runat="server" 
        AllowPaging="True" 
        AllowSorting="True" 
        DataKeyNames="cus_id"
        DataSourceID="ObjectDataSource2" 
        CellPadding="4" ForeColor="#333333"
        CssClass="gridview" 
        PageSize="5" 
        AutoGenerateColumns="False" 
        Font-Size="Medium" >
           <PagerSettings FirstPageText="First" LastPageText="Last" 
               Mode="NumericFirstLast" />
        <RowStyle BackColor="#EFF3FB" />
        <Columns>
       
            <asp:CommandField ShowEditButton="True" />
            
               <asp:TemplateField HeaderText="Date Submited" 
                SortExpression="appointment_date">
             <ItemTemplate><%#Eval("date_submited", "{0:M/dd/yyyy}")%>
             </ItemTemplate>
             </asp:TemplateField>  
     <asp:TemplateField SortExpression="first_name" HeaderText="First Name">
    <ItemTemplate><%#Eval("first_name")%></ItemTemplate>
    <EditItemTemplate><asp:Label id="first_name" Font-Bold=true runat="server" Text='<%# Eval("first_name") %>' />
    </EditItemTemplate>  
      </asp:TemplateField> 
   
   <asp:TemplateField SortExpression="last_name" HeaderText="Last Name">
   <ItemTemplate><%#Eval("last_name")%></ItemTemplate>
    <EditItemTemplate><asp:Label id="last_name" Font-Bold=true runat="server" Text='<%# Eval("last_name") %>' />
    </EditItemTemplate>  
       </asp:TemplateField> 

            
            <asp:BoundField DataField="cell_phone" HeaderText="Cell#" 
                SortExpression="cell_phone" />

     <asp:TemplateField SortExpression="email" HeaderText="Email">
    <ItemTemplate><%#Eval("email")%></ItemTemplate>
    <EditItemTemplate><asp:TextBox id="email" runat="server" Columns="20" Text='<%# Bind("email") %>'></asp:TextBox>
    </EditItemTemplate>
    </asp:TemplateField>   

            <asp:BoundField DataField="status" HeaderText="Status"
                SortExpression="status" ReadOnly=true />
            
             <asp:BoundField DataField="paid" HeaderText="Paid"
                SortExpression="paid" ReadOnly=true />                                                 
                
            <asp:TemplateField HeaderText="Appointment Date" 
                SortExpression="appointment_date">
             <ItemTemplate><%#Eval("appointment_date", "{0:M/dd/yyyy}")%>
             </ItemTemplate>
             <EditItemTemplate>
             <asp:TextBox runat="server" id="textbox1" Text='<%# Bind("appointment_date") %>'></asp:TextBox>
             <cc1:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="textbox1" PopupButtonID="textbox1"></cc1:CalendarExtender>
             </EditItemTemplate>
             </asp:TemplateField>
             <asp:BoundField DataField="notes" HeaderStyle-Width="15px" HeaderText="Notes"
                SortExpression="Notes" >

<HeaderStyle Width="15px"></HeaderStyle>
            </asp:BoundField>

           </Columns>
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#507CD1" Font-Bold="True" ForeColor="#333333" 
               Wrap="False" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#99CCFF" Wrap="True" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>

Is there anyone out there who can tell me where I am going wrong here? How can I easily pull the userid once the user is logged in and reference it with the gridview I am trying to display on their home page?
Thanks!
Shawn

[jimib]

I am trying to do something similar, so I may have as many questions as answers. Try something like WHERE (bd_id = OneUserID) where OneUserID is a legitamite user id and see if the problem is really your userid parameter, or maybe something else.

[shawn.bordeaux]

I have it working now... I kept playing with it and figured it out somehow. Thanks to a lot of help from around the world.

Basically I went to the data source in design view, double clicked on selecting which loaded a sub in my code behind page where I declared the @username parameter.

My code behind Sub;

Protected Sub ObjectDataSource2_Selecting1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) Handles ObjectDataSource2.Selecting e.InputParameters("UserID") = Membership.GetUser.ProviderUserKey.ToString 
End Sub

I also had to define @userid in the data source select parameter;

<SelectParameters>

<asp:Parameter Name="UserID" />

</SelectParameters>

It works like a champ now!

Best regards,
Shawn