• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

SQL works in access but not in program

I am trying to implement a search function into the program I am writing and I am trying to write a SQL statement that uses the Like command to return results.  When I tested it in Access 2000 it worked fine but when I run the same command in my program I get no results

my code:
          private OleDbConnection windowsData;
     windowsData = new OleDbConnection "Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=" + Application.StartupPath + "\\weatherwise.mdb");

               DataTable dt;
               DataRow dr;
               OleDbDataAdapter dbIO;
               OleDbParameter p;
               dbIO = new OleDbDataAdapter("SELECT JobInfo.JobNumber, JobInfo.StreetAddress, JobInfo.CustomerName, JobInfo.AuditDate, JobInfo.OwnerName FROM JobInfo WHERE (((JobInfo.JobNumber) = \"*5*\"))", windowsData);
               dt = new DataTable("JobInfo");
               //p = new OleDbParameter("@number", DbType.String);
               //p.Value = jobNo;
          //     dbIO.SelectCommand.Parameters.Add(p);

               for( int i=0; i < dt.Rows.Count; i++ )
                    dr = dt.Rows[i];
                    ListViewItem listing = new ListViewItem(d "JobNumber"].ToString() );
                    listing.SubItems.Add( dr["StreetAddress"].ToString() );
                    listing.SubItems.Add( dr["CustomerName"].ToString() );
                    listing.SubItems.Add( dr["OwnerName"].ToString() );
                    listing.SubItems.Add( dr["AuditDate"].ToString() );

                    myList.Items.Add( listing );

Any ideas or is there a better way to implement a search without using LIKE?
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
If your jobNumber value is 5, use this:

WHERE (((JobInfo.JobNumber) = 5))"

It appears that you want ALL JobNumbers that have a 5 anywhere within the JobNumber....is that correct?

id so, change you code like this:

(((JobInfo.JobNumber) LIKE '%5%'))",

Assuming that JobNumber is in fact a TEXT field, and that you are using ADO as the Data Access technology.

in ADO the Wildcard character is the %, while within Access itself, and with DAO, the * is the wildcard.

corneliuosAuthor Commented:
Thanks arthur that was exactly what I needed
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now