有关DBNULL值的帮助 [英] Help on DBNULL values
本文介绍了有关DBNULL值的帮助的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好,请问我正在一个项目上.在一个网页中,有一个文本框可以选择用户必须给定的两个给定期间之间的总金额.但是,如果没有给定的日期,它将引发异常,指出无法将对象从DBNull强制转换为其他类型".不知道该怎么做才能使它正常工作.我仍然是vb和sql的婴儿.任何帮助都受到高度赞赏.以下是我的代码
Hello,please i am working on a project.In one of the web page there is a textbox to select the total amount between two given periods which the user must give.The code works fine if the given dates are present in the database.But if those date given are not present it throws an exception saying "Object cannot be cast from DBNull to other types". Don''t know what to do to make it work.i am still a baby in vb and sql.Any help is highly appreciated.Below is my code
Protected Sub SearchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchButton.Click
If TxtEndDate.Text = String.Empty Then
ElseIf TxtStartDate.Text = String.Empty Then
Exit Sub
Else
Try
Dim connectionstring As String = ConfigurationManager.ConnectionStrings("LordsBannerMotorsConnectionString1").ConnectionString
Dim insertsql As String = "SELECT SUM(Amount) As [Total Amount] FROM MoneyPaidToBank WHERE " &
" Bank_Location=@Location AND Date BETWEEN @Start AND @End"
Using connection As New SqlConnection(connectionstring)
connection.Open()
Dim mycommand As New SqlCommand(insertsql, connection)
mycommand.Parameters.Add("@Location", SqlDbType.VarChar, 50)
mycommand.Parameters("@Location").Value = TotalMoneyDropDownList1.SelectedValue
mycommand.Parameters.Add("@Start", SqlDbType.SmallDateTime)
mycommand.Parameters("@Start").Value = CType(TxtStartDate.Text, Date)
mycommand.Parameters.Add("@End", SqlDbType.SmallDateTime)
mycommand.Parameters("@End").Value = CType(TxtEndDate.Text, Date)
Dim str As String = ""
Dim reader As SqlDataReader = mycommand.ExecuteReader
If reader.HasRows Then
While reader.Read
str &= Convert.ToDouble(reader.Item("Total Amount"))
End While
Else
SearchLabel.Text = "Record not found"
End If
SearchLabel.Text = "The total Amount is " & str
connection.Close()
End Using
Catch ex As Exception
SearchLabel.Text = ex.Message
End Try
End If
推荐答案
如果未找到指定位置和日期范围的金额,则SUM函数将返回NULL.
使用 COLEASCE() [ ISNULL() [ ^ ]函数,如下所示:
If the Amount was not found for the specified location and date range, SUM function returns NULL.
Use COLEASCE()[^] or ISNULL()[^] function, like this:
SELECT SUM(ISNULL(Amount,0)) As [Total Amount]
FROM MoneyPaidToBank
WHERE Bank_Location=@Location AND Date BETWEEN @Start AND @End
这篇关于有关DBNULL值的帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文