techno techno - 4 months ago 9
SQL Question

Selecting Items from Database that Matches with the string Based on Entered Characters

I want to select the items that matches with the entered string.It seems the query is selecting all items that contain at-least a matching letter.

I don't want exact match ..i want to select the strings that match the starting..Like if i type 'it' i want to list all strings that starts with 'it'

What im i doing wrong?

enter image description here

using (SqlConnection conn = new SqlConnection(constr))
{

try
{
conn.Open();
SqlDataReader myReader = null;

string commandText = "SELECT itemname,rate,stock FROM mytable WHERE itemname LIKE @id";
SqlCommand command = new SqlCommand(commandText, conn);
string searchParam = String.Format("%{0}%", text_item.Text);
command.Parameters.AddWithValue("@id", searchParam);
using (SqlDataAdapter sda = new SqlDataAdapter(command))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
}

}
catch (Exception err)
{
MessageBox.Show(err.Message);
}

}

Answer

Don't use LIKE but use =. And change your string be exact string, instead of %text%

string commandText = "SELECT itemname,rate,stock FROM mytable WHERE itemname = @id";
...
string searchParam = text_item.Text;

---------------------- EDITED ------------------------

After the updated question, the answer would be:

string searchParam = string.Format("{0}%", text_item.Text);

The first % is not necessary, because you don't want wildcard there.