CODEWITHSUNDEEP
c#asp.netsql-serverwebforms
user13541818
user13541818

Reputation:

Set the first value as NULL value to DropDownMenu C#

When I run application dropdown menu's value are always set to 0 and displaying result in Report.

I want to modify these to add text in DropDownMenu and when user is not selected anything it should return all data, if user select value from dropdown it should return value which user selected.

First DropDownMenu

public void FillOrgUnit()
{
            using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=DesignSaoOsig1;Integrated Security=True"))
            {
                string com = "SELECT DISTINCT OrgUnitID FROM tblZaposleni_AD ORDER BY OrgUnitID ASC";
                SqlDataAdapter adpt = new SqlDataAdapter(com, conn);
                DataTable dt = new DataTable();
                adpt.Fill(dt);
                ddlOrgUnit.DataSource = dt;
                ddlOrgUnit.DataTextField = "OrgUnitID";
                ddlOrgUnit.DataValueField = "OrgUnitID";
                ddlOrgUnit.DataBind();

                ddlOrgUnit.Items.Insert(0, new ListItem("-- Izaberi Org Jedinicu --", "NULL"));
            }
}

Second dropdown menu:

public void FillStatus()
{
        using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=DesignSaoOsig1;Integrated Security=True"))
        {
            string com = "SELECT DISTINCT Status FROM tblZaposleni_AD";
            SqlDataAdapter adpt = new SqlDataAdapter(com, conn);
            DataTable dt = new DataTable();
            adpt.Fill(dt);
            ddlStatus.DataSource = dt;
            ddlStatus.DataTextField = "Status";
            ddlStatus.DataValueField = "Status";
            ddlStatus.DataBind();

            ddlStatus.Items.Insert(0, new ListItem("-- Izaberi Status --", "NULL"));
        }
}

enter image description here

HTML

 <div>
            <p class="auto-style1">
                Izaberi Izvjestaj :
                <br class="auto-style1" />
                <asp:DropDownList ID="ddlReportName" runat="server" Width="168px" DataTextField="Value" DataValueField="Key" OnSelectedIndexChanged="ddlReportName_SelectedIndexChanged" Height="16px">
                </asp:DropDownList>
                <br class="auto-style1" />
                Org Unit
                <br class="auto-style1" />
                <asp:DropDownList ID="ddlOrgUnit" runat="server" Height="17px" OnSelectedIndexChanged="ddlOrgUnit_SelectedIndexChanged" Width="157px" AppendDataBoundItems="True">                
                <asp:ListItem Value="">-- Izaberi Org Jedinicu --</asp:ListItem>
                </asp:DropDownList>
                &nbsp;
                <br class="auto-style1" />
                Status:
                <br class="auto-style1" />
                <asp:DropDownList ID="ddlStatus" runat="server"  Height="16px" OnSelectedIndexChanged="ddlStatus_SelectedIndexChanged1" Width="147px" AppendDataBoundItems="True">               
                <asp:ListItem Value="">-- Izaberi Status --</asp:ListItem>
                </asp:DropDownList>
                &nbsp;
            </p>
            <p class="auto-style1">
                <br class="auto-style1" />
                <%--<a href="javascript:ShowReport()">Show</a>--%>
                <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Filter" Width="224px" />

            </p>
        </div>

Page_Load where I call FillStatus and FillOrgUnt metod

protected void Page_Load(object sender, EventArgs e)
{
            if (!IsPostBack)
            {
                string path = @"\Reports\";
                CustomReportStorageWebExtension reportsStorage = new CustomReportStorageWebExtension(path);
                ddlReportName.DataSource = reportsStorage.GetUrls();
                ddlReportName.DataBind();

                //Call function for populate cb
                FillStatus();
                FillOrgUnit();
            }
            else
            {
                XtraReport reportToOpen = null;

                switch (ddlReportName.SelectedValue)
                {
                    case "Zaposleni 1":
                        reportToOpen = new ZaposleniSaoOsig1();
                        break;

                    case "Zaposleni 2":
                        reportToOpen = new ZaposleniSaoOsig2();
                        break;

                    case "Zaposleni 3":
                        reportToOpen = new ZaposleniSaoOsig3();
                        break;
                }

                GetReports(reportToOpen);
                ASPxWebDocumentViewer1.OpenReport(reportToOpen);
            }
        }

Main function which filters Status and OrgUnit

private void GetReports(XtraReport report)
{
            try
            {
                string connString = @"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=DesignSaoOsig1;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connString);
                string strproc = "TestReport";
                using (SqlDataAdapter sda = new SqlDataAdapter(strproc, connString))
                {
                    DataSet ds = new DataSet();
                    SqlCommand cmd = new SqlCommand();
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;

                    sda.SelectCommand.Parameters.Add("@Status", SqlDbType.Bit).Value = ddlStatus.SelectedValue == "1" ? true : false;
                    sda.SelectCommand.Parameters.Add("@OrgJed", SqlDbType.Int).Value = ddlOrgUnit.SelectedValue;
                    sda.Fill(ds);

                    string[] arrvalues = new string[ds.Tables[0].Rows.Count];

                    for (int loopcounter = 0; loopcounter < ds.Tables[0].Rows.Count; loopcounter++)
                    {
                        //assign dataset values to array
                        arrvalues[loopcounter] = ds.Tables[0].Rows[loopcounter]["PrezimeIme"].ToString();
                        arrvalues[loopcounter] = ds.Tables[0].Rows[loopcounter]["NetworkLogin"].ToString();
                        arrvalues[loopcounter] = ds.Tables[0].Rows[loopcounter]["Status"].ToString();
                        arrvalues[loopcounter] = ds.Tables[0].Rows[loopcounter]["OrgUnitID"].ToString();
                    }


                    report.DataSource = ds;
                    report.DataMember = ds.Tables[0].TableName.ToString();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

As well as stored procedure which return filtered Report by Status Or OrgId

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TestReport]
(           
    @Status bit,
    @OrgJed int
) 
AS  
BEGIN
    SELECT PrezimeIme, NetworkLogin, Status, OrgUnitId, DT_Creat, DT_Modif
    FROM [DesignSaoOsig1].[dbo].[tblZaposleni_AD]
    WHERE (@Status IS NULL OR Status = @Status) 
      AND (@OrgJed IS NULL OR OrgUnitID = @OrgJed)
END

Upvotes: 1

Views: 1021

Answers (2)

Alex Leo
Alex Leo

Reputation: 2851

You can do the following to bind the datasource:

    using (SqlConnection conn = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=DesignSaoOsig1;Integrated Security=True"))
    {
      string com = "SELECT DISTINCT OrgUnitID FROM tblZaposleni_AD ORDER BY OrgUnitID ASC";    
      SqlDataAdapter adpt = new SqlDataAdapter(com, conn); 
      DataTable dt = new DataTable();    
      adpt.Fill(dt);    
      ddlOrgUnit.DataSource = dt;    
      ddlOrgUnit.DataBind();    
      ddlOrgUnit.DataTextField = "text field you want to bind";    
      ddlOrgUnit.DataValueField = "value field you want to bind";    
      ddlOrgUnit.DataBind();

      //add default value - you can then remove the default value from html
      ddlOrgUnit.Items.Insert(0, new ListItem("-- Izaberi Org Jedinicu --","N/A")
     }

The above logic should be done in the FillStatus() method too.

In your Page_Load method do the following

        if (!IsPostBack)
        {
           FillStatus();

           FillOrgUnit();
        }

In the ddlOrgUnit_SelectedIndexChanged - for example - you will handle the value selected by the user accordingly - and filter for the select value - or return all.

NOTE When you fill your DataTable(dt) from your query - you will have a table structure from the following SQL table tblZaposleni_AD

In here ddlOrgUnit.DataTextField = "text field you want to bind"; you will add the column name you want to bind as text file - eg Name

NOTE How to use tryparse in C#

    if (Int32.TryParse(ddlStatus.SelectedValue, out int theValue))
    {
        //is not null
        sda.SelectCommand.Parameters.Add("@OrgJed", SqlDbType.Int).Value = theValue
    }

    // is null and you dont pass the parameter

Then in your stored procedure you set the default value for @OrgJed int to be null

ALTER PROCEDURE [dbo].[TestReport]
(           
    @Status bit,
    @OrgJed int = NULL
)

Upvotes: 1

Abdul Haseeb
Abdul Haseeb

Reputation: 582

using(sqlconnection con=new sqlconnection(cs))
{
   sqlcommand cmd=new sqlcommand("select [datatextfield], [datavaluefield] from tbl",con);
   sqldatareader rdr=cmd.executereader();
   dropdown.datasource=rdr;
   dropdown.datatextfield=rdr[0];
   dropdown.datavaluefield=rdr[1];
   dropdown.databind();
}

Upvotes: 0

Related Questions