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

Recommended Answers

All 4 Replies

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

commented: You're right man. I LOVE YOU SO MUCH ! +1

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

?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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.