locked
Select Command - Sql DataSource RRS feed

  • Question

  • User-158363518 posted

    hi every body

    Select Command :

    SELECT stInfo.stId, stInfo.stName, stInfo.stName+'  '+stInfo.stFamily as nf, stInfo.stFamily, stInfo.stimage FROM Field INNER JOIN  Course ON Field.idField = Course.idField INNER JOIN  stInfo INNER JOIN  Class ON stInfo.stClass = Class.idClass ON Course.idField = stInfo.stField  where stClass='" + Session["gradlistkls"] + "' and Course.idCourse='" + Session["gradlistdrs"] + "'

    SELECT stInfo.stId, stInfo.stName, stInfo.stName+'  '+stInfo.stFamily as nf, stInfo.stFamily, stInfo.stimage
    FROM Field INNER JOIN Course ON Field.idField = Course.idField
    INNER JOIN stInfo INNER JOIN Class ON stInfo.stClass = Class.idClass ON Course.idField = stInfo.stField
    where stClass='" + Session["gradlistkls"] + "' and Course.idCourse='" + Session["gradlistdrs"] + "'

    I used Session to Filter Select command.(Session["gradlistdrs"]       and        Session["gradlistkls"] )

    How to write it in the Sql Data Source ?


                                            enter family  : 
                                            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                                            <input type="submit" id="Submit1" value="search" runat="server" />
                                            <br />
                                            <br />
                                            <asp:GridView ID="GridView1"
                                                DataSourceID="SqlDataSource2"
                                                CellPadding="4"
                                                AllowPaging="True" PageSize="5"
                                                AutoGenerateColumns="false"
                                                OnRowCreated="GridView1_RowCreated"
                                                Width="100%"
                                                DataKeyNames="stId"
                                                runat="server" ForeColor="#333333" GridLines="None">
    
    
                                                <AlternatingRowStyle BackColor="White" />
                                                <Columns>
                                                    <asp:CommandField ShowSelectButton="true" ButtonType="Button" SelectText="select" ItemStyle-Width="100px" />
                                                    <asp:TemplateField ItemStyle-Width="100px" HeaderText="stid ">
                                                        <ItemTemplate>
                                                            <asp:Label ID="lblId" runat="server"
                                                                Text='<%# Eval("stId")%>'></asp:Label>
                                                        </ItemTemplate>
                                                    </asp:TemplateField>
                                                    <asp:TemplateField ItemStyle-Width="200px" HeaderText="name">
                                                        <ItemTemplate>
                                                            <asp:Label ID="lblDate" runat="server"
                                                                Text='<%# Eval("stName")%>'></asp:Label>
                                                        </ItemTemplate>
    
                                                    </asp:TemplateField>
                                                    <asp:TemplateField ItemStyle-Width="200px" HeaderText="family">
    
                                                        <ItemTemplate>
                                                            <asp:Label ID="lblDate" runat="server"
                                                                Text='<%# Eval("stFamily")%>'></asp:Label>
                                                        </ItemTemplate>
    
                                                    </asp:TemplateField>
                                                    <asp:TemplateField ItemStyle-Width="200px" HeaderText="NCode">
    
                                                        <ItemTemplate>
                                                            <asp:Label ID="lblDate" runat="server"
                                                                Text='<%# Eval("stNC")%>'></asp:Label>
                                                        </ItemTemplate>
    
                                                    </asp:TemplateField>
                                                </Columns>
                                                <EditRowStyle BackColor="#2461BF" />
                                                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                                                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                                                <RowStyle BackColor="#EFF3FB" />
                                                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                                                <SortedAscendingCellStyle BackColor="#F5F7FB" />
                                                <SortedAscendingHeaderStyle BackColor="#6D95E1" />
                                                <SortedDescendingCellStyle BackColor="#E9EBEF" />
                                                <SortedDescendingHeaderStyle BackColor="#4870BE" />
    
    
                                            </asp:GridView>
                                            <asp:SqlDataSource ID="SqlDataSource2" runat="server"   ConnectionString="<%$ ConnectionStrings:madrese %>" SelectCommand="SELECT stInfo.stId, stInfo.stName, stInfo.stName+'  '+stInfo.stFamily as nf, stInfo.stFamily, stInfo.stimage FROM Field INNER JOIN  Course ON Field.idField = Course.idField INNER JOIN  stInfo INNER JOIN  Class ON stInfo.stClass = Class.idClass ON Course.idField = stInfo.stField  where stClass='" + <%# Session["gradlistkls"] %> + "' and Course.idCourse='" + <%# Session["gradlistdrs"] %> + "'" FilterExpression="[stFamily] LIKE '%{0}%'">
                                                <FilterParameters>
                                                    <asp:ControlParameter Name="stFamily"
                                                        ControlID="TextBox1" PropertyName="Text" />
                                                </FilterParameters>
                                            </asp:SqlDataSource>
                                            <br />
    
    
    
                                            <%--LABEL TO SHOW ROW COUNT.--%>
                                            <div style="clear: both; padding: 10px 0;">
                                                <label id="Label1" runat="server"></label>
                                            </div>
                                            <%-- end GridView --%>

    Saturday, May 21, 2016 10:59 AM

Answers

  • User2103319870 posted

    Since you are having concatenating operation inside the query, IMHO best option is to use StoredProcedure and then use SessionParameters in SQLDatasource

     <asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:madrese %>"
                SelectCommand="Provide Your SP Name" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:SessionParameter Name="stClass" SessionField="gradlistkls"
                        DefaultValue="" />
                    <asp:SessionParameter Name="Course" SessionField="gradlistdrs"
                        DefaultValue="" />
                </SelectParameters>
            </asp:SqlDataSource>

    Sample SP

    CREATE PROCEDURE YourStoredProcname 
    	-- Add the parameters for the stored procedure here
    	@stClass Varchar(100),
    	@Course Varchar(100)
    AS
    BEGIN
        -- Select statements for procedure here
    	SELECT stInfo.stId, stInfo.stName, stInfo.stName+'  '+stInfo.stFamily as nf, stInfo.stFamily, stInfo.stimage
         FROM Field INNER JOIN  Course ON Field.idField = Course.idField
         INNER JOIN  stInfo INNER JOIN  Class ON stInfo.stClass = Class.idClass ON Course.idField = stInfo.stField
          where stClass =@stClass and Course.idCourse=@Course
    END
    GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 21, 2016 12:02 PM

All replies

  • User2103319870 posted

    Since you are having concatenating operation inside the query, IMHO best option is to use StoredProcedure and then use SessionParameters in SQLDatasource

     <asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:madrese %>"
                SelectCommand="Provide Your SP Name" SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:SessionParameter Name="stClass" SessionField="gradlistkls"
                        DefaultValue="" />
                    <asp:SessionParameter Name="Course" SessionField="gradlistdrs"
                        DefaultValue="" />
                </SelectParameters>
            </asp:SqlDataSource>

    Sample SP

    CREATE PROCEDURE YourStoredProcname 
    	-- Add the parameters for the stored procedure here
    	@stClass Varchar(100),
    	@Course Varchar(100)
    AS
    BEGIN
        -- Select statements for procedure here
    	SELECT stInfo.stId, stInfo.stName, stInfo.stName+'  '+stInfo.stFamily as nf, stInfo.stFamily, stInfo.stimage
         FROM Field INNER JOIN  Course ON Field.idField = Course.idField
         INNER JOIN  stInfo INNER JOIN  Class ON stInfo.stClass = Class.idClass ON Course.idField = stInfo.stField
          where stClass =@stClass and Course.idCourse=@Course
    END
    GO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 21, 2016 12:02 PM
  • User-158363518 posted

    thanks a lot .

    I Execute Select CMD In sql And I saw results .

    I do not know why the information is not loading !!

    The session is full .

    (But I wrote it all in code behind.)

    Saturday, May 21, 2016 7:26 PM