专家坐堂-[求助]提示sql语句执行错误Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  [求助]提示sql语句执行错误  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=126564)

--  作者：hahahehe21
--  发布时间：2018/10/24 14:10:00
--  [求助]提示sql语句执行错误

Dim Filter As String
With e.Form.Controls("txtkh")
If .Value IsNot Nothing Then
Filter = "Customer like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txtcp")
If .Value IsNot Nothing Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "Product like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txtgdh")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "PNum like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txthth")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "ContractNum like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txtddh")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "OrderNum like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("cmbywy")
If .Value IsNot Nothing Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "Sales like \'%" & .Value & "%\'"
End If
End With

With e.Form.Controls("startdate")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "InDate >= \'" & .Value & "\'"
End If
End With
With e.Form.Controls("enddate")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "InDate <= \'" & .Value & "\'"
End If
End With
If Filter > "" Then
DataTables("工单资料").Fill("Select {PNumPrintInfo}.ID,{PNumPrintInfo}.PNum,{PNumPrintInfo}.Product,PartsName,MPStyle,PaperName,PrintScale,PrintColor,PrintColorDes,PCount,LCount,TotalCount,DevName,MScale,MPCount,Customer,ContractNum,InDate,OrderNum,Sales Fro m {PNumPrintInfo} Inner JOIN {PNumSumInfo} ON {PNumSumInfo}.[PNum] = {PNumPrintInfo}.[PNum] Where Filter Order By InDate desc", "xunyue", True)

End If

此主题相关图片如下：测试.png

[此贴子已经被作者于2018/10/24 17:16:31编辑过]

--  作者：有点甜
--  发布时间：2018/10/24 14:43:00
--

改成

DataTables("工单资料").Fill("Select {PNumPrintInfo}.ID,{PNumPrintInfo}.PNum,{PNumPrintInfo}.Product,PartsName,MPStyle,PaperName,PrintScale,PrintColor,PrintColorDes,PCount,LCount,TotalCount,DevName,MScale,MPCount,Customer,ContractNum,InDate,OrderNum,Sales Fro m {PNumPrintInfo} Inner JOIN {PNumSumInfo} ON {PNumSumInfo}.[PNum] = {PNumPrintInfo}.[PNum] Where " & Filter & " Order By InDate desc", "xunyue", True)

--  作者：hahahehe21
--  发布时间：2018/10/24 15:36:00
--
好的，谢谢

--  作者：hahahehe21
--  发布时间：2018/10/24 15:45:00
--

With e.Form.Controls("txtcp") \'产品名称
If .Value IsNot Nothing Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "Product like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txtgdh") \'工单号
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "PNum like \'%" & .Value & "%\'"
End If
End With

With e.Form.Controls("txtddh") \'订单号
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "OrderNum like \'%" & .Value & "%\'"
End If
End With

查询条件输入订单号，工单号，产品名称后又会提示sql语句执行错误。不知道哪里还要修改？

--  作者：有点甜
--  发布时间：2018/10/24 16:12:00
--

试试改成（sqlserver数据源的话）

Filter = Filter & "convert(varchar(200), PNum) like \'%" & .Value & "%\'"

--  作者：hahahehe21
--  发布时间：2018/10/24 16:26:00
--
是的 sql 数据源
这个代码试了还是不行

--  作者：有点甜
--  发布时间：2018/10/24 16:36:00
--
Filter = Filter & "convert(varchar(200), OrderNum) like \'%" & .Value & "%\'"

--  作者：有点甜
--  发布时间：2018/10/24 16:37:00
--

如果还报错，msgbox(filter) 弹出的值，发上来看看。

--  作者：hahahehe21
--  发布时间：2018/10/24 17:14:00
--

With e.Form.Controls("txtcp")
If .Value IsNot Nothing Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "convert(varchar(200),Product) like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txtgdh")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "convert(varchar(200),PNum) like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txtddh")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "OrderNum like \'%" & .Value & "%\'"
End If
End With

--  作者：hahahehe21
--  发布时间：2018/10/24 17:32:00
--

With e.Form.Controls("txtcp")
If .Value IsNot Nothing Then
If Filter > "" Then
Filter = Filter & " And "
End If
Filter = Filter & "Product like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txtgdh")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "PNum like \'%" & .Value & "%\'"
End If
End With
With e.Form.Controls("txtddh")
If .Value IsNot Nothing Then
If Filter >"" Then
Filter = Filter & " And "
End If
Filter = Filter & "OrderNum like \'%" & .Value & "%\'"
End If
End With

此主题相关图片如下：梦之队截图未命名.jpg