如何使用VB.NET 2015替换SQL Server中select语句中的空值 [英] How do I replace the null values in select statement in SQL server using VB.NET 2015

问题描述

如果我想保存0，如果select语句中的sum函数中有空值，我的代码中出现错误，在我的数据库表字段中不接受空值并且我给出默认值0.00。



我尝试过：



我试图使用isnull（sum（field，0））

这是代码

 SaveInto.CommandText = UPDATE TrialBalance SET TransactionBalance = 
（SELECT isnull（SUM（Dr_Acc_Value），0.00）As NullValue 
来自JV_QRY 
 GROUP BY Main_Gr_Code，JV_Year 
 HAVING Main_Gr_Code ='& Trim （Account_Code）&'
和JV_Year ='& CurYear&'）
其中Account ='&修剪（Account_Code）& '
 
如果不是conn.State = ConnectionState.Open那么conn.Open（）
 SaveInto.ExecuteNonQuery（）

解决方案

在保密中使用ISNULL

喜欢

 UPDATE TrialBalance SET TransactionBalance = 
  ISNULL（（SELECT SUM（Dr_Acc_Value）
来自JV_QRY 
 GROUP BY Main_Gr_Code，JV_Year 
 HAVING Main_Gr_Code ='& Trim（Account_Code）& '
和JV_Year ='& CurYear&'，0.00） 
其中Account ='& Trim（Account_Code）&'

<预lang =vb>< pre> SaveInto.CommandText = 更新TrialBalance SET TransactionBalance =
ISNULL（ （SELECT SUM（Dr_Acc_Value）
来自JV_QRY
Group By Main_Gr_Code，JV_Year
HAVING Main_Gr_Code ='&修剪（Account_Code）& '
和JV_Year ='& CurYear& '），0.00）
其中Account ='&修剪（Account_Code）& '

谢谢大家，



@pooranendu，你的解决方案是正确的，但括号应该在逗号之前，非常感谢

Hi,
I am getting error in my code when i want to save 0 if there is null value in sum function in select statement,In my database table field not accepting null values and i give the default value 0.00.

What I have tried:

I tried to use isnull(sum(field,0))
This is the code

SaveInto.CommandText = "UPDATE TrialBalance SET TransactionBalance = 
                                                (SELECT isnull(SUM(Dr_Acc_Value),0.00) As NullValue
                                                From JV_QRY
                                                GROUP BY Main_Gr_Code,JV_Year
                                                HAVING Main_Gr_Code = '" & Trim(Account_Code) & "'
                                                And JV_Year = '" & CurYear & "') 
                                                where Account = '" & Trim(Account_Code) & "'"

                                                If Not conn.State = ConnectionState.Open Then conn.Open()
                                                SaveInto.ExecuteNonQuery()

解决方案

Use ISNULL in outerstatement
like

UPDATE TrialBalance SET TransactionBalance = 
                                                ISNULL((SELECT SUM(Dr_Acc_Value)
                                                From JV_QRY
                                                GROUP BY Main_Gr_Code,JV_Year
                                                HAVING Main_Gr_Code = '" & Trim(Account_Code) & "'
                                                And JV_Year = '" & CurYear & "',0.00)
                                                where Account = '" & Trim(Account_Code) & "'"

<pre>SaveInto.CommandText = "Update TrialBalance SET TransactionBalance = 
                                                ISNULL((SELECT SUM(Dr_Acc_Value)
                                                From JV_QRY
                                                Group By Main_Gr_Code, JV_Year
                                                HAVING Main_Gr_Code = '" & Trim(Account_Code) & "'
                                                And JV_Year = '" & CurYear & "'),0.00)
                                                where Account = '" & Trim(Account_Code) & "'"

Thanks all,

@pooranendu, Your solution is correct but the bracket should be before the comma, Thanks a lot

这篇关于如何使用VB.NET 2015替换SQL Server中select语句中的空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！