[varanasi]

Thank you for the Previous answer.
Now i have a new one.

I want make a search according to category selected from the drop down list that matches with the key word given in other text box and display it a grid view.

Please help me out!!

<asp:Label ID="lbsbyprod" runat="server" Font-Bold="True" 
                    Font-Names="Monotype Corsiva" ForeColor="White" Text="Search By"></asp:Label>
<asp:DropDownList ID="ddsbyprod" runat="server" Height="22px" 
                    style="margin-left: 27px" Width="130px">
                    <asp:ListItem Value="POS004_ITEM_ID">Item ID</asp:ListItem>
                    <asp:ListItem Value="POS004_ALT_ID">Alternate ID</asp:ListItem>
                </asp:DropDownList>
<asp:Label ID="lbsvprod" runat="server" Font-Bold="True" 
                    Font-Names="Monotype Corsiva" ForeColor="White" Text="Search Value"></asp:Label>
<asp:TextBox ID="tbsvprod" runat="server"></asp:TextBox>
<asp:Button ID="btnsrchprodmst" runat="server" onclick="btnsrchprodmst_Click" 
                    Text="Search" />
<asp:GridView ID="gv1" runat="server" DataSourceID="SqlDataSource5">
                </asp:GridView>

//CODE FOR SEARCH BUTTON

protected void btnsrchprodmst_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=SPSERVER;Initial Catalog=SuperPOS;Integrated Security=True");
        if (rblsrcht.SelectedValue == "Exactly")
        {
            string itemmstsrch = "select POS004_ITEM_ID,POS004_DESC,POS004_SALE_PRICE2,POS004_AVAIL_QTY,POS004_UNITS from POS_ITEMMST_004 where '"+ddsbyprod.SelectedValue+"' like '" + tbsvprod.Text + "'";
            con.Open();
            SqlCommand cmd = new SqlCommand(itemmstsrch,con);
            cmd.ExecuteNonQuery();
            this.gv1.DataBind();
            con.Close();
        }

[rgfirefly24]

First you REALLY should be used parameratized queries, second in SQL if you want to search with the like keyword you should use the % sign as well like so:

//Returns any record where column has the letters AR in it regardless of where it is in the string
SELECT * FROM Table WHERE Column LIKE '%AR%'
//Returns any record where the column starts with the letters AR
SELECT * FROM Table WHERE Column LIKE 'AR%'
//Returns any record where the column ends with the letters AR
SELECT * FROM Table WHERE Column LIKE '%AR'

[h4nnib4l]

I feel like a broken record lately, but read this tutorial on parameterizing your queries.

So what problems are you running into? What errors are you receiving? Which part isn't working?

[Nakor]

One problem you're going to have is that ExecuteNonQuery is used for inserting and updating, anything in which you are not trying to retrieve data from the database. You need to use ExecuteReader. You also need to set the datasource of the gridview before you databind it.

[varanasi]

I have made changes to my code.
But there is still problem existing.
When am giving the word to be searched in the text box output that is grid view is not displayed. When i leave the text box empty am the records of the table are displayed in grid view.

//c# code for search button

protected void btnsrchprodmst_Click(object sender, EventArgs e)
    {
        string st = ddsbyprod.SelectedValue;
        string sv = tbsvprod.Text;
        TextBox1.Text = st;
        TextBox2.Text = sv;
        SqlConnection con = new SqlConnection("Data Source=SPSERVER;Initial Catalog=SuperPOS;Integrated Security=True");
        SqlDataAdapter da = new SqlDataAdapter("select POS004_ITEM_ID,POS004_DESC from POS_ITEMMST_004 where '"+st+"' like '%"+sv+"%'", con);
        DataSet ds = new DataSet();
        con.Open();
        da.Fill(ds);
        gv1.DataSource = ds;
        gv1.DataBind();
        con.Close();

    }


//asp code

<td class="style32">
            &nbsp;&nbsp;
                <asp:Label ID="lbsbyprod" runat="server" Font-Bold="True" 
                    Font-Names="Monotype Corsiva" ForeColor="White" Text="Search By"></asp:Label>
                <asp:DropDownList ID="ddsbyprod" runat="server" Height="22px" 
                    style="margin-left: 27px" Width="130px">
                    <asp:ListItem Value="POS004_ITEM_ID">Item ID</asp:ListItem>
                    <asp:ListItem Value="POS004_ALT_ID">Alternate ID</asp:ListItem>
                </asp:DropDownList>
&nbsp;&nbsp;
                <br />
                <br />
            </td>
            <td class="style32">
                <asp:RadioButtonList ID="rblsrcht" runat="server" Font-Bold="True" 
                    Font-Names="Monotype Corsiva" ForeColor="White">
                    <asp:ListItem>Exactly</asp:ListItem>
                    <asp:ListItem>Likely</asp:ListItem>
                </asp:RadioButtonList>
            </td>
        </tr>
        <tr>
            <td class="style33">
                <asp:Label ID="lbscatid" runat="server" Text="Sub Category ID" Font-Bold="True" 
                    Font-Italic="True" Font-Names="Times New Roman" Font-Size="Medium" 
                    ForeColor="White"></asp:Label>
            </td>
            <td class="style34">
                <asp:DropDownList ID="ddscatid" runat="server" DataSourceID="SqlDataSource3" 
                    DataTextField="POS016_SUBCAT_ID" DataValueField="POS016_SUBCAT_ID" 
                    Height="22px" style="margin-bottom: 0px" Width="127px">
                </asp:DropDownList>
                <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:SuperPOProductGridView %>" 
                    SelectCommand="SELECT [POS016_SUBCAT_ID] FROM [POS_SUBCATEGORY_016]">
                </asp:SqlDataSource>
            </td>
            <td class="style35">
                <asp:Label ID="lbsvprod" runat="server" Font-Bold="True" 
                    Font-Names="Monotype Corsiva" ForeColor="White" Text="Search Value"></asp:Label>
&nbsp;&nbsp;&nbsp;
                <asp:TextBox ID="tbsvprod" runat="server" ontextchanged="tbsvprod_TextChanged"></asp:TextBox>
            </td>
            <td class="style35">
                <asp:Button ID="btnsrchprodmst" runat="server" onclick="btnsrchprodmst_Click" 
                    Text="Search" />
            </td>
        </tr>
        <tr>
            <td class="style3">
                <asp:Label ID="lbpp" runat="server" Text="Purchase Price" Font-Bold="True" 
                    Font-Italic="True" Font-Names="Times New Roman" Font-Size="Medium" 
                    ForeColor="White"></asp:Label>
            </td>
            <td class="style20">
                <asp:TextBox ID="tbpp" runat="server"></asp:TextBox>
            </td>
            <td colspan="2">
                &nbsp;</td>
        </tr>
        <tr>
            <td class="style6">
                <asp:Label ID="lbdes" runat="server" Text="Description" Font-Bold="True" 
                    Font-Italic="True" Font-Names="Times New Roman" Font-Size="Medium" 
                    ForeColor="White"></asp:Label>
                &nbsp;</td>
            <td class="style21">
                <asp:TextBox ID="tbdes" runat="server" Height="49px" TextMode="MultiLine" 
                    Width="142px"></asp:TextBox>
            </td>
            <td class="style7" colspan="2">
                <asp:GridView ID="gv1" runat="server">
                </asp:GridView>
            </td>

I have made changes to my code.
But there is still problem existing.
When am giving the word to be searched in the text box output that is grid view is not displayed. When i leave the text box empty all** the records of the table are displayed in grid view.

[Nakor]

It looks like you may have single quotes around your column name in your where clause

[varanasi]

No... That did not work!!

Should I use Commend name & command method?? If so please let me know how to use...

[rgfirefly24]

Have you tried debugging to see what all your values are?

This post has been edited by rgfirefly24: 02 October 2012 - 03:37 AM

[varanasi]

No errors are shown

[rgfirefly24]

Debugging isn't just about errors. It can show you what is in a variable, how a string actually looks after it is constructed, ect. There might be several things wrong including malformed SQL, your sql statement returns no results, or any number of different issues. Have you tried running your sql string directly against the database?

Also did you remove the single quotes around your column name?

SqlDataAdapter da = new SqlDataAdapter("select POS004_ITEM_ID,POS004_DESC from POS_ITEMMST_004 where '"+st+"' like '%"+sv+"%'", con);

should look like

SqlDataAdapter da = new SqlDataAdapter("select POS004_ITEM_ID,POS004_DESC from POS_ITEMMST_004 where "+st+" like '%"+sv+"%'", con);

This post has been edited by rgfirefly24: 02 October 2012 - 05:08 AM

[h4nnib4l]

Let's think this through. When you don't pass any variables to the query, it executes. When you do pass the variables, it fails. There isn't an error code, you just don't get any results. This should lead you to believe that there is something wrong with your WHERE clause. You would be getting an error if the column name was incorrect. Listen to Nakor and remove the single quotes around the column name, and then focus on the second. If a query with a WHERE clause is running without throwing errors, but not returning anything, it's usually a safe assumption that it's because the comparison being performed by the WHERE clause isn't generating any matches.

SELECT * FROM MyTable WHERE SomeColumn LIKE '%SOMEVALUE%'

Or, in your case:

// the @ at the front of the string creates a string literal, which allows my to spread my queries across multiple lines for readability

var sqlStr = @"SELECT POS004_ITEM_ID,POS004_DESC 
               FROM POS_ITEMMST_004 
               WHERE @ST like '%@SV%'"; 



using (var con = new SqlConnection("Data Source=SPSERVER;Initial Catalog=SuperPOS;Integrated Security=True")
using (var da = new SqlDataAdapter(sqlStr, con)
{
    da.SelectCommand.Parameters.AddWithValue("@ST", textbox1.text);
    da.SelectCommand.Parameters.AddWithValue("@SV", textbox2.text);

    var ds = new DataSet();

    // you get the idea...
}

Change out that nasty string concatenation for parameters (if you still need help with that, follow the link I posted earlier), and then start investigating the values you're trying to pass to your WHERE clause. You should get an error if you are misforming your WHERE clause, or if the column name is bad, so start with the values that you're evaluating.

This post has been edited by h4nnib4l: 02 October 2012 - 03:23 PM

[varanasi]

Thank you all. I could get it by removing the string concatenations as u all suggested.
Now i have got a new issue.
I already have a grid view which shows all rows of a table. It shows all the details as the form is loaded and gets updated when ever i add a new row to the table.
Now i want to show the search values in the same grid view when I click search button.

[rgfirefly24]

I have done this exact same thing in the project i'm currently wrapping up. What I did was within the button_click event for the search button execute the sql statement to bring back the results, set the datasource equal to your newly returned dataset and then call rebind() on your gridview.

[h4nnib4l]

The way I usually accomplish this is that I write the logic to build the query based on the criteria into a method, and write the binding logic as a separate method that takes the query as a string argument. I dynamically build the query based on selected search criteria, pass it to the binding method, and then the method does the same thing each time, just with different queries. The button click simply starts that process.