Use * with Like in Access Statment work in Access Engine but not executed from vb.net

Question

0.00/5 (No votes)

See more:

Hi all, I use vb.net2005 and access 2010,
I want to search inside a text in my db
when i use Like '**' it work very fine inside access engine but
when i exceute in from vb.net form it get empty result and count of my datatable =0
i don't know why that is happened but if i don't use *
Ex: where details like 'SomeText' it work but it is not right
because the user use this form specially when he forget
some of the text.

This is my access sql statment

VB

mysql = "   select no_id,mstrid,type,details ,myob,dated  from (  "
    mysql = mysql & " select rcptdetails.no_id, RcptMasterNo as mstrid , type,details,rcptmaster.myob,rcptmaster.dated"
    mysql = mysql & " from rcptdetails inner join rcptmaster   on rcptmaster.no_id  =rcptdetails.RcptMasterNo  "
    mysql = mysql & "  union all select pymtdetails.no_id, PymtMasterNo as mstrid , type,details ,pymtmaster.myob,pymtmaster.dated "
    mysql = mysql & " from pymtdetails inner join pymtmaster on pymtmaster.no_id  =pymtdetails.PymtMasterNo  "
    mysql = mysql & "  union all select  pymtdetailscash.no_id, PymtMasterNo as mstrid ,'PaymentCash' as  type,details ,pymtmastercash.myob,pymtmastercash.dated  "
    mysql = mysql & " from pymtdetailscash inner join pymtmastercash on pymtmastercash.no_id  =pymtdetailscash.PymtMasterNo "
    mysql = mysql & "  union all select jvdetails.noid, JVMMaster as mstrid , jvdetails.type,details ,jvmaster.myob  , jvmaster.dated "
    mysql = mysql & " from jvdetails inner join jvmaster on jvmaster.no_id  =jvdetails.JVMMaster  "
    mysql = mysql & "  union all select pdcidetails.no_id, PDCIMasterNo as mstrid , pdcidetails.type,details ,pdcimaster.myob,pdcimaster.dated "
    mysql = mysql & " from pdcidetails inner join pdcimaster on pdcimaster.no_id  =pdcidetails.PDCIMasterNo "
    mysql = mysql & "  union all select pdcrdetails.no_id, PDCrMasterNo as mstrid , pdcrdetails.type,details ,pdcrmaster.myob ,pdcrmaster.dated "
    mysql = mysql & "  from pdcrdetails inner join pdcrmaster on pdcrmaster.no_id  =pdcrdetails.PDCrMasterNo  "
    mysql = mysql & "   ) as sqlmain "
    mysql = mysql & "  where 1 = 1 And Details Is Not null  " 'and Details like '*" & txtDtls.Text & "*'" ' 

    If txtDtls.Text <> "" Then
        If cn.ConnectionString.Contains("Provider") Then ' access
            mysql = mysql & "  and [Details] like  '*" & txtDtls.Text & "*'"
        Else
            mysql = mysql & "  and Details like '%" & txtDtls.Text & "%'"
        End If
    End If
    mysql = mysql & " order by myob "

    Dim idt9 As New DataTable

    idt9 = MyConn.GetDatatable(mysql)

Can anyone help me Please? Urgently

Posted 28-Mar-15 20:31pm

SarahSaid84

Updated 28-Mar-15 21:25pm

Richard MacCutchan

v2

Add a Solution

Comments

DamithSL 29-Mar-15 3:08am

have you tried with % instead of * for access database?

SarahSaid84 29-Mar-15 3:15am

yes, not work with access,
it work with sql server and oracle only.

Richard MacCutchan 29-Mar-15 3:28am

It is more likely that something else in your select statement is filtering out your records. Try a simple select clause to check that the wildcard specification works.

SarahSaid84 29-Mar-15 3:44am

i tried a simple one from only one table try % > in access not work and vb.net works fine.
try * in access works fine and vb.net not work.
I don't know why * makes problem with vb.net?
Is it a reserved word????

Richard MacCutchan 29-Mar-15 3:56am

How can it be a reserved word, it is inside a string?
Please edit your question and show the samples you have used and the results you receive. See also http://www.techonthenet.com/access/queries/like.php.

SarahSaid84 29-Mar-15 4:06am

Mr. Richard ,
Thanks for your interest First,
i know that there is not a reserved words inside a string.
Put really it does not work
i say this article and this is which make me use * from the beginning, i tried in inside access and work but sql send from vb.net form not work ,
i also try
where details like 'sometext' and work
, i try the select statment without criteria and also work fine ,
Is it a understanding reason on what's happen?

And Thanks again for your quick response.

Richard MacCutchan 29-Mar-15 9:21am

I have run some tests and found that the % character works using the OleDb Jet driver. I have a very small database but statements such as the following all produce valid results:


Select * From Addresses Where City Like 'Lon%'"
Select * From Addresses Where City Not Like '%don'"

Kornfeld Eliyahu Peter 29-Mar-15 4:26am

Are you sure you get the right string in the if statement?
It may identify wrongly the SQL engine and creates the like with % instead of *?

SarahSaid84 29-Mar-15 4:37am

I made it right because when i delete the if statment and take it directly not work also...

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-03-28T23:47:00

First of all, i do not believe you that above query works fine in MS Access, because it does contain reserved word: type, which is not allowed. Please, see the list of reserved words in MS Access[^].
To be able to use reserved word in MS Access query, you need to add [] brackets around reserved word: [type]

MS Access database engine does like ; at the end of query.

SQL

SELECT no_id, mstrid, [type], details, myob, dated
FROM (
    SELECT rcptdetails.no_id, RcptMasterNo AS mstrid , [type], details, rcptmaster.myob,rcptmaster.dated
    FROM rcptdetails INNER JOIN rcptmaster   ON rcptmaster.no_id=rcptdetails.RcptMasterNo
    union all
    SELECT pymtdetails.no_id, PymtMasterNo AS mstrid , [type], details, pymtmaster.myob, pymtmaster.dated
    FROM pymtdetails INNER JOIN pymtmaster ON pymtmaster.no_id=pymtdetails.PymtMasterNo
    union all
    SELECT  pymtdetailscash.no_id, PymtMasterNo AS mstrid ,'PaymentCash' AS  [type], details, pymtmastercash.myob, pymtmastercash.dated
    FROM pymtdetailscash INNER JOIN pymtmastercash ON pymtmastercash.no_id=pymtdetailscash.PymtMasterNo
    union all
    SELECT jvdetails.noid, JVMMaster AS mstrid , jvdetails.[type], details,jvmaster.myob, jvmaster.dated
    FROM jvdetails INNER JOIN jvmaster ON jvmaster.no_id=jvdetails.JVMMaster
    union all
    SELECT pdcidetails.no_id, PDCIMasterNo AS mstrid, pdcidetails.[type], details, pdcimaster.myob, pdcimaster.dated
    FROM pdcidetails INNER JOIN pdcimaster ON pdcimaster.no_id=pdcidetails.PDCIMasterNo
    union all
    SELECT pdcrdetails.no_id, PDCrMasterNo AS mstrid , pdcrdetails.[type], details, pdcrmaster.myob, pdcrmaster.dated
    FROM pdcrdetails INNER JOIN pdcrmaster ON pdcrmaster.no_id=pdcrdetails.PDCrMasterNo
  ) AS sqlmain
 where 1 = 1 And Details Is Not null and Details like ?;

where ? means parameter.

Final suggestion:
1) It is strongly recommended to ude parameters[^] together with OledbCommand to avoid Sql Injection[^].
2) If you're trying to write application which can use few data providers, i'd suggest to read this: Writing a Portable Data Access Layer[^]
3) Use table aliases[^] instead of their names

SarahSaid84 · Answer 2 · 2015-03-30T22:41:00

Solution 3

Thanks All ,
I couldn't till now make this even % not work with me but i make if programmatically by using dataView and rowFilter
Thanks Again For Your Interest...

Posted 30-Mar-15 22:41pm