Getting "1/1/0001 12:00:00 AM" for a datetime SQL field when there are no records for that field.

Posted on 2004-04-27
Last Modified: 2012-05-04
Problem:  "1/1/0001 12:00:00 AM" is assigned to dtInvDate
               "0" to curInvAmt

When there is no record matching the query below. How can I have the two variables to display nothing  in their respective textboxes when there is no record?

Dim strInvoiceNo As String
Dim curInvAmt As Double
Dim dtInvDate As DateTime

strSQL2 = "SELECT InvoiceNo, InvoiceAmount, InvoiceDate FROM tblInvoice Where ObligationID = " & request("obid")
 cmdObl2 = New SqlCommand(strSQL2,dsConn2)
  dtrObl2 = cmdObl2.ExecuteReader()

       If dtrObl2.HasRows Then
            strInvoiceNo = dtrObl2("InvoiceNo")
             curInvAmt =  dtrObl2("InvoiceAmount")       
            dtInvDate = dtrObl2("InvoiceDate")
             strInvoiceNo = ""
             curInvAmt = Nothing       
           dtInvDate = Nothing       
       End If
       'Close DataReader

Please help. I need a solution to this problem urgently. I have to be able to deal with NULLS in .NET. In ASP this was not a problem.
Question by:DeMyu
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 10932236
here is how I deal with nulls from a queery

If IsDBNull(myReader("ColumnName")) = true
    txtDisplay.text = ""
end if

let me know if this is what you are looking for

Author Comment

ID: 10932745

The code above works for string data not date or double. My problem is with these datatypes.

Here is a solution that I have that is currently working:

       If dtrObl2.Read() Then
            strInvoiceNo = dtrObl2("InvoiceNo")
            curInvAmt =  dtrObl2("InvoiceAmount")
            strInvAmt = curInvAmt.ToString( "c" )       
            dtInvDate = dtrObl2("InvoiceDate")
            strInvDate = dtInvDate.ToString( "d" )
       strInvoiceNo = ""
       strInvAmt = ""      
       strInvDate = ""
       End If
       'Close DataReader

I am sure there is a better way!
LVL 28

Accepted Solution

mmarinov earned 125 total points
ID: 10933019
have you tried this

Dim first as Boolean
first = true
Dim indexDate as Integer
If dtrObl2.Read() Then

   If first Then
    indexDate = dtrObl2.GetOrdinal("InvoiceDate")
    first = false
   End If

    If Not dtrObl2.IsDbNull(indexDate) Then
       dtInvDate = dtrObl2.GetDateTime(indexDate)
    End If
End If

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Assisted Solution

zulu_11 earned 125 total points
ID: 10936506
The best solution would be chage your SQL Query so that you do your checking for null at the DB level itself..

Like so..--- ISNULL(check_expression,replacement_value)

strSQL2 = "SELECT InvoiceNo, InvoiceAmount, InvoiceDate

SELECT ISNULL(InvoiceNo,""),ISNULL( InvoiceAmount,0),ISNULL( InvoiceDate,SYSDATE)

so this would effectively return

"" if InvoiceNo is NULL
0 if InvoiceAmount is NULL
SysDate if InvoceDate is NULL

you can change them to whatever you want in case of NULL values...

No need to put check in the code..

Hope this helps


Assisted Solution

by:Volkan Vardar
Volkan Vardar earned 125 total points
ID: 10937281
change your SQL query like that:

strSQL2 = "SELECT InvoiceNo, InvoiceAmount, ISNULL(CONVERT(varchar, InvoiceDate),'') FROM tblInvoice Where ObligationID = " & request("obid")


Expert Comment

ID: 10988995
Why not have another WHERE clause for the date field in your query. THis would return records where the date is >=1/1/1900 there by eliminating the date 1/1/0001 12:00:00 AM
that is displayed now. I didnt quite get your question and sorry if this is not you were asking.

WHERE dtInvDate >= to_date('1/1/1900 00:00:00' ,'MM/DD/YYYY HH24:MI:SS')

Assisted Solution

Kittrick earned 125 total points
ID: 11021379
I have had the same problems you are having, and here's how I solved them:


I don't exactly know how you have your code laid out, but I had this function ( in my codebehind to verify the field <%# DataBinder.Eval(Container.DataItem, "BirthDate") %> , but to get the function to work, I had to call it from the ASP.NET syntax in the form like this:

<%# CHECKbdate(DataBinder.Eval(Container.DataItem, "BirthDate")) %>

here is the function:
Function CHECKbdate(var1 as object) as String

'this line verifies that the field itself  isn't empty, if so what to do...
if var1 is DBNull.Value Then
end if

'The mid function trims everything after ten characters, just long enough for a date in mm/dd/yyyy format. Returns the result back to:
'<%# CHECKbdate(DataBinder.Eval(Container.DataItem, "BirthDate")) %>
'to post back to the page.

Return (mid(var1,1,10)).ToString
End Function


This should help you, since you are used to old asp. I used a repeater with my fields, but like I said, I don't know how your data is set up, but I hope I helped.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
User art_snob ( encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question