SEARCH Operation on SQL database tables

Question

0.00/5 (No votes)

See more:

Dear All,

I am using Gridview control of ASP.NET to bind and access sql database table. I tried to implement "Search" records operation using the following code:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace PIMS
{
    public partial class Default : Page
    {
        public SqlConnection con;
        public string constr;

        public void connection()
        {
            constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            con = new SqlConnection(constr);
            con.Open();
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            Label1.Visible = false;
        }

        private void rep_bind()
        {
            using (SqlConnection con = new SqlConnection("Data Source=MEHDI-PC\\SQLEXPRESS;Initial Catalog=PIMS;Integrated Security=True"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    String sql = "SELECT DocumentsID, Ref,Subject,Src,Dst,Medium,Date_Printed,Date_Received,Document_Type,Action_Required,Due_Date,Actual_Date,Content,Tag,Issue_No,Attachment,Notes,Assigned_To,Reply_Ref,Priority,Status,Response FROM dbo.Documents";
                    cmd.Connection = con;
                    cmd.CommandText = sql;
                    //cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    //dataset object to get all select statement results
                    DataSet ds = new DataSet();

                    //sql dataadoptor to fill dataset
                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        //here all select statements are fill in dataset object
                        adp.Fill(ds);
                    } 
                    this.GridView1.DataSource = ds.Tables[0]; // first select statement result
                    this.GridView1.DataBind();
                }

                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            connection();
            string query = "select Ref  from dbo.Documents where Ref like'" + TextBox1.Text + "%'";
            SqlCommand com = new SqlCommand(query, con);

            SqlDataReader dr;
            dr = com.ExecuteReader();

            if (dr.HasRows)
            {
                dr.Read();
                rep_bind();
                GridView1.Visible = true;

                TextBox1.Text = "";
                Label1.Text = "";
            }
            else
            {
                GridView1.Visible = false;
                Label1.Visible = true;
                Label1.Text = "The search Term " + TextBox1.Text + "  Is Not Available in the Records"; ;

            }
        }
    }
}

But its now performing any search operations on the table. Neither it is throwing any errors.

Please help.

Posted 24-Jun-13 21:44pm

rf_libra

Updated 24-Jun-13 21:46pm

Orcun Iyigun

v2

Add a Solution

3 solutions

Solution 1

Test your query running on Sql server Query command prompt and check is There any Other Result available or not?

SQL

select Ref  from dbo.Documents where Ref like 'Your searchText%';

Check CoonectionString provided in your application.

Try to Use Exception Handling mechanism to test your code.

try to find actual place of errors and post all the steps result.

Posted 24-Jun-13 22:05pm

ashriv

Comments

[no name] 25-Jun-13 4:15am

Thanks for the reply.

select Ref from dbo.Documents where Ref like 'Your searchText%';

is working fine in SSMS.

Solution 3

This solution is in response to the comment on Solution 2.

Okay, so this section of code is never going to work:

C#

bool flag = false;
if (!txtRef0.Text.Equals(""))
{
   if (flag == false)
   {
        sql = sql + " where Ref =N'" + txtRef0.Text + "'";
        flag = true;
    }
    else
    {
         sql = sql + "  and Ref =N'" + txtRef0.Text + "'";
     }

The reason for this is that you are loosing the scope of the flag each time the method ends. So every time it gets triggered it will start false.

Also, it seems like you are tying to create a search method that combines multiple options, in which case I would suggest to look up the "IN" method for SQL.

When you step through your code, before you execute the SQL statement that it has produced, use the tools in visual studio to copy the statement and then try running in SSMS and see what happens.

Posted 24-Jun-13 22:31pm

Pheonyx

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pheonyx · Accepted Answer · 2013-06-24T22:08:00

Hi there,

Your code is probably working exactly as you have written it. At a quick glance I can see a basic flaw in your "Button1_Click" event (which I have assumed is your search function?).

You have put this section of code in:

C#

if (dr.HasRows)
{
    dr.Read();
    rep_bind();
    GridView1.Visible = true;

    TextBox1.Text = "";
    Label1.Text = "";
}

Which, will mean that if the search result has rows then it just calls the standard rep_bind method.
This rep_bind method does not do any filtering based on your search criteria, hence I suspect you are seeing that the list populates but does not filter.

I would suggest having the rep_bind method defined with a "filter" parameter that gets appended to the end of your SQL query. Then in your filter search, you could pass the "filter" code in.

This is not the best method, because it means you end up querying the database multiple times.

An alternative would be to re-work the code so that even when you are "searching" you only query the data one to both perform the search and to return the results.

Also, I suggest you look at some of the following links as your current search query is susceptible to SQL Injection attacks.

Using SQLParameters with VB.NET/C#[^]
http://csharp-station.com/Tutorial/AdoDotNet/Lesson06[^]
http://johnhforrest.com/2010/10/parameterized-sql-queries-in-c/[^]