Query Works in ACCESS but returns nothing in C# code

jbedson · #1 (**permalink**) 12-30-09, 22:54

I'm trying to add search functionallity to a program. I'm searching multiple tables and returning a result from a sing table to fill a dropdown list box.
I pass the "searchTerm" to the procedure and return a DataSet. The query is relatively complex but it prevents me from running multiple queries to get the same result.
When I use this query in Access, I get the results I expect. When I run it from the code, my dataset comes back empty...

Here is the code I'm using:

Code:

OleDbConnection conn = new OleDbConnection(conStr);
            conn.Open();
            DataSet ds = new DataSet();
            string qry = "Select e.ErrorID, e.ErrorName " +
                    "from Errors as e " +
                    "inner join solutions as s " +
                    "on e.errorid = s.errorid " +
                    "where s.summary like \"*" + SearchTerm + "*\" " +
                    "or s.detail like \"*" + SearchTerm + "*\" " +
                    "union " +
                    "Select e.errorid, e.errorname " +
                    "from errors as e " +
                    "inner join alt as a " +
                    "on e.errorid = a.errorid " +
                    "where a.summary like \"*" + SearchTerm + "*\" " +
                    "or a.detail like \"*" + SearchTerm + "*\"";            
           
            OleDbDataAdapter da = new OleDbDataAdapter(qry,conn);
          
            da.Fill(ds, "ERRORS");
            conn.Close();
            return ds;

I'm using the same code to fill a dataset in other sections and it works fine, but those sections use very simple select queries.
What am I doing wrong?

see Join on 3 tables with wild card for Search for infor on the tables

Thanks
JIM

Teddy · #2 (**permalink**) 01-22-10, 13:33

Quote:

Originally Posted by jbedson

...I'm using the same code to fill a dataset in other sections and it works fine, but those sections use very simple select queries...

That statement is contradictory.

At first glance, I'd say you might want to look in to the fact that SQL Server (TSQL) uses the '%' character as a string pattern wildcard whereas Access (Jet-SQL) uses the '*' character.

jbedson · #3 (**permalink**) 01-22-10, 21:14

Thanks. The problem is solved. It was a case of trying to over complicate things. Turns out the program works much more eficiantly with only one table. Having separate tables with relationships and trying to tie them all together was making the overly complicated for what I was trying to accomplish. I redid the interface and added two fields to the main table and everything is fine.
thanks for the response.

PS: I did need to use % instead of * in my code. For any who runs into this problem and is searching for the answer.

Internet Services

Web Development

Digital Marketing

Consumer Tech