Posts by shawn.bordeaux

I have a web application for recording time, bonuses and commissions for employees. I am having a hard time joining three tables together.

Here are the tables:

employee - provides employee information including the primary key emp_id
emp_timecard - provides recorded time from employees
tc_bon_com - provides bonuses and commissions for employee

I join employee with emp_timecard by joining on emp_id I then use a where statement to select only the records that are between a certain date range. This part works fine but where I run into a problem is when I throw the commission and bonus table into the mix. I need it to join the other tables using the emp_id field but I also need to only pull records that are in the pay_end_date range of the emp_timecard.

Here is my statement, what is happening now is that it is selecting records outside the date range;

sql_select = "" & _ 
" SELECT " & _
" E.first_name, E.last_name, E.userid, E.employee_status, E.filenumber, E.paygroup, C.date, C.day_in, " & _
" C.day_out, E.emp_id, E.employee_type, C.meal_out, C.meal_in, C.pto_hours, C.hol_hours, C.ber_hours, " & _
" C.chb_hours, C.special_hours, C.hours_worked, E.supervisorname, E.term_date, E.user_type_id, " & _
" T.bonus, T.commission, T.status " & _
" FROM employee E " & _ 
" LEFT JOIN emp_timecard C ON " & _
" E.userid=C.userid " & _
" LEFT JOIN tc_bon_com T ON " & _
" E.emp_id = T.emp_id " & _
" AND C.date BETWEEN '"&session("payp_week1_start")&"' AND …

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

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 …

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" …

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!

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!

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

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!

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

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" /> …