- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- [求助]多列查重失效 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=134054)
-- 发布时间：2019/4/26 9:41:00
-- [求助]多列查重失效
在使用多列查重的时候发现系统已经有存在一样的物料信息，但还是无法识别出来，不知道是代码的问题还是针对数据里面有空格的时查重失效的情况，请帮忙给下意见；
此主题相关图片如下：微信截图_20190426093817.png
此主题相关图片如下：微信截图_20190426094221.png
以下是代码：
Dim dwdm As String = e.Form.Controls("单位代码").Value
Dim ckdm As String = e.Form.Controls("仓库代码").Value
If dwdm IsNot Nothing AndAlso ckdm IsNot Nothing Then
For Each r As Row In Tables("0604仓库物料导入_Table1").Rows
Dim Filter As String = "物料类别 = \'" & r("物料类别") & "\' And 物料名称 = \'" & r("物料名称") & "\' And 单位 = \'" & r("单位") & "\'"
If r("品牌") = Nothing Then
Filter & = " And 品牌 Is Null"
Else
Filter & = " And 品牌 = \'" & r("品牌") & "\'"
End If
If r("规格") = Nothing Then
Filter & = " And 规格 Is Null"
Else
Filter & = " And 规格 = \'" & r("规格") & "\'"
End If
If r("等级") = Nothing Then
Filter & = " And 等级 Is Null"
Else
Filter & = " And 等级 = \'" & r("等级") & "\'"
End If
Dim dr As DataRow = DataTables("C_Material").SQLFind(Filter)
If dr IsNot Nothing Then
r("物料编号") = dr("物料编号")
Dim fr As DataRow = DataTables("C_Roomm").SQLFind("单位代码 = \'" & dwdm & "\' And 仓库代码 = \'" & ckdm & "\' And 物料编号 = \'" & r("物料编号") & "\'")
If fr Is Nothing Then
r("校对通过") = True
r("备注") = Nothing
Else
r("备注") = "仓库已有"
End If
Else
Dim sl As Integer
sl = DataTables("C_Material").SQLCompute("Count(物料编号)","物料名称 Like \'%"& r("物料名称") &"%\'")
r("类似数量") = sl
r("备注") = "未有物料"
End If
Next
Else
MessageBox.Show("请检查是否输入部门或仓库信息后再进行操作,谢谢!","温馨提示")
End If
[此贴子已经被作者于2019/4/26 9:43:12编辑过]
-- 发布时间：2019/4/26 10:51:00
--
估计是中间空格的问题。对比一下数据看看有什么不同
代码看不出什么问题。
-- 发布时间：2019/4/27 12:32:00
--
此主题相关图片如下：微信截图_20190427122517.png
我在两个表中建了一列校正信息的列，将5个列去空格之后信息放在一列，在直接查询还是出现个别无法关联；
被查找列的内容：/色谱柱BEHC181.7μm/waters/2.1*100mm186002352///根
查找列的内容：/色谱柱BEHC181.7μm/waters/2.1*100mm186002352///根
查重代码：
Dim dwdm As String = e.Form.Controls("单位代码").Value
Dim ckdm As String = e.Form.Controls("仓库代码").Value
If dwdm IsNot Nothing AndAlso ckdm IsNot Nothing Then
For Each r As Row In Tables("0604仓库物料导入_Table1").Rows
Dim dr As DataRow = DataTables("C_Material").SQLFind("校正信息 = \'" & r("校正信息") &"\'")
If dr IsNot Nothing Then
r("物料编号") = dr("物料编号")
Dim fr As DataRow = DataTables("C_Roomm").SQLFind("单位代码 = \'" & dwdm & "\' And 仓库代码 = \'" & ckdm & "\' And 物料编号 = \'" & r("物料编号") & "\'")
If fr Is Nothing Then
r("校对通过") = True
r("备注") = Nothing
Else
r("备注") = "仓库已有"
End If
Else
Dim sl As Integer
sl = DataTables("C_Material").SQLCompute("Count(物料编号)","物料名称 Like \'%"& r("物料名称") &"%\'")
r("类似数量") = sl
r("备注") = "未有物料"
End If
Next
Else
MessageBox.Show("请检查是否输入部门或仓库信息后再进行操作,谢谢!","温馨提示")
End If
Dim ckdm As String = e.Form.Controls("仓库代码").Value
If dwdm IsNot Nothing AndAlso ckdm IsNot Nothing Then
For Each r As Row In Tables("0604仓库物料导入_Table1").Rows
Dim dr As DataRow = DataTables("C_Material").SQLFind("校正信息 = \'" & r("校正信息") &"\'")
If dr IsNot Nothing Then
r("物料编号") = dr("物料编号")
Dim fr As DataRow = DataTables("C_Roomm").SQLFind("单位代码 = \'" & dwdm & "\' And 仓库代码 = \'" & ckdm & "\' And 物料编号 = \'" & r("物料编号") & "\'")
If fr Is Nothing Then
r("校对通过") = True
r("备注") = Nothing
Else
r("备注") = "仓库已有"
End If
Else
Dim sl As Integer
sl = DataTables("C_Material").SQLCompute("Count(物料编号)","物料名称 Like \'%"& r("物料名称") &"%\'")
r("类似数量") = sl
r("备注") = "未有物料"
End If
Next
Else
MessageBox.Show("请检查是否输入部门或仓库信息后再进行操作,谢谢!","温馨提示")
End If
生成校正信息列的代码：
Select Case e.DataCol.Name
Case "物料名称","品牌","规格","等级","单位"
Dim jz As String
If e.DataRow.IsNull("物料名称") Then
jz = "/"
Else
jz = "/" & e.DataRow("物料名称").Replace(" ","")
End If
If e.DataRow.IsNull("品牌") Then
jz = jz & "/"
Else
jz = jz & "/" & e.DataRow("品牌").Replace(" ","")
End If
If e.DataRow.IsNull("规格") Then
jz = jz & "/"
Else
jz = jz & "/" & e.DataRow("规格").Replace(" ","")
End If
If e.DataRow.IsNull("等级") Then
jz = jz & "/"
Else
jz = jz & "/" & e.DataRow("等级").Replace(" ","")
End If
If e.DataRow.IsNull("单位") Then
jz = jz & "/"
Else
jz = jz & "/" & e.DataRow("单位").Replace(" ","")
End If
e.DataRow("校正信息") = jz
End Select
-- 发布时间：2019/4/27 13:43:00
--
如果是SqlServer数据库，到服务器跟踪一下执行了什么sql，把sql拿出来执行看看
另外SQLFind只能查询保存了的数据，看看是不是没有保存