Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009

    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);
                da.Fill(ds, "ERRORS");
                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 for infor on the tables

    Last edited by jbedson; 12-30-09 at 22:59.

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Quote Originally Posted by jbedson View Post
    ...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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2009
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts