Solved

Using wildcard to query Access DB in ASP.NET(VB)

Posted on 2007-03-28
4
335 Views
Last Modified: 2012-06-21
I am binding a datagrid to an MS Access DB table. It works fine except when I use a query that involves a wildcard search. I've tried several methods, none of which seem to work. I've even tried the same query within access and it works, but not in .NET.
Here's my query...

select TargetCompany, ID, NextActionDate, NextAction from tblMain where TCProducts like '*" & strKey & "*' or PCType like '*" & strKey & "*' or PCNotes like '*" & strKey & "*' or NextAction like '*" & strKey & "*' or Notes like '*" & strKey & "*' ORDER BY TargetCompany


Like I said, I've tried moving the quotes/asterisks around every way imaginable, and I've tried using = instead of like. I don't get any errors, just not data. Can someone please show me the proper format for writing a query using a wildcard character when connected to an Access DB table through and ASP.NET page (VB)

Here's my vb code also...

    Sub BindData()

Dim strKey As String = Label8.Text
Label1.Text = "select TargetCompany, ID, NextActionDate, NextAction from tblMain where TCProducts like '*" & strKey & "*' or PCType like '*" & strKey & "*' or PCNotes like '*" & strKey & "*' or NextAction like '*" & strKey & "*' or Notes like '*" & strKey & "*' ORDER BY TargetCompany"

    Dim ds As New Data.DataSet
        Dim ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("contacts.mdb")
        Dim da As New Data.OleDb.OleDbDataAdapter(Label1.Text, ConString)
        da.Fill(ds, "employees")
        GridView1.DataSource = ds.Tables("employees")
        GridView1.DataBind()
    End Sub
0
Comment
Question by:mwmiller78
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 18808074
use % instead of *
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 18808082
You have to use % and _ as wildcards even though you are querying access. When you use any ADO or ADO.NET methods to connect to an access database the wildcards *MUST* be % and _ not * and ?:

Label1.Text = "select TargetCompany, ID, NextActionDate, NextAction from tblMain where TCProducts like '%" & strKey & "%' or PCType like '%" & strKey & "%' or PCNotes like '%" & strKey & "%' or NextAction like '%" & strKey & "%' or Notes like '%" & strKey & "%' ORDER BY TargetCompany"
0
 
LVL 35

Expert Comment

by:YZlat
ID: 18808088
select TargetCompany, ID, NextActionDate, NextAction from tblMain where TCProducts like '%" & strKey & "%' or PCType like '%" & strKey & "%' or PCNotes like '%" & strKey & "%' or NextAction like '%" & strKey & "%' or Notes like '%" & strKey & "%' ORDER BY TargetCompany

Sub BindData()

Dim strKey As String = Label8.Text
Label1.Text = "select TargetCompany, ID, NextActionDate, NextAction from tblMain where TCProducts like '%" & strKey & "%' or PCType like '%" & strKey & "%' or PCNotes like '%" & strKey & "%' or NextAction like '%" & strKey & "%' or Notes like '%" & strKey & "%' ORDER BY TargetCompany"

    Dim ds As New Data.DataSet
        Dim ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("contacts.mdb")
        Dim da As New Data.OleDb.OleDbDataAdapter(Label1.Text, ConString)
        da.Fill(ds, "employees")
        GridView1.DataSource = ds.Tables("employees")
        GridView1.DataBind()
    End Sub
0
 
LVL 2

Expert Comment

by:alfredwhang
ID: 18808181
you are using Jet so the wildcard character is % and not *
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with retrieving partial value from a column using VB.NET 4 39
SQL Exceptions 3 35
How to add <appSettings> to the webconfig file 2 20
Help with AsEnumerable(), LINQ 4 19
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now