Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET SQL-Server
my asp code:
<%@ Page Title="" Language="C#" MasterPageFile="~/grid.master" AutoEventWireup="true" CodeFile="yearwiseex.aspx.cs" Inherits="Default2" %>
 
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>
 
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <asp:Label ID="Label1" runat="server" Text="Select Project Code"></asp:Label>
&nbsp;
&nbsp;
<asp:DropDownList ID="DropDownList1" runat="server"
       Height="20px" Width="130px"
       onselectedindexchanged="DropDownList1_SelectedIndexChanged"
        AutoPostBack="True"> </asp:DropDownList>
 
    <asp:Label ID="Label2" runat="server" Text="Select Financial Year"></asp:Label>
    &nbsp;&nbsp;
&nbsp;&nbsp;
<asp:DropDownList ID="DropDownList2" runat="server"
        Height="20px" Width="130px"></asp:DropDownList>
 

    <asp:Button ID="Button1" runat="server" Text="GO" onclick="Button1_Click" />
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
    <br />
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
        AllowSorting="True" AutoGenerateColumns="False" DataSourceID="sds"
        Width="775px">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
            <asp:BoundField DataField="pcode" HeaderText="Project Code"
                SortExpression="pcode" />
            <asp:BoundField DataField="fyyear" HeaderText="Financial year"
                SortExpression="fyyear" />
            <asp:BoundField DataField="date" HeaderText="Date" SortExpression="date" />
            <asp:BoundField DataField="salary" HeaderText="Salary"
                SortExpression="salary" />
            <asp:BoundField DataField="ta" HeaderText="TA" SortExpression="ta" />
            <asp:BoundField DataField="contigency" HeaderText="Contingency"
                SortExpression="contigency" />
            <asp:BoundField DataField="nrc" HeaderText="NRC" SortExpression="nrc" />
            <asp:BoundField DataField="institcharges" HeaderText="Institutional Charges"
                SortExpression="institcharges" />
            <asp:BoundField DataField="others" HeaderText="others"
                SortExpression="others" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="sds" runat="server"
        ConnectionString="<%$ ConnectionStrings:connstr %>"
        SelectCommand="SELECT [pcode], [fyyear], [date], [salary], [ta], [contigency], [nrc], [institcharges], [others] FROM [monthly] WHERE ([pcode] = @pcode)">
        <SelectParameters>
            <asp:Parameter Name="pcode" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
    </asp:Content>
 

 

my code behind file where i havent done much i have just populated two dropdownlists and i m struggling with how to pass the values of dropdownlist which will act as filter for displaying data in gridview:
Posted 5-Nov-12 23:46pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

When you are creating the datasource of the gridview you can click the "where" button, here you can assign the dropdown as a where clause.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Instead of binding gridview in design page try by binding itin code behind, on click of submit button
 
protected void btnSubmit_Click(object sender, EventArgs e) {
 
Conn.Open();
Cmd = new SqlCommand("select * from Tablename where colum1=@value1 and column2=@value2", Conn);
Cmd.Parameters.Add("@value1", SqlDbType.datatype).Value = dropdownlist1.selectedvalue
Cmd.Parameters.Add("@value2", SqlDbType.datatype).Value = dropdownlist2.selectedvalue
da = new SqlDataAdapter(Cmd);
da.Fill(ds, "Tablename");
GridView1.DataSource = ds;
Conn.Close();

 
}
  Permalink  
Comments
a2ulthakur at 6-Nov-12 8:13am
   
wat do i have to write in value1 and value2??
Anusha SR at 6-Nov-12 23:22pm
   
I think i had clearly mentioned the values for value1 and value2:
 
Cmd.Parameters.Add("@value1", SqlDbType.datatype).Value = dropdownlist1.selectedvalue
Cmd.Parameters.Add("@value2", SqlDbType.datatype).Value = dropdownlist2.selectedvalue
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

in your codebehind.cs go to Button1_Click event inside it..
 
add code as...
 
sds.selectcommand="SELECT [pcode], [fyyear], [date], [salary], [ta], [contigency], [nrc], [institcharges], [others] FROM [monthly] WHERE ([pcode] = "+DropDownList1.selectedvalue.tostring()+") and ([fyyear]="+DropDownList2.selectedvalue.tostring()+")";

 
because when u edit the selectcommand it automatically populates the sqlds..and databind it..following databind the gridview..
 
sds.databind();
grid.databind();
 
it works..but one thing google it properly..u'll find no of examples..
  Permalink  
Comments
a2ulthakur at 6-Nov-12 10:48am
   
m getting this error
Source Error:
 

Line 83: sds.SelectCommand = "SELECT [pcode], [fyyear], [date], [salary], [ta], [contigency], [nrc], [institcharges], [others] FROM [monthly] WHERE ([pcode] = " + DropDownList1.SelectedValue.ToString() + ") and ([fyyear]=" + DropDownList2.SelectedValue.ToString() + ")";
Line 84: sds.DataBind();
Line 85: GridView1.DataBind();
Line 86:
Line 87: }
a2ulthakur at 6-Nov-12 10:54am
   
one more thing this is happening when m selecting values from dropdownlist and then hitting the go button
a2ulthakur at 6-Nov-12 22:08pm
   
my problem still remains ...when i click my button nothing happens :(
Nandakishorerao at 6-Nov-12 23:11pm
   
what is the datatype of pcode and fyyear...if both are varchar..then alter the code like this..
 
sds.SelectCommand = "SELECT [pcode], [fyyear], [date], [salary], [ta], [contigency], [nrc], [institcharges], [others] FROM [monthly] WHERE ([pcode] = '" + DropDownList1.SelectedValue.ToString() + "') and ([fyyear]='" + DropDownList2.SelectedValue.ToString() + "')";
 
if u get any error related to sql queries..better pate the code in ssms and try u'll find out the problem...
Anusha SR at 6-Nov-12 23:24pm
   
I suggest you to use breakpoint and check where you get the error
a2ulthakur at 8-Nov-12 1:03am
   
can anyone tell me whats happening here ...when i run my page using the query that is being mentioned here by "Nandakishorerao" the page is showing the gridview but when i select values from dropdownlist i dont get any result....the gridview is not showing ...
Nandakishorerao at 8-Nov-12 1:41am
   
are u sure the values u are selecting from dropdownlist is passing to the datasource...debug and check it...paste the codebehind....code..here...atleast v can suggest something..
a2ulthakur at 8-Nov-12 4:49am
   
here is the thing i will show u the code any ya both fyyear and pcode are string (varchar)values:
 
aspx code
 
<asp:DropDownList ID="DropDownList1" runat="server"
Height="20px" Width="130px"
onselectedindexchanged="DropDownList1_SelectedIndexChanged"
AutoPostBack="True">
 
<asp:Label ID="Label2" runat="server" Text="Select Financial Year">
  
  
<asp:DropDownList ID="DropDownList2" runat="server"
Height="20px" Width="130px" AutoPostBack="True"
onselectedindexchanged="DropDownList2_SelectedIndexChanged">
 

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

<br />
<br />
<br />
<br />
<br />
 <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AllowSorting="True" AutoGenerateColumns="False" DataSourceID="sds"
ShowFooter="True" style="margin-left: 80px" Width="90%">

<asp:BoundField DataField="pcode" HeaderText="pcode" SortExpression="pcode" />
<asp:BoundField DataField="fyyear" HeaderText="fyyear"
SortExpression="fyyear" />
<asp:BoundField DataField="date" HeaderText="date" SortExpression="date" />
<asp:BoundField DataField="salary" HeaderText="salary"
SortExpression="salary" />
<asp:BoundField DataField="ta" HeaderText="ta" SortExpression="ta" />
<asp:BoundField DataField="contigency" HeaderText="contigency"
SortExpression="contigency" />
<asp:BoundField DataField="nrc" HeaderText="nrc" SortExpression="nrc" />
<asp:BoundField DataField="institcharges" HeaderText="institcharges"
SortExpression="institcharges" />
<asp:BoundField DataField="others" HeaderText="others"
SortExpression="others" />


<asp:SqlDataSource ID="sds" runat="server"
ConnectionString="<%$ ConnectionStrings:connstr %>"
SelectCommand="SELECT DISTINCT [pcode], [fyyear], [date], [salary], [ta], [contigency], [nrc], [institcharges], [others] FROM [monthly] WHERE (([pcode] = @pcode) AND ([fyyear] = @fyyear))">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" DefaultValue="select"
Name="pcode" PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="DropDownList2" DefaultValue="select"
Name="fyyear" PropertyName="SelectedValue" Type="String" />
</SelectParameters>

<br />

 

 
codebehind for it:
 
protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
{
if (!IsPostBack == true)
{
con.Open();
}
 
try
{
 
string query = "SELECT [pcode], [fyyear], [date], [salary], [ta], [contigency], [nrc], [institcharges], [others] FROM [monthly] WHERE ([pcode] = '" + DropDownList1.SelectedValue.ToString() + "') and ([fyyear]='" + DropDownList2.SelectedValue.ToString() + "')";
SqlCommand cmd = new SqlCommand(query, con);
DataTable dt = new DataTable();
SqlDataAdapter apd = new SqlDataAdapter(query, con);
apd.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
 
}
 
catch
{
}
 
con.Close();
 
m coding it under selectindexchange event of my 2nd dropdownlist...but when m selecting values from dropdownlist gridview is not visisble.
 

database table:
fyyear...varchar
 
pcode...varchar
Nandakishorerao at 8-Nov-12 8:17am
   
the code behind what have u written...gives error bcoz gridview cannot have two datasource at a stretch...therefore,i suggested u to change the selectcommand of sqldatasource.but u hav not done...it..and
initially,when u posted there was a button but now it is not there..
 
remove that !ispostback...and try....using the code mentioned...just try to be cool and think..
a2ulthakur at 8-Nov-12 22:02pm
   
ok i did what u asked me now i m using just one data source which is in code behind but still whats not happening is ...if i run a simple query like "select * from monthly" its showing gridview but if i am trying to add dropdownlist filters like u told me to in the above query nothing is happening its not even showing me the gridview then ...its not even showing gridview when i do this [ string str = "select * from monthly where pcode = " +DropDownList1.SelectedItem.Value.ToString();]
a2ulthakur at 8-Nov-12 22:10pm
   
and about button i thought i wud just remove the button and put the coding on dropdownlist2_selectindex but still its not happening. and i am again using a button cause i thought the autopostback property of dropdwn2 was not firing
so this is the code behind that m running now on submit button :
try
{
 
string str = "select * from monthly where pcode = " + DropDownList1.SelectedItem.Value.ToString();
SqlDataAdapter adp = new SqlDataAdapter(str, con);
adp.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
 

 

 
catch (Exception ex)
{
ex.Message.ToString();
}
 
even this simple code is not displaying the result ...
 
one more thing i want to ask ... do i have to make relations b/w all the tables in my database is it necessary somebody was telling me coz i dont have made relations in all my tables this is why i am facing this problem ..but i am passing values from dropdownlist on submit button which are already having their data source. please give me an insight about this too
Nandakishorerao at 8-Nov-12 23:21pm
   
one thing u have to do is...before doing this in vs...better copy paste the code in ssms...and remove that dropdown something..cntrls...and place the original pcode value u are adding from front end ...and execute if u get error...enter that error msg code in google u'll get answers..bcoz we cannot see whats going on in ur server..rite..
ok..i'll give an example...
 
1. this is what u have written..rite?
select * from monthly where pcode = " + DropDownList1.SelectedItem.Value.ToString()
 
paste this in ssms....
 
select * from monthly where pcode = somevalue
 
execute it.... when u specify tbname---monthly..i think this is single table...not a view rite...
 
2. u had said pcode and fyear both are varchar..if s?
then...u cant rite above code....
u have to change it to..
select * from monthly where pcode = 'somevalue'
 
it should execute...it should work...
 
3. after verifying above mentioned steps, during debug time..in vs..place breakpoint at button click..and verify the values u are passing to the query are rite or wrong...
 
try..and tell the result...
a2ulthakur at 11-Nov-12 23:42pm
   
thanks for explaining
so well :)
Nandakishorerao at 12-Nov-12 9:15am
   
you are welcome..but one thing..if it solved your problem then i'm the most happiest person..Enjoy Coding..
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

 public void Button2_Click(object sender, EventArgs e)
    {
        try
        {
 

            SqlDataAdapter da = new SqlDataAdapter("select pcode,fyyear,date,salary,ta,contigency,nrc,institcharges,others from monthly where pcode=('" + DropDownList1.SelectedItem.ToString() + "' ) AND fyyear=('" + DropDownList2.SelectedItem.ToString() + "')", con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
 
            DropDownList1.SelectedIndex = 0;
            DropDownList2.SelectedIndex = 0;
        }
 
        catch (Exception ex)
        {
            ex.Message.ToString();
        }
 

    }
  Permalink  

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

  Print Answers RSS
0 Maciej Los 525
1 OriginalGriff 480
2 Sascha Lefévre 289
3 Richard Deeming 195
4 Peter Leow 148
0 Sergey Alexandrovich Kryukov 9,693
1 OriginalGriff 9,185
2 Peter Leow 5,112
3 Kornfeld Eliyahu Peter 3,373
4 Maciej Los 2,876


Advertise | Privacy | Mobile
Web04 | 2.8.150327.1 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100