CODEWITHSUNDEEP
asp.netsqloracle-databasesearch
Harry
Harry

Reputation: 95

ASP. NET webpage: create a search box to search data from a table in Oracle

I'm trying to create an ASP.NET webpage to output a textbox, then user will enter the key in this textbox and I use that key to put into WHERE condition in SQL code to output data that matches the key. For example, user enters ArtistID in the textbox and I need to print the table SPEEDOFSALEREPORT using

SELECT * FROM SPEEDOFSALEREPORT WHERE ArtistID = "the key that user just enter"

but I don't know how. My code below creates a text box and print all rows in the table. Please give me a hint. I tried googling all days but cannot find the answer. Thank you.

This is my updated Default.aspx

    <body>
    <form id="form1" runat="server">
    <asp:TextBox ID="txtArtistID" runat="server" /><br /><br />
    <asp:Button ID="btnSearch" runat="server" Text="Search" CssClass="submit" />

    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="TRANSACTIONID" HeaderText="TRANSACTIONID" 
                    SortExpression="TRANSACTIONID" />
                <asp:BoundField DataField="WORKID" HeaderText="WORKID" 
                    SortExpression="WORKID" />
                <asp:BoundField DataField="ARTISTID" HeaderText="ARTISTID" 
                    SortExpression="ARTISTID" />
                <asp:BoundField DataField="DATEACQUIRED" HeaderText="DATEACQUIRED" 
                    SortExpression="DATEACQUIRED" />
                <asp:BoundField DataField="DATESOLDID" HeaderText="DATESOLDID" 
                    SortExpression="DATESOLDID" />
                <asp:BoundField DataField="TURNAROUNDDAYS" HeaderText="TURNAROUNDDAYS" 
                    SortExpression="TURNAROUNDDAYS" />
                <asp:BoundField DataField="ASKINGPRICE" HeaderText="ASKINGPRICE" 
                    SortExpression="ASKINGPRICE" />
                <asp:BoundField DataField="SALESPRICE" HeaderText="SALESPRICE" 
                    SortExpression="SALESPRICE" />
                <asp:BoundField DataField="SALESMARGIN" HeaderText="SALESMARGIN" 
                    SortExpression="SALESMARGIN" />
            </Columns>
        </asp:GridView>
    </div>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="Data Source=oracle2;User ID=****;Password=****;Unicode=True" 
    ProviderName="System.Data.OracleClient" 
    SelectCommand="SELECT * FROM SPEEDOFSALEREPORT WHERE ARTISTID = @artistID;">

    <SelectParameters>
        <asp:ControlParameter Name="artistID" ControlID="txtArtistID" PropertyName="Text" />
    </SelectParameter>
</asp:SqlDataSource>

    </form>
</body>

Upvotes: 0

Views: 13112

Answers (3)

Habeebrahman parambil
Habeebrahman parambil

Reputation: 1

protected void Button1_Click(object sender, EventArgs e){
        if (TextBox1.Text != ""){
        con.Open();
        int i = 0;
        SqlCommand cmd0 = new SqlCommand("select noticeid from TBLnotice where noticeid='" + TextBox1.Text + "'", con);
        i= int.Parse(cmd0.ExecuteScalar().ToString());
        if (i > 0){
            SqlCommand cmd1 = new SqlCommand("select notice from TBLnotice where noticeid='" + TextBox1.Text + "'", con);
            string name = cmd1.ExecuteScalar().ToString();
            txtadds.Text = name;
            TextBox1.Text = "";
            txtadds.Text = "";
            con.Close();
        }
        else{
            Label7.Visible = true;
            con.Close();
        }
    }
}

Upvotes: 0

LukeGeneva
LukeGeneva

Reputation: 584

You need to use the ArtistID as a parameter, and also change your text box and submit button to ASP controls like so:

<asp:TextBox ID="txtArtistID" runat="server" /><br /><br />
<asp:Button ID="btnSearch" runat="server" Text="Search" CssClass="submit" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="Data Source=oracle2;User ID=******;Password=***********;Unicode=True" 
    ProviderName="System.Data.OracleClient" 
    SelectCommand="SELECT * FROM SPEEDOFSALEREPORT WHERE ARTISTID = @artistID;">
    <SelectParameters>
        <asp:ControlParameter Name="artistID" ControlID="txtArtistID" PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>

Then bind the grid on the button click in your code behind (VB.NET):

Protected Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    GridView1.DataBind()
End Sub

Or (C#):

protected void btnSearch_Click(Object sender, EventArgs e)
{
    GridView1.DataBind();
}

Upvotes: 1

sanjay bhansali
sanjay bhansali

Reputation: 327

check this line :

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="Data Source=oracle2;User ID=******;Password=***********;Unicode=True" 
        ProviderName="System.Data.OracleClient" 
        SelectCommand="SELECT * FROM SPEEDOFSALEREPORT">

Correct using this :

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="Data Source=oracle2;User ID=******;Password=***********;Unicode=True" 
        ProviderName="System.Data.OracleClient" 
        SelectCommand="SELECT * FROM SPEEDOFSALEREPORT"
        FilterExpression="Artistid='{0}'">

       <FilterParameters><asp:ControlParameter Name="" ControlID="ArtistID" PropertyName="Value"/>  
        </FilterParameters>

Upvotes: 2

Related Questions