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,521 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

22 Experts available now in Live!

Get 1:1 Help Now