Select Command - Sql DataSource

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 --%>

Answer 1

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

Answer 2

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.)