?
Solved

SQL works in access but not in program

Posted on 2003-03-24
3
Medium Priority
?
210 Views
Last Modified: 2010-04-15
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;
               MessageBox.Show(mySelection);
               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);
               windowsData.Open();
               dbIO.Fill(dt);


               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 );
                    windowsData.Close();
               }

Any ideas or is there a better way to implement a search without using LIKE?
0
Comment
Question by:corneliuos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 8195679
If your jobNumber value is 5, use this:

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

0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 300 total points
ID: 8195811
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.

AW
0
 

Author Comment

by:corneliuos
ID: 8197833
Thanks arthur that was exactly what I needed
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question