Querying an Access Database

Question

I'm trying to get matching values from two different tables of an Access Database. I was wondering if it were at all possible to get values from a column where the cell begins with "SN" and leave all the rest of the data out. Here's what I have so far.

        String filePath = textBox1.Text;

        con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath);
        con3 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath);



        if ((string)comboBox1.SelectedItem == "CGA0112")
        {
            try
            {
                string end = "ENDDATE";
                string qual = "CGA0112";
                string start = "START";

                ad.SelectCommand = new OleDbCommand("SELECT* FROM [AC_ECONOMIC] Where KEYWORD = '" + end + "'AND QUALIFIER = '" + qual + "' AND EXPRESSION = '", con); // ISSUE AFTER EXPRESSION= 


                ds.Clear();
                ad.Fill(ds);

                con.Open();
                ad.SelectCommand.ExecuteNonQuery();
                con.Close();

                ad3.SelectCommand = new OleDbCommand("SELECT* FROM [AC_ECONOMIC] Where KEYWORD = '" + start + "'AND QUALIFIER = '" + qual + "'", con3);
                ds3.Clear();
                ad3.Fill(ds3);

                ds.Merge(ds3); //merges the two datasets together


                win1.frm1 = this;
                win1.Show();

                con3.Open();
                ad3.SelectCommand.ExecuteNonQuery();
                con3.Close();

            }

I guess I just want to add some code after the EXPRESSION area but I'm not too sure where to begin. I just want it to read something like AND EXPRESSION = startswith("SN"). Any help would be much appreciated.

Answer 1

You should use like as follows:

"SELECT * FROM [AC_ECONOMIC] Where KEYWORD = '" + end + 
"' AND QUALIFIER = '" + qual + "' AND EXPRESSION LIKE 'SN*'"

However, as someone mentioned in the comments, I think you should definitely look into a framework, maybe something like Dapper, which is pretty awesome. There are also a handful of libraries that extend Dapper to make it even easier to get going, like this one.

Edit: See comments - 'SN%' should be used with OleDb, not 'SN*'