1 Replies - 1269 Views - Last Post: 04 September 2013 - 01:48 AM Rate Topic: -----

#1 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Using Like statment from a DDL in sqlDatasource property in Gridview

Posted 03 September 2013 - 05:48 PM

Good day

I am working in vb.net asp.net and are struggling with something.

What I have is a grid-view

     <asp:GridView ID="gvTask" runat="server" AutoGenerateColumns="False" DataSourceID="sqlTask"
                        PageSize="5" Width="700px">
                        <Columns>
                            <asp:TemplateField HeaderText="Date">
                                <ItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("WF_date", "{0:d}") %>'></asp:Label>
                                </ItemTemplate>
                                <ItemStyle Width="70px" />
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="ColID" Visible="False">
                                <ItemTemplate>
                                    <asp:Label ID="lblTask" runat="server" Text='<%# Eval("WF_ID") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField DataField="Step_Text" HeaderText="Task" SortExpression="Step_Text" />
                            <asp:TemplateField>
                                <ItemTemplate>
                                    <asp:ImageButton ID="cmdView" runat="server" CommandName="View" CommandArgument='<%# Eval("Step_Form") %>'
                                        ImageUrl="~/Images/Details.GIF" />
                                </ItemTemplate>
                                <ItemStyle HorizontalAlign="Center" Width="70px" />
                            </asp:TemplateField>
                        </Columns>
                        <HeaderStyle CssClass="grdhead" />
                        <AlternatingRowStyle CssClass="grdalt" />
                    </asp:GridView>
                    <asp:SqlDataSource ID="sqlTask" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>"
                        ProviderName="<%$ ConnectionStrings:ApplicationServices.ProviderName %>" 
                        SelectCommand="SELECT lms_WF.WF_ID, lms_WF.WF_Date, lms_WF.Item_Type, lms_WF_Step.WF_Step_ID, lms_WF_Step.Step_Order, [font="Arial Black"][size="5"]lms_WF_Step.Step_Text[/size][/font],
lms_WF_Step.Step_User, lms_WF_Step.Step_Active, lms_WF_Step.Step_Complete, lms_WF.Interv_Stat_ID, lms_WF_Step.Step_Type, lms_WF_Step.Step_Form FROM lms_WF_Step INNER JOIN lms_WF ON lms_WF_Step.WF_ID = lms_WF.WF_ID WHERE (lms_WF_Step.Step_Active = 1) AND (lms_WF_Step.Step_Complete = 0) AND (lms_WF_Step.Step_User = @Emp) AND (lms_WF_Step.Step_Type <> N'Approve')">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="lblEmpID" Name="Emp" PropertyName="Text" />
                        </SelectParameters>
                    </asp:SqlDataSource>



All the data display very nicely so its all ok.

I put in a drop down-box that select the value of a schedule name like annual 2013 the drop-down works perfectly

What I want to achieve is I want to filter my results from the grid-view via selected text in the drop down-box.

In the grid-view by the SelectCommand I have a column named Step_Text bolted in code above. This text exist off User plus schedule name like Prepare annual 2013 for Jan Van Zyl

So If I click on the drop down-box and select a schedule name the results in the grid-view must filter via selected text in a like statement because of the rest of the text that doesn't exist in the drop-down.

Like this
Select * FROM lms_WF_Step INNER JOIN lms_WF ON lms_WF_Step.WF_ID = lms_WF.WF_ID WHERE (lms_WF_Step.Step_Active = 1) AND (lms_WF_Step.Step_Complete = 0) AND (lms_WF_Step.Step_User = @Emp) AND (lms_WF_Step.Step_Type <> N'Approve') [b]and Step_Text like '%" & DROPDOWNVALUE & "%'[/b]


If anyone can please assist me on how to do this using the asp:SqlDataSource Filter Properties or easy way to do this would be great.

Thanks a million.

Is This A Good Question/Topic? 0
  • +

Replies To: Using Like statment from a DDL in sqlDatasource property in Gridview

#2 Thinus du Pisanie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 107
  • Joined: 07-October 11

Re: Using Like statment from a DDL in sqlDatasource property in Gridview

Posted 04 September 2013 - 01:48 AM

I Found the solution after a while of searching the net.

 SelectCommand="SELECT lms_WF.WF_ID, lms_WF.WF_Date, lms_WF.Item_Type, lms_WF_Step.WF_Step_ID, lms_WF_Step.Step_Order, lms_WF_Step.Step_Text, lms_WF_Step.Step_User, lms_WF_Step.Step_Active, lms_WF_Step.Step_Complete, lms_WF.Interv_Stat_ID, lms_WF_Step.Step_Type, lms_WF_Step.Step_Form FROM lms_WF_Step INNER JOIN lms_WF ON lms_WF_Step.WF_ID = lms_WF.WF_ID WHERE (lms_WF_Step.Step_Active = 1) AND (lms_WF_Step.Step_Complete = 0) AND (lms_WF_Step.Step_User = @Emp) AND (lms_WF_Step.Step_Type <> N'Approve')" 
                        FilterExpression="Step_Text like '%{0}%'">
                        <FilterParameters>
                            <asp:ControlParameter ControlID="dropRevschedfinal" Name="SchedName" 
                                PropertyName="SelectedValue" Type="String"/>
                        </FilterParameters>
                        <SelectParameters>
                            <asp:ControlParameter ControlID="lblEmpID" Name="Emp" PropertyName="Text" />
                        </SelectParameters>
                    </asp:SqlDataSource>



Note the FilterExpression put in
And then just Declaring the FilterParameters

The Step_Text is my column name in the FilterExpression and the {0} is the first value of my Dropdownlist

Thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1