E:\web\learnaspcom\htdocs\freebook\learn\ubtoc.xml LearnAsp.com - ASP ASP.net Free Lessons
Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll 		<Asp.net blog>
<personal site>
xxx

C# Searcher Sample by Charles Carroll

Here is code to show several list boxes and let the user search data from an Access database.

   filename=/experiments/training/cs/searcher.aspx

<Test Script Below>

 

<%@ trace="true" debug="true"%>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="C#" runat="server">
string strConn;
protected void Page_Load(Object S, EventArgs E)
    {
    strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=";
    strConn += Server.MapPath(@"/experiments/data/biblio.mdb") + ";" ;
    searchagain.Visible=false;
    if (!Page.IsPostBack)
        {
        string strSQL;
        OleDbConnection Conn=new OleDbConnection(strConn);

        strSQL="select distinct city from publishers";
        OleDbCommand Cmd=new OleDbCommand(strSQL,Conn);
        Conn.Open();
        cy.DataSource = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        cy.DataBind();
        Conn.Close();

        strSQL="select distinct state from publishers";
        OleDbCommand Cmd2=new OleDbCommand(strSQL,Conn);
        Conn.Open();
        st.DataSource = Cmd2.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        st.DataBind();
        Conn.Close();
        
        strSQL="select distinct zip from publishers";
        OleDbCommand Cmd3=new OleDbCommand(strSQL,Conn);
        Conn.Open();
        zp.DataSource = Cmd3.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        zp.DataBind();
        Conn.Close();    
        } // end if
    } // end page_load    

protected void search_click(object S, EventArgs E)
    {
    Trace.Write("city",cy.SelectedItem.Text);
    Trace.Write("state",st.SelectedItem.Text);
    Trace.Write("zip",zp.SelectedItem.Text);
    Trace.Write("cityinc",cyinclude.Checked.ToString()); 
    Trace.Write("stateinc",stinclude.Checked.ToString()); 
    Trace.Write("zipinc",zpinclude.Checked.ToString()); 
    
    string strSQL;
    string prefix="";
    
    
    searchcriteria.Visible=false;
    search.Visible=false;
    searchagain.Visible=true;
    searchresults.Visible=true;
    
    strSQL="select * from publishers where ";
    
    if (!(cyinclude.Checked || stinclude.Checked || zpinclude.Checked))
        {
        message.Text="You did not choose any search categories so we cannot Search";
        searchresults.Visible=false;
        return;
        } // end if
    else
        {
        message.Text="";
        } // end else
    
    if (cyinclude.Checked)
        {
        strSQL+= " city='" + cy.SelectedItem.Text + "' ";
        prefix=" AND ";
        } // end if

    if (stinclude.Checked)
        {
        strSQL+= prefix + " state='" + st.SelectedItem.Text + "' ";
        prefix=" AND ";
        } // end if

    if (zpinclude.Checked)
        {
        strSQL+= prefix + " zip='" + zp.SelectedItem.Text + "' ";
        } // end if

    Trace.Write("strSQL",strSQL);

    OleDbConnection Conn=new OleDbConnection(strConn);
    Conn.Open();
    OleDbCommand Cmd1=new OleDbCommand(strSQL,Conn);
    searchresults.DataSource=Cmd1.ExecuteReader();
        searchresults.DataBind();
    Conn.Close();

    } // end search_click

protected void searchagain_click(object S, EventArgs E)
    {
    searchcriteria.Visible=true;
    searchresults.Visible=false;
    searchagain.Visible=false;
    search.Visible=true;
    } // end searchagain_click
    
</script>
<html><head>
<title>Searcher</title>
</head>
<body bgcolor="#FFFFFF">
<form runat="server">
<asp:literal id="message" runat="server"/>
<asp:Table id="searchcriteria" runat="server" GridLines="both" BorderWidth="1px">
<asp:TableRow>
<asp:TableCell>City</asp:TableCell>
<asp:TableCell>
    <ASP:DropDownList  id="cy" datatextfield="city" runat="server"/>
</asp:TableCell>
<asp:TableCell>
    <ASP:checkbox id="cyinclude" text="include in Search?" runat="server"/>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell>State</asp:TableCell>
<asp:TableCell>
    <ASP:DropDownList id="st" datatextfield="state" runat="server"/>
</asp:TableCell>
<asp:TableCell>
    <ASP:checkbox id="stinclude" text="include in Search?" runat="server"/>
</asp:TableCell>
</asp:TableRow>

<asp:TableRow>
<asp:TableCell>Zip</asp:TableCell>
<asp:TableCell>
    <ASP:DropDownList id="zp" datatextfield="zip" runat="server"/>
</asp:TableCell>
<asp:TableCell>
    <ASP:checkbox id="zpinclude" text="include in Search?" runat="server"/>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
<asp:button id="search" text="search for data" onclick="search_click" runat="server"/><br>
<asp:button id="searchagain" text="search for data again" onclick="searchagain_click" runat="server"/>

<ASP:DataGrid id="searchresults" runat="server" 
Width="100%" 
BackColor="white" 
BorderColor="black" 
ShowFooter="false" 
CellPadding=3 
CellSpacing="0" 
Font-Name="Verdana" 
Font-Size="8pt" 
Headerstyle-BackColor="lightblue" 
Headerstyle-Font-Size="10pt" 
Headerstyle-Font-Style="bold" 
MaintainState="false" 
/> 

</form>

</body></html>
Chaz Wish List
Tall Tip $5
Grande Tip $20
Venti Tip $39
Tip Jar Thanks
2004 Thanks
2005 Thanks
HUGE Tip -love site