Visual Basic 2012，在如何编写DBNull方面遇到麻烦。 "Conversion from type 'DBNull' to type 'String' is not vali

Question

我得到上面列出的错误，我已经在其他地方读到，我要做的是做一个dbnull检查…但我不知道该怎么做。我想知道是否有人能帮助我?我是VB的新手。

我还尝试将LedgerId和Net更改为小数，但随后它告诉我这一行:

JVNet += Net

是错误的。如"+对DBNull和Decimals无效"。

这是相关的代码(我想)。

    Dim SQLStmt, LedgerId, NetDisp As String
    Dim Net, JVNet As Decimal
    Dim NetCounter As Integer
    Cnxn.Open()
    SQLStmt = "SELECT LedgerId, sum(Qty * Each) as Net FROM Orders, Details  where CONVERT(nvarchar(10), AcctgDate, 101) = '" & _
        cbJVDate.Text & _
        "' and Orders.Id = Details.OrderId group by LedgerId order by LedgerId"
    Dim JVCommand As New SqlCeCommand(SQLStmt, Cnxn)
    Dim JVReader As SqlCeDataReader
    lblAdvice.Text = ""
    Debug.Print(vbCrLf & "Got Here with '" & SQLStmt & "'" & vbCrLf)
    Try
        JVReader = JVCommand.ExecuteReader
    Catch ex As Exception
        MsgBox("Execute JVReader with '" & SQLStmt & "' got " & ex.Message)
        Exit Sub
    End Try
    JVNet = 0
    NetCounter = 0
    lbJournal.Items.Clear()
    Do While JVReader.Read()
        LedgerId = JVReader.Item("LedgerId")
        Net = JVReader.Item("Net")
        JVNet += Net
        NetCounter += 1
        NetDisp = Format(Net, "0.00;(0.00)")
        NetDisp = NetDisp.PadLeft(10, " ")
        If Net < 0 Then NetDisp = " " & NetDisp
        lbJournal.Items.Add(LedgerId & NetDisp)
    Loop
    If NetCounter = 0 Then
        lblAdvice.ForeColor = Color.DarkRed
        lblAdvice.Text = "There are no details posted for " & cbJVDate.Text & "..."
    ElseIf JVNet = 0 Then
        lblAdvice.ForeColor = Color.Black
        lblAdvice.Text = "The journal for this date nets zero."
    Else
        lblAdvice.ForeColor = Color.DarkRed
        lblAdvice.Text = "The journal for this date does not net zero." & vbCrLf & _
           vbCrLf & "It nets " & Format(JVNet, "0.00")
    End If
    Cnxn.Close()
End Sub

谢谢你的帮助!

Answer 1

在VB代码中，数据库NULL用DBNull.Value表示。如果你从数据库中获取数据那么你可以这样做:

Dim str As String = Nothing
Dim int As Integer? = Nothing
If Not myDataRow.IsNull("Text") Then
    str = CStr(myDataRow("Text"))
End If
If Not myDataRow.IsNull("Number") Then
    int = CInt(myDataRow("Number"))
End If

反过来，你可以这样做:

Dim command As New SqlCommand("UPDATE MyTable SET Text = @Text, Number = @Number WHERE ID = @ID", connection)
command.Parameters.Add("@Text", SqlDbType.VarChar, 50).Value = If(str Is Nothing, CObj(DBNull.Value), str)
command.Parameters.Add("@Number", SqlDbType.Int).Value = If(int.HasValue, int.Value, CObj(DBNull.Value))

Answer 2

SQL CE不支持IFNULL或ISNULL函数，要复制该函数，一种方法是使用CASE..END构造:

SQLStmt = "SELECT LedgerId, 
CASE SUM(Qty * Each) IS NULL THEN 0 END AS Net
FROM Orders, Details
WHERE CONVERT(nvarchar(10), AcctgDate, 101) = '" & cbJVDate.Text & "' AND Orders.Id = Details.OrderId
GROUP BY LedgerId ORDER BY LedgerId"

另一种方法是使用COALESCE(我不能测试，因为我没有SQL CE)

SQLStmt = "SELECT LedgerId, 
CAOLESCE(SUM(Qty * Each),0) AS Net
FROM Orders, Details
WHERE CONVERT(nvarchar(10), AcctgDate, 101) = '" & cbJVDate.Text & "' AND Orders.Id = Details.OrderId
GROUP BY LedgerId ORDER BY LedgerId"