上一篇
下一篇
Access中日文导致搜索出错
作者:刚子 日期:2010-07-26
今天碰见了一个超级奇怪的问题，我和一个朋友同时在用我自己写的一个程序。但是发现他的无法使用搜索，一搜索就会出错。我自己的搜索没任何问题。我把他的数据库放到我的网站发现搜索也会出错。我打开两个数据库一个一个对比，发现数据库结构完全一致，内容数量也差不多。于是我怀疑是access里存储了“不该存储”的东西。于是打开他的数据库快速浏览了一遍，不注意发现了一个日文文章。哥顿时内牛满面，不知道在何年何月隐约记得ACC和日文有冲突，我就想狗日的，肯定是他搞的鬼。于是把他数据库中的日文记录删除掉再次测试，果然没出问题。
后来百度查资料发现，原来有26个日文片假名出现在数据库中就会出错，这是Access的一个Bug。
至于更深层的东西我就不再琢磨了，不过那哥们提供了一个解决办法蛮不错，不用删除数据库记录了。
引用内容
补充：上面的方法是在查询的时候使用，下面这个类可以在加入新内容的时候过滤一下，直接不允许这类字符加入。
程序代码
后来百度查资料发现，原来有26个日文片假名出现在数据库中就会出错，这是Access的一个Bug。
至于更深层的东西我就不再琢磨了，不过那哥们提供了一个解决办法蛮不错，不用删除数据库记录了。
引用内容在查询的时候把如下的语句：
Sql = "select * From [tbname] Where Title like '%"&keyword&"%' order By id Desc"
换成：
Sql = "select * from [tbname] where InStr(1,LCase(Title),LCase('"&keyword&"'),0)<>0 order by id desc"
Sql = "select * From [tbname] Where Title like '%"&keyword&"%' order By id Desc"
换成：
Sql = "select * from [tbname] where InStr(1,LCase(Title),LCase('"&keyword&"'),0)<>0 order by id desc"
补充：上面的方法是在查询的时候使用，下面这个类可以在加入新内容的时候过滤一下，直接不允许这类字符加入。
程序代码Function EncodeJP(StrHTML)
If Not StrHTML="" Then
StrHTML=Replace(StrHTML,"ガ","ガ")
StrHTML=Replace(StrHTML,"ギ","ギ")
StrHTML=Replace(StrHTML,"ア","ア")
StrHTML=Replace(StrHTML,"ゲ","ゲ")
StrHTML=Replace(StrHTML,"ゴ","ゴ")
StrHTML=Replace(StrHTML,"ザ","ザ")
StrHTML=Replace(StrHTML,"ジ","ジ")
StrHTML=Replace(StrHTML,"ズ","ズ")
StrHTML=Replace(StrHTML,"ゼ","ゼ")
StrHTML=Replace(StrHTML,"ゾ","ゾ")
StrHTML=Replace(StrHTML,"ダ","ダ")
StrHTML=Replace(StrHTML,"ヂ","ヂ")
StrHTML=Replace(StrHTML,"ヅ","ヅ")
StrHTML=Replace(StrHTML,"デ","デ")
StrHTML=Replace(StrHTML,"ド","ド")
StrHTML=Replace(StrHTML,"バ","バ")
StrHTML=Replace(StrHTML,"パ","パ")
StrHTML=Replace(StrHTML,"ビ","ビ")
StrHTML=Replace(StrHTML,"ピ","ピ")
StrHTML=Replace(StrHTML,"ブ","ブ")
StrHTML=Replace(StrHTML,"ブ","ブ")
StrHTML=Replace(StrHTML,"プ","プ")
StrHTML=Replace(StrHTML,"ベ","ベ")
StrHTML=Replace(StrHTML,"ペ","ペ")
StrHTML=Replace(StrHTML,"ボ","ボ")
StrHTML=Replace(StrHTML,"ポ","ポ")
StrHTML=Replace(StrHTML,"ヴ","ヴ")
End If
EncodeJP=StrHTML
End Function
If Not StrHTML="" Then
StrHTML=Replace(StrHTML,"ガ","ガ")
StrHTML=Replace(StrHTML,"ギ","ギ")
StrHTML=Replace(StrHTML,"ア","ア")
StrHTML=Replace(StrHTML,"ゲ","ゲ")
StrHTML=Replace(StrHTML,"ゴ","ゴ")
StrHTML=Replace(StrHTML,"ザ","ザ")
StrHTML=Replace(StrHTML,"ジ","ジ")
StrHTML=Replace(StrHTML,"ズ","ズ")
StrHTML=Replace(StrHTML,"ゼ","ゼ")
StrHTML=Replace(StrHTML,"ゾ","ゾ")
StrHTML=Replace(StrHTML,"ダ","ダ")
StrHTML=Replace(StrHTML,"ヂ","ヂ")
StrHTML=Replace(StrHTML,"ヅ","ヅ")
StrHTML=Replace(StrHTML,"デ","デ")
StrHTML=Replace(StrHTML,"ド","ド")
StrHTML=Replace(StrHTML,"バ","バ")
StrHTML=Replace(StrHTML,"パ","パ")
StrHTML=Replace(StrHTML,"ビ","ビ")
StrHTML=Replace(StrHTML,"ピ","ピ")
StrHTML=Replace(StrHTML,"ブ","ブ")
StrHTML=Replace(StrHTML,"ブ","ブ")
StrHTML=Replace(StrHTML,"プ","プ")
StrHTML=Replace(StrHTML,"ベ","ベ")
StrHTML=Replace(StrHTML,"ペ","ペ")
StrHTML=Replace(StrHTML,"ボ","ボ")
StrHTML=Replace(StrHTML,"ポ","ポ")
StrHTML=Replace(StrHTML,"ヴ","ヴ")
End If
EncodeJP=StrHTML
End Function
文章来自: 
引用通告: 
Tags: 评论: 0 | 引用: 0 | 查看次数: -
发表评论