how to access data from sql and show in datalist at runtime

Question

1.00/5 (1 vote)

See more:

hi i am using datalist in asp.net page,that display data from SQL DB at design time.
datalist show those candidate who live in Punjab state,but now after that i want more sorting in that data, i have a dropdownlist that display punjab state city,when user select a sepecific city
datalist show only that city candidate.

plz help

till i am using this code

XML

<div id="dr1" style="width: 300px; padding-bottom: 1px; height: 630px; overflow: scroll">
             <asp:UpdatePanel ID="UpdatePanel3" runat="server">
           <ContentTemplate>
                       <asp:DataList ID="DataList1" runat="server" CellPadding="4" RepeatLayout="Flow" DataSourceID="SqlDataSource1"
                           ForeColor="#333333" Width="300px" RepeatColumns="2" RepeatDirection="Horizontal">
                           <AlternatingItemStyle BackColor="White" ForeColor="#284775" />
                           <FooterStyle Font-Bold="True" ForeColor="White" BackColor="#5D7B9D" />
                           <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#5D7B9D" />
                           <ItemStyle ForeColor="#333333" BackColor="#F7F6F3" />
                           <ItemTemplate>
                               <a class='iframe' href="my-business.aspx?nm='<%# Eval("Name") %>'">
                                   <div class="card" style="min-height: 50px;">
                                       <div class="fl">
                                          <asp:Image ID="Image2" runat="server" Style="border: 1px solid #C6C6C6; float: left;
                                               height: 50px; border-radius: 3px; width: 50px;" ImageUrl='<%# "AdminGetImage.aspx?id=" + Eval("id")%>' />
                                       </div>
                                       <div class="fl" style="margin-left: 7px; width: 215px;">
                                           <div>
                                               <asp:Label ID="Sent_FromLabel" Font-Size="10px" Style="text-transform: capitalize;
                                                   font-weight: bold;" Font-Names="Verdana" ForeColor="#014265" runat="server" Text='<%# Eval("Name") %>' />
                                           </div>
                                           <div>
                                               <div>
                                                   <asp:Label ID="label3" runat="server" Font-Names="Verdana" ForeColor="#014265" Font-Size="10px"
                                                       Text='<%# Eval("Address") %>' />
                                               </div>
                                               <div style="margin-top: 2px;">
                                                   <asp:Label ID="Label1" Font-Size="10px" Font-Names="Verdana" ForeColor="#014265"
                                                       runat="server" Text='<%# Eval("Mobile") %>' />,
                                                   <asp:Label ID="Label4" Font-Size="10px" Font-Names="Verdana" ForeColor="#014265"
                                                       runat="server" Text='<%# Eval("Office") %>' />
                                               </div>
                                               <div>
                                                   <asp:Label ID="Label2" runat="server" Text='<%# Eval("claim_status") %>' class="claim"></asp:Label>
                                               </div>
                                           </div>
                                       </div>
                                       <div class="clr">
                                       </div>
                                   </div>
                               </a>
                           </ItemTemplate>
                           <SelectedItemStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                       </asp:DataList>
                   </ContentTemplate>
               </asp:UpdatePanel>
           </div>

XML

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyArea %>"
    SelectCommand="Sp_State_candidate" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:QueryStringParameter Name="state" QueryStringField="id" Type="String" />
     </SelectParameters>
</asp:SqlDataSource>

Posted 19-Apr-13 20:15pm

BALBINDER4u

Updated 19-Apr-13 20:17pm

v2

Add a Solution

Comments

RedDk 22-Apr-13 14:14pm

There is no query block in the above code. See the difference, as posted in Solution 01 or Solution 02. Solution 04 is essentially repost and etc ...

4 solutions

Solution 1

XML

<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"
       DataTextField="city_name" DataValueField="city_id" AutoPostBack="true" BackColor="IndianRed"
       ForeColor="Snow">
   </asp:DropDownList>
   <asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT City_Id, City_Name FROM City"
       ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"></asp:SqlDataSource>

XML

<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource2">
        <ItemTemplate>
            <ul>
                <li>name:
                    <asp:Label ID="nameLabel" runat="server" Text='<%# Eval("name") %>' /></li>
                <li>category:
                    <asp:Label ID="categoryLabel" runat="server" Text='<%# Eval("category") %>' /></li>

                <li>City_Id:
                    <asp:Label ID="City_IdLabel" runat="server" Text='<%# Eval("City_Id") %>' /></li>
            </ul>

        </ItemTemplate>
    </asp:DataList>
     <asp:SqlDataSource ID="SqlDataSource2" runat="server" DataSourceMode="DataReader"
        SelectCommand="SELECT * FROM [Products] WHERE ([City_Id] = @City_Id)" ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>">
        <SelectParameters>
            <asp:ControlParameter Name="City_Id" ControlID="DropDownList1" PropertyName="SelectedValue"
                DefaultValue="0" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

Posted 19-Apr-13 21:58pm

JaffreenaMichael

Updated 19-Apr-13 21:59pm

v2

Comments

BALBINDER4u 22-Apr-13 5:35am

both answer are wrong.
plz read question carefully?

on the page load datalist show all candidate and after button click show only those whose belong to particular city ?

Plz...................

Solution 2

XML

Collapse | Copy Code
<asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource2">
        <ItemTemplate>
            <ul>
                <li>name:
                    <asp:Label ID="nameLabel" runat="server" Text='<%# Eval("name") %>' /></li>
                <li>category:
                    <asp:Label ID="categoryLabel" runat="server" Text='<%# Eval("category") %>' /></li>

                <li>City_Id:
                    <asp:Label ID="City_IdLabel" runat="server" Text='<%# Eval("City_Id") %>' /></li>
            </ul>

        </ItemTemplate>
    </asp:DataList>
     <asp:SqlDataSource ID="SqlDataSource2" runat="server" DataSourceMode="DataReader"
        SelectCommand="SELECT * FROM [Products] WHERE ([City_Id] = @City_Id)" ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>">
        <SelectParameters>
            <asp:ControlParameter Name="City_Id" ControlID="DropDownList1" PropertyName="SelectedValue"
                DefaultValue="0" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

Posted 20-Apr-13 0:07am

samir_sam

Comments

BALBINDER4u 22-Apr-13 5:35am

both answer are wrong .
Plz read question carefully.

on the page load datalist show all candidate and after that button click, datalist show only particular city candidate.

Plz >>>>>>>>>>>>>>>

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

JaffreenaMichael · Accepted Answer · 2013-04-22T03:11:00

u have to add 2 sql datasources one without where condition and the other with where condition. Then on dropdown selection changed u should change sqldatasource id then it works as u wish

XML

<asp:SqlDataSource ID="SqlDataSource2" runat="server" DataSourceMode="DataReader"
        SelectCommand="SELECT * FROM [Products]" ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>">
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource3" runat="server" DataSourceMode="DataReader"
        SelectCommand="SELECT * FROM [Products] WHERE ([City_Id] = @City_Id)" ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>">
        <SelectParameters>
            <asp:ControlParameter Name="City_Id" ControlID="DropDownList1" PropertyName="SelectedValue"
                DefaultValue="0" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

-------------------------------------------
in .cs of dropdown chage
-------------------------------------------

C#

protected void DropDownList1_OnSelectedIndexChanged(object sender, EventArgs e)
   {
       DataList1.DataSourceID = "SqlDataSource3";
   }

BALBINDER4u · Accepted Answer · 2013-04-22T18:39:00

I think this code work also !

Design Mode

XML

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyArea %>"
                SelectCommand="Select id,name,address,mobile,office,catg_ID,claim_status,keyword from famous">
            </asp:SqlDataSource>


Code File

<pre lang="cs">protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
   {

       try
       {

           string str = DropDownList1.SelectedItem.ToString();
           Session[&quot;tblnm&quot;] = str;
           SqlDataSource1.SelectCommand = &quot;myarea1&quot;;
           SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
           SqlDataSource1.SelectParameters.Add(&quot;tablename&quot;, TypeCode.String, str);
           str = &quot;&quot;;

       }</pre>



myarea1 is a  DB SP Name

<pre lang="sql">create procedure [dbo].[myarea1](@cityname varchar(250))
as
begin
EXEC(&#39;Select  id,name,address,mobile,office,catg_ID,claim_status,keyword from &#39;+@cityname)
end</pre>

how to access data from sql and show in datalist at runtime

4 solutions

Solution 1

Solution 2

Solution 4

Solution 5

Add your solution here

Preview 0