Click here to Skip to main content
15,791,567 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello all, thanks for reading my post. I have the following code and every so often I get a conversion error: DBNull cannot be converted to long. I am using the following code:

Protected Sub Calendar332_SelectionChanged(sender As Object, e As EventArgs)
        'declare SQL statement that will query the database
        Dim sqlstring As String = "SELECT firstname, lastname, startdate, starttime, endtime, COALESCE(hours,0) AS hours FROM hoursworked WHERE startdate >= '" & Calendar332.SelectedDate & "' AND startdate <= '" & Calendar333.SelectedDate & "' ORDER BY startdate"

        Dim strSQLconnection As String = "Data Source=SQL Server;SERVER=123456; Initial Catalog=ABC; UID=abc123;PWD=123;"

        Dim sqlConnection As New Data.SqlClient.SqlConnection(strSQLconnection)
        Dim sqlCommand As New Data.SqlClient.SqlCommand(sqlstring, sqlConnection)

        Dim reader As Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader()

        GridView7.Visible = True
        GridView7.DataSource = reader
        Dim totalhours
        Dim sqlstring1 As String = "SELECT firstname, lastname, SUM(hours) AS totalhours FROM hoursworked WHERE startdate >= '" & Calendar332.SelectedDate & "' AND startdate <= '" & Calendar333.SelectedDate & "'  group by firstname, lastname"
        Dim sqlConnection1 As New Data.SqlClient.SqlConnection(strSQLconnection)
        Dim sqlCommand1 As New Data.SqlClient.SqlCommand(sqlstring1, sqlConnection1)
        '  Dim sqlCommand1 As New Data.SqlClient.SqlCommand(sqlstring1, sqlConnection)
        Dim reader1 As Data.SqlClient.SqlDataReader = sqlCommand1.ExecuteReader()

        If totalhours Is DBNull.Value Then
            totalhours = 0
        End If

        GridView8.Visible = True
        GridView8.DataSource = reader1
    End Sub

In the aspx part I have:
                        <%# Format((CLng(DataBinder.Eval(Container.DataItem, "totalhours")) - (CLng(DataBinder.Eval(Container.DataItem, "totalhours")) Mod 60)) / 60, "0")
                        <%# Format(CLng(DataBinder.Eval(Container.DataItem, "totalhours")) Mod 60, "00")%>

After I used COALESCE in the first select statement the error went away. But, when I act as an end user and start clicking on stuff randomly the error returned. I was hoping my if statement would do the trick, but no such luck. Any ideas or suggestions would be greatly appreciated.

Thank you very much,
bluesathish 29-Nov-13 5:15am    
I think your sql command string was the problem, sometimes calendar control cause this error, In your code Calendar332.SelectedDate done this (my guess). Try to check the selecteddate value before you assigning it in a sqlcommand string.

1 solution

"The Is operator determines if two object references refer to the same object. However, it does not perform value comparisons"

I think you want an equal sign (=), rather than is, but I'm no VB expert.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900