Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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
0
Mike Miller
Asked:
Mike Miller
  • 2
1 Solution
 
YZlatCommented:
use % instead of *
0
 
TimCotteeCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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