stored procedure for searching records with if - else - condition

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[search1]
(
@category varchar(max),
@state varchar(max),
@City varchar(max)
)as
if @category = 'Select Category' and @state = '' and @City = ''
Begin
Select * From Colleges;
END

else if @state = '' and @City = ''
Begin
Select * From Colleges where SE_category =@category
END

else if @category = 'Select Category' and @City = ''
Begin
Select * From Colleges where SE_Loaction =@state
END

else if @category = 'Select Category' and @state = ''
Begin
Select * From Colleges where SE_Loaction =@City
END

else if @category = 'Select Category'
Begin
Select * From Colleges where SE_Loaction = @state or SE_Loaction =@City
END

else if @state = ''
Begin
Select * From Colleges where SE_category =@category  and SE_Loaction =@City
END

else if @City = ''
Begin
Select * From Colleges where SE_category =@category  and SE_Loaction =@state
END

else
Begin
Select * From Colleges where SE_category =@category  and SE_Loaction =@state and  SE_Loaction =@City
END

grouping of list values in dropdownlist

1 First we need to add other third party .dll file

Koutny.WebControls.DropDownGroupableList.Net2.dll


2 Then we need to include this in our project

we do this by adding this Koutny.WebControls.DropDownGroupableList.Net2.dll file in bin and adding inleft side toolbox

3 after this include this in web config file


<authentication mode="Windows"/>
<pages>
<controls>
<add tagPrefix="koutny" namespace="Koutny.Web.UI.WebControls" assembly="Koutny.WebControls.DropDownGroupableList.Net2"/>
<add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/></controls>
<namespaces>
<add namespace="Koutny.Web.UI.WebControls"/>
</namespaces>
</pages>



4 after that use the control in design page like this


 <koutny:DropDownGroupableList ID="drop1" runat="server" CssClass="select" Width="150px" >
<koutny:OptionListItem Text="Select Category"/>
</koutny:DropDownGroupableList>

5 and in code behind


 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            drop1.Items.AddItem("Item1");
            drop1.Items.AddItem("Item2");
            drop1.Items.AddGroup("Group1", "G1");
            drop1.Items.AddItem("Item3", "Item3", "G1");
            drop1.Items.AddItem("Item4", "Item4", "G1");
            drop1.Items.AddItem("Item5");
        }
    }


6. to get the value of the selected item use like this

   protected void DisplaySelectedItem(object sender, EventArgs e)
{
OptionListItem selectedItem = drop1.SelectedItem;
if (selectedItem != null)
{
litSelectedItem.Text = selectedItem.Text;

OptionGroupItem selectedGroup = drop1.SelectedGroup;
if (selectedGroup != null) litSelectedGroup.Text = selectedGroup.Text;
else litSelectedGroup.Text = "none";
}
}

How to pass field value from one page to another page

Case: we need to send dropdownlist value which is in home page to search result page on button click


in button click include this
 
     Response.Redirect("Searchresults.aspx?DropDownList1=" + DropDownList1.SelectedItem.Text);

in serach result page access the value like this 

      String s = Request.QueryString["DropDownList1"];
       Label4.Text = s;


Thats it..........



DropDownList1 is the variable wch holds the value
DropDownList1.SelectedItem.Text is the property for the value.

storing text documents in the folder and the file path in the database

1 .first create the folder to store the documents 
    here i created folder with name "documents"

2 after that create table in database like this


and in code behind

   string FileName = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
        //Save images into Images folder
        FileUpload1.SaveAs(Server.MapPath("documents/" + FileName));
        //Getting dbconnection from web.config connectionstring
     
        //Open the database connection
        con.Open();
        //Query to insert images path and name into database
        SqlCommand cmd = new SqlCommand("Insert into testing(ImageName,ImagePath) values(@ImageName,@ImagePath)", con);
        //Passing parameters to query
        cmd.Parameters.AddWithValue("@ImageName", FileName);
        cmd.Parameters.AddWithValue("@ImagePath", "documents/" + FileName);
        cmd.ExecuteNonQuery();
        //Close dbconnection
        con.Close();

Procedure to bind checkboxlist control

  public void chkboxlist()
    {
        SqlDataAdapter da = new SqlDataAdapter("select SE_Category from category", con);
        DataTable dt = new DataTable();
        da.Fill(dt);
        CheckBoxList1.DataSource = dt;
        CheckBoxList1.DataTextField = "SE_Category";
        CheckBoxList1.DataValueField = "SE_Category";
        CheckBoxList1.DataBind();
        CheckBoxList1.Items.Insert(0, "Others");
    }

Importing data from text fles/notepad into database

BULK
INSERT table_name
FROM 'C:\Documents and Settings\manju\Desktop\test.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Most hardest thing is to change the default view of the DropDown LIst. here is the simple CSS for that

<style type="text/css"> 
.select {
  -webkit-appearance: button;
  -webkit-border-radius: 2px;
  -webkit-box-shadow: 0px 1px 3px rgba(0, 0, 0, 0.1);
  -webkit-padding-end: 20px;
  -webkit-padding-start: 2px;
  -webkit-user-select: none;
  background-image: url(images/arrow.png),
    -webkit-linear-gradient(#FAFAFA, #F4F4F4 40%, #E5E5E5);
  background-position: center right;
  background-repeat: no-repeat;
  border: 1px solid #AAA;
  color: #555;
  font-size: inherit;
  margin: 0;
  overflow: hidden;
  padding-top: 2px;
  padding-bottom: 2px;
  text-overflow: ellipsis;
  white-space: nowrap;}
</style>
.----------------------------




  <asp:DropDownList ID="DropDownList1" runat="server"  CssClass="select"    Width="150px">
        <asp:ListItem>manja</asp:ListItem>
         <asp:ListItem>manja1</asp:ListItem>
          <asp:ListItem>manja2</asp:ListItem>
           <asp:ListItem>manja3</asp:ListItem>
        </asp:DropDownList>



    thats it .............. joy......