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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : All lightning effects with instructions : http://www.mediaf…
Suggested Courses

621 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