Unanswered: Query Works in ACCESS but returns nothing in C# code
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:
OleDbConnection conn = new OleDbConnection(conStr);
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);
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?
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.