vb.net 2010 使用oledbparameter操作ACCESS数据库问题

Question

请教各位一个OLEDB比较奇怪的问题

我写个了应用，使用oledb调用access数据库，一开始使用的拼接字符串形式操作sql语句，由于存在严重的sql注入问题，后来改为用oledbparameter，但是发现在一些稍微复杂点的sql语句中，like会出现奇怪的问题，

比如下面的语句运行完全正常，不管是中文查找还是英文查找

select * From OPERATELOGTITLE where

OperateUserIndexNum = 400000002

and [OutTarget] Like ?

and [TITLE] Like ?

order by ID

但是下面一句带两层嵌套的

select * From OPERATELOGTITLE where

OperateUserIndexNum = 400000002

[TITLE] Like ? and IndexNum in 

(select IndexNum from OPERATELOGDETAIL where STORE=1 and OPERATE_IndexNum in

(select IndexNum from [ZJ] where DESC Like ?))

order by ID

两个？参数不能有中文，输入中文就没有任何返回，输入英文就没有问题，传入的参数是标准的string

而使用之前的字符串拼接方式执行完全正常，由于对字符编码方面了解的不是太多，请教下各位，这个情况如何处理，谢谢

Answer 1

你好,

请问你使用的是什么版本的access, 可以提供一下你的代码吗？

Best regards,

Zhanglong

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

您好

数据库是用的access 2007创建的，但是调用的话是在server2012上安装的OLEDB支持包，并没有安装access

部分代码如下

            Dim cmd As OleDbCommand
            Dim sql As String
            If IndexNum > 0 Then
                sql = "select * From OPERATELOGTITLE where IndexNum=" & IndexNum
            Else
                sql = "select * From OPERATELOGTITLE where " &
                                  "OperateTime >= #" & TimeStart.ToString("yyyy/MM/dd HH:mm:ss") & "# and " &
                                  "OperateTime <= #" & TimeEnd.ToString("yyyy/MM/dd HH:mm:ss") & "# and " &
                                  If(UserIndexNum = -1, "NOT OperateUserIndexNum IS NULL", "OperateUserIndexNum = " & UserIndexNum.ToString) & " and " &
                                  GetCaseTypeSqlString(Casetype) & " and " &
                                  If(String.IsNullOrEmpty(OutTarget), "", "[OutTarget] Like ? and ") &
                                  If(String.IsNullOrEmpty(CaseTitle), "", "[TITLE] Like ? and ") &
                                  If(String.IsNullOrEmpty(BEIZHU), "", "Beizhu Like ? and ")

                Select Case Store
                    Case e_Store.ZJ
                        If SN = "" And PN = "" And DESC = "" Then
                            sql &= "IndexNum in  " &
                                        "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & ")"
                        Else
                            sql &= "IndexNum in  " &
                                        "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & " and OPERATE_IndexNum in " &
                                                "(select IndexNum from [ZJ] where " &
                                                If(String.IsNullOrEmpty(SN), "", "SN Like ? and ") &
                                                If(String.IsNullOrEmpty(PN), "", "PN Like ? and ") &
                                                If(String.IsNullOrEmpty(DESC), "", "DESC Like ?")
                            If Strings.Right(sql, 5) = " and " Then sql = Strings.Left(sql, sql.Length - 5)
                            sql &= "))"
                        End If
                    Case e_Store.XJ
                        If PN = "" And DESC = "" Then
                            sql &= "IndexNum in  " &
                                        "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & ")"
                        Else
                            sql &= "IndexNum in  " &
                                        "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & " and OPERATE_IndexNum in " &
                                                "(select IndexNum from [XJ] where " &
                                                If(String.IsNullOrEmpty(PN), "", "PN Like ? and ") &
                                                If(String.IsNullOrEmpty(DESC), "", "DESC Like ?")
                            If Strings.Right(sql, 5) = " and " Then sql = Strings.Left(sql, sql.Length - 5)
                            sql &= "))"
                        End If
                    Case e_Store.CCJ
                        If SPN = "" And DESC = "" Then
                            sql &= "IndexNum in  " &
                                         "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & ")"
                        Else
                            sql &= "IndexNum in  " &
                                        "(select IndexNum from OPERATELOGDETAIL where STORE=" & Convert.ToString(Store) & " and OPERATE_IndexNum in " &
                                                "(select IndexNum from [CCJ] where " &
                                                If(String.IsNullOrEmpty(PN), "", "SPN Like ? and ") &
                                                If(String.IsNullOrEmpty(DESC), "", "DESC Like ?")
                            If Strings.Right(sql, 5) = " and " Then sql = Strings.Left(sql, sql.Length - 5)
                            sql &= "))"
                        End If
                End Select
            End If
            If Strings.Right(sql, 5) = " and " Then sql = Strings.Left(sql, sql.Length - 5)
            sql &= " order by ID" 

            cmd = New OleDbCommand(sql, cn)
            If InStr(sql, "[OutTarget] Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@OutTarget", "%" & OutTarget & "%"))
            If InStr(sql, "[TITLE] Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@CaseTitle", "%" & CaseTitle & "%"))
            If InStr(sql, "Beizhu Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@BEIZHU", "%" & BEIZHU & "%"))
            If InStr(sql, "SN Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@SN", "%" & SN & "%"))
            If InStr(sql, "PN Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@PN", "%" & PN & "%"))
            If InStr(sql, "SPN Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@SPN", "%" & SPN & "%"))
            If InStr(sql, "DESC Like") > 0 Then cmd.Parameters.Add(New OleDbParameter("@DESC", "%" & DESC & "%"))