Hello,

I have created a query as follow in ms access:

SELECT cont.id, cont.name, cont.mobile_num, cont.date_created, memb.group_id, grp.name
FROM (contact AS cont LEFT JOIN contact_group_member AS memb ON memb.contact_id=cont.id) LEFT JOIN contact_group AS grp ON grp.id=memb.group_id
WHERE cont.removed=False
ORDER BY cont.id DESC;

and I created a 2nd query to query the first query as follow and returned 1 record:

SELECT *
FROM query1
WHERE cont.name like '*mark*' or cont.mobile_num like '*mark*';

In c# code, I query ms access same as 2nd query above:

public static DataSet getContact(string sql)
        {
            OleDbConnection conn = null;

            OleDbCommand aCommand = null;

            OleDbDataAdapter oAdapter = null;

            DataSet ds = new DataSet();

            try
            {
                conn = new OleDbConnection(connectionString);
                conn.Open();
                aCommand = new OleDbCommand(sql, conn);
                oAdapter = new OleDbDataAdapter();

                oAdapter.SelectCommand = aCommand;

                //Get the data for the selected table and populate the Grid
                oAdapter.Fill(ds);

            }
            catch (Exception exp)
            {
                MessageBox.Show("Error: ", exp.Message);
            }
            finally
            {
                oAdapter.Dispose();
                aCommand.Dispose();
                conn.Close();
            }
            return ds;
        }


        private void btnSearch1_Click(object sender, EventArgs e)
        {
            string searchText = txtSearch.Text.Trim();

            if (String.IsNullOrEmpty(searchText))
            {
                MessageBox.Show("Please enter contact's name or mobile number to search!");
                return;
            }

            DataSet ds = getContact("SELECT * FROM query1 where cont.name like '*" + searchText + "*' or cont.mobile_num like '*" + searchText + "*'");

            if (ds != null && ds.Tables[0].Rows.Count < 1)
            {
                MessageBox.Show("No contact found!");
                return;
            }
            dsTemp = ds;

        }

and a message popup saying No contact found. Appreciate any advice please. Thanks in advance!

Cheers,
Mark Thien

Change asterisk characters to percent characters. Like this: WHERE cont.name like '%mark%' or cont.mobile_num like '%mark%'; HTH

Comments
You're right man. I LOVE YOU SO MUCH !

?If you have received help, please remember to mark the thread as solved

where to mark? tell me where to mark ?

at the bottom of the page where the quick post box is, just above the box there is a link to "Mark this Thread as Solved"
By marking as solved, others solvers know you have received help :) Helsp to keep things neat.

This question has already been answered. Start a new discussion instead.