在Access Engine中使用*和Access中的Like工作但不从vb.net执行 [英] Use * with Like in Access Statment work in Access Engine but not executed from vb.net

问题描述

大家好，我使用vb.net2005并访问2010，

我想搜索我的数据库中的文本

当我使用Like'**'时它在访问引擎内工作非常好，但是当我从vb.net表格中获胜时它会得到空的结果和我的数据的计数= 0

i不知道为什么那是发生了，但如果我不使用*

例如：'SomeText'这样的细节可以正常工作但是它不正确

因为用户在忘记时特别使用这个表格

部分文字。



这是我的访问sql语句

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

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

推荐答案

首先，我不相信你上面的查询在MS Access中工作正常，因为它确实包含保留字： type ，这是不允许的。请参阅 MS Access中的保留字列表 [ ^ ]。

能够在MS Access中使用保留字查询，您需要在保留字周围添加 [] 括号： [type]



MS Access数据库引擎在查询结束时的确如; 。

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.

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 ?;

其中？表示参数。



最终建议：

1）强烈建议你使用参数 [ ^ ]使用OledbCommand避免 Sql Injection []。

2）如果您正在尝试编写可以使用少量数据提供程序的应用程序，我建议您阅读：写一个可移植的数据访问层 [ ^ ]

3）使用表别名 [ ^ ]而不是他们的名字

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

全部谢谢，

我无法直到现在让这个甚至％不能和我一起工作，但我通过使用dataView和rowFilter以编程方式制作

再次感谢您的兴趣......

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...

这篇关于在Access Engine中使用*和Access中的Like工作但不从vb.net执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！