Solved

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
9
1,570 Views
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")
       Else
             strInvoiceNo = ""
             curInvAmt = Nothing       
           dtInvDate = Nothing       
       End If
       'Close DataReader
       dtrObl2.Close()

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.
0
Comment
Question by:DeMyu
9 Comments
 
LVL 6

Expert Comment

by:gtvingo14
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
0
 

Author Comment

by:DeMyu
ID: 10932745
gtvingo14,

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" )
       Else
       strInvoiceNo = ""
       strInvAmt = ""      
       strInvDate = ""
      
       End If
       'Close DataReader
       dtrObl2.Close()

I am sure there is a better way!
0
 
LVL 28

Accepted Solution

by:
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

HTH
B..M
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Assisted Solution

by:zulu_11
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

Zulu
0
 
LVL 6

Assisted Solution

by:vardium
vardium 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")

0
 
LVL 1

Expert Comment

by:nparthi
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')
0
 
LVL 4

Assisted Solution

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

 ABOUT NULLS AND DATES

I don't exactly know how you have your code laid out, but I had this function (vb.net) 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''''''''''''''''''''''''''''''
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
   Return"000"
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

''''''''''''''''''''''''''''''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.

Kittrick
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now