以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  数据类型 ntext 和 varchar 在 equal to 运算符中不兼容。  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=96269)
--  作者：huhu
--  发布时间：2017/2/16 18:19:00
--  数据类型 ntext 和 varchar 在 equal to 运算符中不兼容。
下面的方法报了这么一个错误。是哪里出错了?
System.Data.SqlClient.SqlException: 数据类型 ntext 和 varchar 在 equal to 运算符中不兼容。
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 filter As String = "1=1"
        If ddh = "null" Then
            filter &= " and 订单号 is null"
        ElseIf ddh > "" Then
            filter &= " and 订单号 = \'" & ddh & "\'"
        End If

       If ddhh = "null" Then
            filter &= " and 订单行号 is null"
        ElseIf ddhh > "" Then
            filter &= " and 订单行号 = \'" & ddhh & "\'"
        End If

        
        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 adapter As New SqlClient.SqlDataAdapter("sele ct * from [可用数量表] where " & filter, cn)

        Dim dt As New DataTable
        adapter.Fill(dt)
        cn.Close()
--  作者：有点色
--  发布时间：2017/2/16 19:07:00
--  

做类型转换

 

Convert(varchar, 备注类型的列名) = \'xxx\'
--  作者：huhu
--  发布时间：2017/2/17 9:23:00
--  
在那里改呢?改哪一句?
Public Shared Function ConvertToJsonObject(ByVal dataTable As DataTable) As String
        Dim result As New Dictionary(Of String, WarecodeStateJsonModel)

        For Each dr As DataRow In dataTable.Rows
            Dim wareCode As String
            wareCode = dr("库位类别").ToString()
            If (result.ContainsKey(wareCode)) Then
                Dim model = result(wareCode)
                model.rootWorks.Add(GetWareRootWork(dr))
            Else
                Dim model As New WarecodeStateJsonModel
                model.wareCode = wareCode
                model.rootWorks.Add(GetWareRootWork(dr))
                result.Add(wareCode, model)
            End If
        Next

        Dim models As New List(Of WarecodeStateJsonModel)

        For Each warecodeStateJsonModel As WarecodeStateJsonModel In result.Values
            models.Add(warecodeStateJsonModel)
        Next

        Return JsonConvertor.ObjectToJson(models)

    End Function

    Public Shared Function GetWareRootWork(ByVal dr1 As DataRow) As WareRootWork
        Dim rootWork As New WareRootWork(dr1("_Identify").ToString(), dr1("生产订单号码").ToString(), dr1("工单行号").ToString(), dr1("订单号").ToString(), dr1("订单行号").ToString(), dr1("物料编码").ToString(), dr1("软件版本").ToString(), dr1("两化表编号").ToString(), dr1("数量").ToString(), Format(dr1("入库时间"), "yyyy-MM-dd HH:mm:ss"))
        Return rootWork
    End Function
--  作者：有点色
--  发布时间：2017/2/17 10:04:00
--  

比如

 

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

改成

 

filter &= " and Convert(varchar, 软件版本) = \'" & rjbb & "\'"