Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 1115
Home > Forums > C# >

how to dislay the data from sql by selecting dropdown value?

Posted by Tejamanimala under C# on 10/18/2013 | Points: 10 | Views : 2026 | Status : [Member] | Replies : 4
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
hi,i have one empid textbox,when i enter the emplid in the teaxtbox,am displaying the payroll details of that particular emplid and am displying the gridview also by using "ontextchanged".table have these columns.....empid,empname,designation,department,DOJ,DOR,Basicsal,ot,....some like this...i cont put primary key for any column,because,when for every month we have payroll so id will berepeated,so i think to put the two extra columns as month and year.and i want give the year as primery key.i have created dropdownlist above emid text box,when i select the month and year from the dropdownlist,after that when i enter the empid in the text box,we need to retrieve that year that month payroll,please help me for dropdown selection code,please check my code i wrote like this,but i didn't write the code for dropdown ,i con't understand how to wrote it....
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace pay_description
{
public partial class paydescription : System.Web.UI.Page
{
// string strcon = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection("user id=sa;password=Ektha@2013;Data Source=EKTHA-3D34;Initial Catalog=Ektha");
protected void Page_Load(object sender, EventArgs e)
{

txtEmpid.Attributes["onclick"] = "clearTextBox(this.id)";
TextBox24.Attributes["onclick"] = "clearTextBox(this.id)";
GridView2.Visible = true;
Label1.Text = Session["UserID"].ToString();
getName();

}

private void getName()
{
// SqlConnection con = new SqlConnection(strcon);
con.Open();

SqlCommand cmd = new SqlCommand("select Name from Admin where UserID ='" + Label1.Text + "'", con);
SqlDataReader dr = cmd.ExecuteReader();
string name = "";
try
{
if (dr.Read())
{
name = dr["Name"].ToString();

lblName.Text = name;
}
dr.Close();
}
catch (Exception ex)
{
string script = "<script>alert('" + ex.Message + "');</script>";
}

finally
{
con.Close();
}
}

protected void txtEmpid_TextChanged(object sender, EventArgs e)
{

con.Open();

SqlCommand com = new SqlCommand("select * from payroll where EmployeeID='" + txtEmpid.Text + "', Month='"+DropDownList1.SelectedItem.Value+"' and Year='"+DropDownList2.SelectedItem.Value+"'", con);
SqlDataReader dr;
dr = com.ExecuteReader();

if (dr.Read())
{
TextBox24.Text = dr["EmpName"].ToString();
TextBox1.Text = dr["Department"].ToString();
TextBox2.Text = dr["DOJ"].ToString();
TextBox3.Text = dr["BasicSalary"].ToString();
TextBox4.Text = dr["Designation"].ToString();
TextBox5.Text = dr["DOR"].ToString();
TextBox6.Text = dr["NetAmt"].ToString();

}
else
{

TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
}
if (dr.HasRows)
{
GridView3.DataSource = dr;
GridView3.DataBind();
GridView2.Visible = false;
GridView3.Visible = true;
}
else
{

GridView3.Visible = false;
GridView2.Visible = false;

}

}
}

}

manimala

Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Bandi on: 10/18/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer these links for getting dropdown list selected value
http://www.webcodeexpert.com/2013/08/how-to-get-dropdownlist-selected-value.html#.UmDjx9KnoRo
http://stackoverflow.com/questions/19198181/show-data-in-dropdownlist-from-sqlserver-in-asp-net-using-c-sharp


Note that you missed out AND operator in the WHERE clause of SELECT statement
SqlCommand com = new SqlCommand("select * from payroll where EmployeeID='" + txtEmpid.Text + "' AND  Month='"+DropDownList1.SelectedItem.Value+"' and Year='"+DropDownList2.SelectedItem.Value+"'", con);


Do you have Year, Month as separate columns in the table Payroll?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 10/18/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
>> Do you have Year, Month as separate columns in the table Payroll?

If you have DATETIME column in the table, then while binding data to drop down list get the Year and month values from DATETIME column by using datetime functions such as YEAR(DateColumn), DATEPART( MM, DateColumn)....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 10/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Sample code for GridView Binding based on Drop down selection and textbox value
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DropDownListWithGridView.aspx.cs" Inherits="TextBoxValidation.DropDownListWithGridView" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Week Days: <asp:DropDownList runat="server" ID="DropDownList1">
        Year: <asp:TextBox runat="server" id="txtYear" ontextchanged="txtYear_TextChanged"/>
            <asp:ListItem></asp:ListItem>
        </asp:DropDownList>  
        <asp:GridView ID="grdUserList" runat="server" AutoGenerateColumns="False" DataKeyNames="FullDateAlternateKey" >
            <Columns>
                    <asp:BoundField HeaderText="FullDateAlternateKey" DataField="FullDateAlternateKey" />
                    <asp:BoundField HeaderText="EnglishDayNameOfWeek" DataField="EnglishDayNameOfWeek" />
                    <asp:BoundField HeaderText="CalendarYear" DataField="CalendarYear" />
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace TextBoxValidation
{
    public partial class DropDownListWithGridView : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            BindDropDownList();
        }

        private void BindDropDownList()
        {
            string connectionstring = "Data source=XXXX ; Initial Catalog=AdventureWorksDW2008R2; Integrated Security=true";
            //ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

            SqlConnection conn = new SqlConnection(connectionstring);
            conn.Open();
            SqlCommand comm = new SqlCommand("SELECT DISTINCT EnglishDayNameOfWeek from [DimDate] ", conn);
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataTable dt = new DataTable();
            da.Fill(dt);
            DropDownList1.DataSource = dt;
            DropDownList1.DataTextField = "EnglishDayNameOfWeek";
            DropDownList1.DataValueField = "EnglishDayNameOfWeek";
            DropDownList1.DataBind();
        }

        protected void txtYear_TextChanged(object sender, EventArgs e)
        {
            string connectionstring = "Data source=XXXX ; Initial Catalog=AdventureWorksDW2008R2; Integrated Security=true";
            //ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

            SqlConnection conn = new SqlConnection(connectionstring);
            conn.Open();
            SqlCommand comm = new SqlCommand("SELECT FullDateAlternateKey,EnglishDayNameOfWeek,CalendarYear from [DimDate] where CalendarYear = " + txtYear.Text + "AND DATENAME(WEEKDAY, FullDateAlternateKey) = '" + DropDownList1.SelectedValue + "';", conn);
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            grdUserList.DataSource = ds;
            grdUserList.DataBind();
            grdUserList.UseAccessibleHeader = true;
            grdUserList.HeaderRow.TableSection = TableRowSection.TableHeader;
        }
    }
}




Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 10/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Click on "Mark as Answer " if you got help from above posts

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts