对NULL的处理 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

<WebMethod()>

Public Function GetState1Refine(ByVal ddh As String, ByVal ddhh As String, ByVal wlbm As String, ByVal lhbbh As String, ByVal rjbb As String) As String

Dim cnStr As String = "Data Source=172.16.11.201;Initial Catalog=songjiang;Integrated Security=False;User ID=sa;Password=bdcom103liujy;"

Dim cn As New SqlClient.SqlConnection(cnStr)

cn.Open()

Dim adapter As New SqlClient.SqlDataAdapter("s elect * from [可用数量表] where 物料编码 = '" & wlbm & "' and 订单行号 = '" & ddhh & "' and 软件版本 = '" & rjbb & "' and 两化表编号 = '" & lhbbh & "' and 订单号 = '" & ddh & "'", cn)

Dim dt As New DataTable

adapter.Fill(dt)

cn.Close()

当后台数据表的值为NULL。如附件后台NULL

查询界面写NULL或者为空，都不行。如附件NULL。这是怎么回事?

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

此主题相关图片如下：后台null.png

改成 where 物料名 is null and 订单号 is null

这是给别人提供接口。别人又不知道两化表编号或者订单号为NULL。

怎么变通呢

别人留空，你就不查值；别人填null，你就对应改成 is null 啊

参考前几天那种写法，合成条件。

恩。已经做好了。

但有一种情况。

Dim filter1 As String = "订单号 <> '" & ddh & "' and 订单行号 <> '" & ddhh & "'"

加如果ddh=1.但后台订单号的值为NULL

NULL<>'1'---这个成立吗?

null值和值比较得到的结构，都是false。所以无法成立。

你需要这样比较

(订单 is null Or 订单 <> '1')

<WebMethod()>

Public Function GetState2Refine(ByVal ddh As String, ByVal ddhh As String, ByVal wlbm As String, ByVal rjbb As String, ByVal lhbbh As String) As String

Dim cnStr As String = "Data Source=172.16.11.201;Initial Catalog=songjiang;Integrated Security=False;User ID=sa;Password=bdcom103liujy;"

Dim cn As New SqlClient.SqlConnection(cnStr)

cn.Open()

Dim adapter As SqlClient.SqlDataAdapter

Dim strSql As String = ""

If ddh = "" Or ddhh = "" Or ddh = "null" Or ddhh = "null" Then

Dim state1 As String

state1 = "订单号或者订单行号不能为空"

Return state1

'adapter = New SqlClient.SqlDataAdapter("s elect * from [可用数量表] where 物料编码 = '" & wlbm & "' and 软件版本 = '" & rjbb & "' and 两化表编号 like '% " & lhbbh & "%'", cn)

Else

Dim filter As String = "1=1"

If wlbm = "" Then

filter &= " and 1=1"

ElseIf wlbm = "null" Then

filter &= " and 物料编码 is null"

Else

filter &= " and 物料编码 = '" & wlbm & "'"

End If

If lhbbh = "" Then

filter &= " and 1=1"

ElseIf lhbbh = "null" Then

filter &= " and 两化表编号 is null"

Else

filter &= " and 两化表编号 like '%" & lhbbh & "%'"

End If

If rjbb = "" Then

filter &= " and 1=1"

ElseIf rjbb = "null" Then

filter &= " and 软件版本 is null"

Else

filter &= " and 软件版本 = '" & rjbb & "'"

End If

Dim filter1 As String = "订单号 is null or 订单行号 is null or 订单号 <> '" & ddh & "' or 订单行号 <> '" & ddhh & "' and "

MsgBox(filter1)

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

strSql = "s elect * from [可用数量表] where " & filter1 & filter

MsgBox(strSql)

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

adapter = New SqlClient.SqlDataAdapter(strSql, cn)

Dim dt As New DataTable

adapter.Fill(dt)

cn.Close()

报下面的错误参数“Expression”不是有效值。这不是合成s elect不对？

System.ArgumentException: 参数“Expression”不是有效值。
   在 Microsoft.VisualBasic.Strings.Format(Object Expression, String Style)
   在 WebApplication4.OrderQueryBLL.GetWareRootWork(DataRow dr1) 位置 F:\VS\WebApplicationN\WebApplication4\OrderQueryBLL.vb:行号 51
   在 WebApplication4.OrderQueryBLL.ConvertToJsonObject(DataTable dataTable) 位置 F:\VS\WebApplicationN\WebApplication4\OrderQueryBLL.vb:行号 31
   在 WebApplication4.WebService1.GetState2Refine(String ddh, String ddhh, String wlbm, String rjbb, String lhbbh) 位置 F:\VS\WebApplicationN\WebApplication4\WebService1.asmx.vb:行号 167

Dim filter As String = "1=1"
ElseIf wlbm = "null" Then
filter &= " and 物料编码 is null"
ElseIf wlbm > ""
filter &= " and 物料编码 = '" & wlbm & "'"
End If

If lhbbh = "null" Then
filter &= " and 两化表编号 is null"
Else If lhbbh > "" Then
filter &= " and 两化表编号 like '%" & lhbbh & "%'"
End If

If rjbb = "null" Then
filter &= " and 软件版本 is null"
ElseIf rjbb = "" Then
filter &= " and 软件版本 = '" & rjbb & "'"
End If
Dim filter1 As String = "(订单号 is null or 订单号 <> '" & ddh & "') and (订单行号 <> '" & ddhh & "') and "
MsgBox(filter1)

strSql = "select * from [可用数量表] where " & filter1 & filter
MsgBox(strSql)

Dim filter As String = "1=1"

If wlbm = "null" Then

filter &= " and 物料编码 is null"

ElseIf wlbm > "" Then

filter &= " and 物料编码 = '" & wlbm & "'"

End If

If lhbbh = "null" Then

filter &= " and 两化表编号 is null"

ElseIf lhbbh > "" Then

filter &= " and 两化表编号 like '%" & lhbbh & "%'"

End If

If rjbb = "null" Then

filter &= " and 软件版本 is null"

ElseIf rjbb > "" Then

filter &= " and 软件版本 = '" & rjbb & "'"

End If

Dim filter1 As String = "(订单号 is null or 订单号 <> '" & ddh & "') and (订单行号 is null or 订单行号 <> '" & ddhh & "') and "

strSql = "s elect * from [可用数量表] where " & filter1 & filter

MsgBox(strSql)

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

还是回报这个错

System.ArgumentException: 参数“Expression”不是有效值。

条件没有错，你其它代码有问题。

[此贴子已经被作者于2017/2/9 11:16:44编辑过]

主题：对NULL的处理