Firstly never ever use string concatenation to create your SQL statements like this. Research
Parameters
and use them.
To get the last 5 rows try this query
SELECT * FROM (
SELECT * FROM yourTtable ORDER BY (id) DESC LIMIT 5
WHERE Convert(researchkey,'System.String') like @filter
) subq
ORDER BY (id) ASC
[Edit] - This solution only works when populating the DataGridView and not when filtering. I am attempting to find the correct solution and will revisit this solution as soon as possible.
[Edit2]. I've played around with this for some time and can only limit the display to the
first 5 rows which fit the filter - by implementing paging on the datagridview
(These articles give ideas how to do that:
DataGridView With Paging (UserControl)[
^] or
A Simple Way for Paging in DataGridView in WinForm Applications[
^])
You could display only the last 5 by navigating to the last row in the filtered grid:
DataGridView1.FirstDisplayedScrollingRowIndex = DataGridView1.RowCount - 1
DataGridView1.Rows(Me.DataGridView1.RowCount - 1).Selected = True
Personally I would not do the filtering on the DataGridView but just populate it with pre-filtered data - in which case my original SQL statement would apply.
E.g. (untested)
private void button1_Click(object sender, EventArgs e)
{
var constring = "";
var sql =
"SELECT * FROM (SELECT * FROM yourTtable ORDER BY (id) DESC LIMIT 5 WHERE Convert(researchkey,'System.String') like @filter) subq ORDER BY (id) ASC";
using (var connection = new SqlConnection(constring))
{
connection.Open();
using (var myAdapter = new SqlDataAdapter(sql, connection))
{
myAdapter.SelectCommand.Parameters.AddWithValue("@filter", textBox1.Text);
var ds = new DataSet();
myAdapter.Fill(ds);
bs.DataSource = ds.Tables[0];
}
connection.Close();
}
}