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.

Not that I want to suggest a completely new way of thinking, but have you heard of the [EntityFramework](http://en.wikipedia.org/wiki/Entity_Framework)? — gunr2171, Jul 22 '13 at 15:53
`AND EXPRESSION LIKE 'SN%'` (May be `*` instead of `%` - can't remember) (you can also reuse a single connection ...) — Alex K., Jul 22 '13 at 15:56
Not that this really solves your problem quickly, but I would suggest taking a look at it. It solves your problem in about 1 line, and eliminates SQL injection (which your code is prone to). — gunr2171, Jul 22 '13 at 15:56
you want to use substring i would imagine to only search the first 2 letters — Dev N00B, Jul 22 '13 at 15:57
@gunr2171: Entity Framework doesn't work with Access databases. — Christian Specht, Jul 22 '13 at 17:20

Alden · Accepted Answer · 2013-07-23T16:22:31.180

2

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*'

edited Jul 23 '13 at 16:22

answered Jul 22 '13 at 16:01

Alden

6,553
2
36
50

I know this code works, but it is an excellent example of [SQL Injection](http://msdn.microsoft.com/en-us/library/ms161953(v=sql.105).aspx). What happens if `end` is equal to `'; drop table AC_ECONOMIC --`? – gunr2171 Jul 22 '13 at 16:16
If `end` and `qual` were coming from user input, I would agree with you, but in this case they're defined by user2607468 right above the query. That said, there are better ways to construct queries, like by adding parameters via `SelectCommand.Parameters.Add()`. – Alden Jul 22 '13 at 16:21
A handful of people keep trying to edit 'SN*' to be 'SN%'. However, * is the wild card character for MsAccess, not %. – Alden Jul 22 '13 at 23:35
With OleDb the wild card must be `%`, not `*`. This issue with MS Access has been discussed many times here on SO. Here are 2 examples: http://stackoverflow.com/questions/3193350/like-command-problem-using-oledbreader-and-ms-access and http://stackoverflow.com/questions/5166907/ms-access-sql-any-reason-why-like-does-not-work – HansUp Jul 23 '13 at 00:19

Querying an Access Database

1 Answers1