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

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
Mike MillerSoftware EngineerAsked:
Who is Participating?
 
YZlatConnect With a Mentor Commented:
use % instead of *
0
 
TimCotteeHead of Software ServicesCommented:
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
 
YZlatCommented:
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
 
alfredwhangCommented:
you are using Jet so the wildcard character is % and not *
0
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.

All Courses

From novice to tech pro — start learning today.