Search facility enhancements

.NET
1

Hey,

I have the following code which searches through the database and pulls out information. It all works fine:-


    protected void Button1_Click(object sender, EventArgs e)
    {
        string connStr = "Data Source=SQLB23.webcontrolcenter.com;User ID=wbsd;Password=*******;";
        SqlConnection dbConn = new SqlConnection(connStr);

        dbConn.Open();

        Label1.Text = "Sorry no results found.";
        Label2.Text = "";

        string sqlStr = "SELECT user_id, fname, sname, email, avatar, sex FROM hussaini_users WHERE fname like '%" + TextBox1.Text + "%' AND sex = '" + DropDownList1.SelectedValue + "'";

        SqlCommand dbCommand = new SqlCommand(sqlStr, dbConn);
        SqlDataReader dbReader = dbCommand.ExecuteReader();

        if (dbReader.HasRows)
        {
            Label1.Text = "You searched for <strong>" + TextBox1.Text + "</strong>";
            Label2.Text = "<ul>";

            while (dbReader.Read())
            {
                Label2.Text += "<li><a href='user-wall.aspx?ID=" + dbReader["user_id"].ToString() + "'><img src='avatars/" + dbReader["avatar"].ToString() + "' border='0'></a><p style='float:right;margin-right:10px'><a href='user-wall.aspx?ID=" + dbReader["user_id"].ToString() + "'>View now</a></p>" +
                "<p><a href='user-wall.aspx?ID=" + dbReader["user_id"].ToString() + "'>" + dbReader["fname"].ToString() + " " + dbReader["sname"].ToString() + "</a></p></li>";
            }

            Label2.Text += "</ul>";
        }
        dbReader.Close();
        dbConn.Close();
    }

However when the search works it display a message like this:-

You searched for ben

Now i also want to show how many fields have been returned so i would want to show the message like so:-

You searched for ben, which returned 2 results.

How can i alter my code to achieve this?

Regards
Billy

2

Firstly, you need to remove all the sql injection holes by using parameters in your query instead of inline concat like this.

To answer ur question: You can just put and int count = 0; at the top and call count++; in each loop. Then in the end the count would contain the number of results. Not sure if there is a property to get it or not, have not used datareaders in ages.

But, looking at your code you should really be using a repeater for something like this. Thats what they are there for. This is MVC style where you output html in a loop. Then you can go repeater.Items.Count;

3

lol i forgot about the parameterized queries! thanks, and i actually managed to fix the code by adding a count = 0, count ++…

But i think your way is better, so will go for that when i get a chance to implement!

Regards
Thanks